Nthlargest of multiple summaries

[michbell]

I'm using Crystal 9 with Informix

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'm drawing a blank on how to do this. I think it might involve arrays, but I'm not sure how. Any help would be greatly appreciated. Thanks in advance!

 

[synapsevampire]

I would consider pivoting the data into a more Crystal freindly fashion, and then using the conventional Top N in Crystal.

An Add Command or a View could do this using a UNION ALL query, as in:

select 'C1' MyType, {table.c1}, {table.date}
union all
select 'C2' MyType, {table.c2}, {table.date}
union all
select 'C3' MyType, {table.c3}, {table.date}
...

You get the idea.

-k

 

[michbell]

Thanks for the response. I'm guessing you mean do the query in Informix? If so, I don't have access to do that (it's Unix, if that makes a difference).

Is there any other way to get the results I need?

Thanks again!