TopN of Multiple Field Totals 1

[michbell]

I'm using Crystal 9 with Informix (Unix -- and I don't have access to edit or add tables/queries)

The table I'm using lists the daily amount of time spent on various tasks, all with separate fields (c1, c2, c3,...,c30). I summarize each in the group footer for monthly totals. However, I need to somehow display the 3 highest totals for each month in a chart.

what I have now:
{date} {c1} {c2} {c3} {c4} {c5} {c6} {c7} (until c30)
2/1 5 10 2 30 4 10 15
2/2 7 5 5 11 30 5 7
2/3 10 5 1 15 10 2 9
--------------------------------------------------
totals: 22 20 8 56 44 17 31

what I need to graph:
c4=56, c5=44, c7=31

I've posted this question before but only got a suggestion about creating a query in Informix, which I can't do. Does anyone have any ideas how to do this in Crystal? Any help would be greatly appreciated. Thanks in advance!

 

[lbass]

You misinterpreted SynapseVampire's suggestion in your previous thread. He was suggesting that you use a SQL command in CR as your datasource and in the command, use a union statement to combine your fields in such a way that you can use topN. I think his suggestion was correct. I don't have 9.0, but I believe this is one of your datasource options when you start designing a new report. Choose the SQL command option and then try his suggestion there.

-LB

 

[michbell]

Lbass --

Thanks for that clarification. I appreciate it. However, in the Datebase Expert, the "add command" is not an option for Informix. It appears for something like Access, but not for Informix.

Can you think of any other way? Thanks again!!

 

[lbass]

You could determine the top3 by using a formula like:

whileprintingrecords;
numbervar top1;
numbervar top2;
numbervar top3;

if sum({c1},{table.date},"monthly") > top1 then
top1 := sum({c1},{table.date},"monthly") else
if sum({c1},{table.date},"monthly") < top1 and
sum({c1},{table.date},"monthly") > top2 then
top2 := sum({c1},{table.date},"monthly") else
if sum({c1},{table.date},"monthly") < top2 and
sum({c1},{table.date},"monthly") > top3 then
top3 := sum({c1},{table.date},"monthly");

if sum({c2},{table.date},"monthly") > top1 then
top1 := sum({c2},{table.date},"monthly") else
if sum({c2},{table.date},"monthly") < top1 and
sum({c2},{table.date},"monthly") > top2 then
top2 := sum({c2},{table.date},"monthly") else
if sum({c2},{table.date},"monthly") < top2 and
sum({c2},{table.date},"monthly") > top3 then
top3 := sum({c2},{table.date},"monthly");

//repeat for c3 to c30.

You can then reference each topN value in a separate formula:

whileprintingrecords;
numbervar top1;

Before doing this, go to

http://www.BusinessObjects.com

->Crystal support->knowledge base and search for "Charting on Print-time formulas" to make sure this is an approach you can use.

-LB

 

[michbell]

Thanks for the help, Lbass! The formulas worked great. However, with these formulus, I don't know what code (c1, c2, ... C30) the number corresponds with:

whileprinting records;
numbervar top1;
(and then the one for top2 and top3)

So, from the example in my first post, what I need to see is:
c4=56, c5=44, c7=31
and what I am getting from those formulas is:
top1=56, top2=44, top3=31

Do you have any suggestions? Thanks again!!!

 

[lbass]

You could change the formula to:

whileprintingrecords;
numbervar top1;
stringvar name1;
numbervar top2;
stringvar name2;
numbervar top3;
stringvar name3;

if sum({c1},{table.date},"monthly") > top1 then
(top1 := sum({c1},{table.date},"monthly");
name1 := "C1") else
if sum({c1},{table.date},"monthly") < top1 and
sum({c1},{table.date},"monthly") > top2 then
(top2 := sum({c1},{table.date},"monthly");
name2 := "C1") else
if sum({c1},{table.date},"monthly") < top2 and
sum({c1},{table.date},"monthly") > top3 then
(top3 := sum({c1},{table.date},"monthly");
name3 := "C1") else

if sum({c2},{table.date},"monthly") > top1 then
(top1 := sum({c2},{table.date},"monthly");
name1 := "C2") else
if sum({c2},{table.date},"monthly") < top1 and
sum({c2},{table.date},"monthly") > top2 then
(top2 := sum({c2},{table.date},"monthly");
name2 := "C2") else
if sum({c2},{table.date},"monthly") < top2 and
sum({c2},{table.date},"monthly") > top3 then
(top3 := sum({c2},{table.date},"monthly");
name3 := "C3";

I guess you realized you also need a reset formula in the group header for month:

whileprintingrecords;
numbervar top1 := 0;
stringvar name1 := "";
numbervar top2 := 0;
stringvar name2 := "";
numbervar top3 := 0;
stringvar name3 := "";

You can then reference the top1, top2,top3, name1, name2, name3 per month group with separate formulas. I'm not sure how this would work with charting, however. Although I referred you to "Charting on PrintTime Formulas", I've never needed to use that technique, so am not familiar with it.

-LB