Prev: Weekly Maintenance Plan
Next: Sql Server 2000 restore to different db name?
From: Jan Waiz on 10 Aug 2010 10:48
Hi All,

working with a SQL-Server 2005 i have a problem generating indicators (like
kpi) on current dynamic data. Let me describe:

Given is a dataset as this:

Supplier Year Amount
-------- ----- --------
Supp1 2008 10
Supp1 2009 15
Supp1 2010 13
Supp2 2008 15
Supp2 2009 03
Supp2 2010 05
Supp3 2008 12
Supp3 2009 10
Supp3 2010 11

Three Suppliers with their Amounts (whatever the amount is isnt important
here) for the last three years. In the report a need a matrix like this:

Supplier Total Best
-------- ------ -----
Supp1 38 No
Supp2 23 Yes
Supp3 31 No

For the first two columns no problem - the matrix has a rowgroup on field
"Supplier" and for the column "Total" in the datacell i have an expression
like SUM(Amount). Anyhow - all this works fine. But now:

I need to indicate the "best" (in real word by setting a color instead of a
text "No" or "Yes") on dynamic data of the sum for each supplier/all years.
All examples i find in internet using constructs like IIF(Total < 10,
"red") - but i cant make the "10" fix - i have to determine them on the fly,
because the values can change and the smallest value should win...

Any idea? Any Tip? Any Solution? *smile*

Regards

From: Gene Wirchenko on 11 Aug 2010 15:13
On Tue, 10 Aug 2010 16:48:00 +0200, "Jan Waiz" <JWaiz(a)dennsohh.local>
wrote:

>Hi All,
>
>working with a SQL-Server 2005 i have a problem generating indicators (like
>kpi) on current dynamic data. Let me describe:
>
>Given is a dataset as this:
>
>Supplier Year Amount
>-------- ----- --------
>Supp1 2008 10
>Supp1 2009 15
>Supp1 2010 13
>Supp2 2008 15
>Supp2 2009 03
>Supp2 2010 05
>Supp3 2008 12
>Supp3 2009 10
>Supp3 2010 11
>
>Three Suppliers with their Amounts (whatever the amount is isnt important
>here) for the last three years. In the report a need a matrix like this:
>
>Supplier Total Best
>-------- ------ -----
>Supp1 38 No
>Supp2 23 Yes
>Supp3 31 No
^^
This should be 33.

>For the first two columns no problem - the matrix has a rowgroup on field
>"Supplier" and for the column "Total" in the datacell i have an expression
>like SUM(Amount). Anyhow - all this works fine. But now:
>
>I need to indicate the "best" (in real word by setting a color instead of a
>text "No" or "Yes") on dynamic data of the sum for each supplier/all years.
>All examples i find in internet using constructs like IIF(Total < 10,
>"red") - but i cant make the "10" fix - i have to determine them on the fly,
>because the values can change and the smallest value should win...
>
>Any idea? Any Tip? Any Solution? *smile*

I am just getting into SQL Express so I do not know the answer
for that. I am, however, experienced with the Visual FoxPro SQL
dialect.

Here is my VFP solution. I invite a conversion to SQL Express.
In particular, I do not like the left join. I may be missing
something there.

***** Start of Included Code *****
* try
* Try to Generate a Query
* Last Modification: 2010-08-11

? "*** Execution begins."
? program()

close all
clear all

set talk off
set exact on
set ansi on

*

create cursor basedata;
(;
Supplier c(5) not null,;
Yr n(4) not null,;
Amount n(2) not null;
)

insert into basedata;
(Supplier, Yr, Amount);
values;
("Supp1",2008,10)
insert into basedata;
(Supplier, Yr, Amount);
values;
("Supp1",2009,15)
insert into basedata;
(Supplier, Yr, Amount);
values;
("Supp1",2010,13)
insert into basedata;
(Supplier, Yr, Amount);
values;
("Supp2",2008,15)
insert into basedata;
(Supplier, Yr, Amount);
values;
("Supp2",2009,03)
insert into basedata;
(Supplier, Yr, Amount);
values;
("Supp2",2010,05)
insert into basedata;
(Supplier, Yr, Amount);
values;
("Supp3",2008,12)
insert into basedata;
(Supplier, Yr, Amount);
values;
("Supp3",2009,10)
insert into basedata;
(Supplier, Yr, Amount);
values;
("Supp3",2010,11)

* Cursor of Suppliers and Totals
select Supplier, sum(Amount) as Total from basedata;
group by Supplier;
into cursor totals

* Cursor of Suppliers that have the best values
select Supplier;
from totals;
where Total in (select min(Total) from totals);
into cursor best

* Cursor of Best Supplier with boolean flag true
select Supplier, .t. as Best from best into cursor realbest

* Cursor of the Desired Query
select;
totals.Supplier, totals.Total, iif(nvl(Best,.f.),"Yes","No ") as
Best;
from totals;
left outer join realbest on totals.Supplier=realbest.Supplier;
order by totals.Supplier;
into cursor results

browse normal

*

close all
clear all

? "*** Execution ends."

return
***** End of Included Code *****

Sincerely,

Gene Wirchenko
 | 
Pages: 1
Prev: Weekly Maintenance Plan
Next: Sql Server 2000 restore to different db name?