CFCHART - Multiple values, multiple elements over time

[MsChelle]

I need to create a dynamic chart page that charts multiple values for multiple elements over time.

For example, a query could be something like:

select element, date_val, value
from table
where element IN('abc','bcd','def') and date_val > '5/5/2007'

Output:
abc,5/5/2007,10
abc,5/6/2007,15
abc,5/7/2007,8
bcd,5/5/2007,12
bcd,5/6/2007,15
bcd,5/7/2007,20
def,5/5/2007,10
def,5/6/2007,27
def,5/7/2007,2

The number of elements is dynamic - anywhere from 1 to 20. The number of dates is dynamic, too.

I need it to chart one line for each element, with values over time.

Any ideas?? Am I missing something obvious?

 

[NorthStarDA]

i would group it by date, for instance if you were using a column chart, each day would be a point on the X axis and you could use a multi-series (stacked columns) to show each element in that day. your 'value' being on the y axis. does this help?

=========================================
Don't sweat the petty things and don't pet the sweaty things.

 

[MsChelle]

I tried grouping by element, then date, then value. The closest I got it to working was grouping by element and putting a loop over cfchardata's.

Most of my various attempts gave me either a correctly formatted graph with no data, but the legend and dates were correct. One arrangement got me all the data, and it seemed to be OK, but it was all squished to the left side of the chart, with the whole right side blank no matter how wide I made it.

I finally settled for looping through a query of queries for each element, but that, of course, makes it pretty slow. It shows up correctly, though I'm not thrilled with the performance.

 

[lucidtech]

Not sure if this will help or not, but my best guess would be using cfswitch and case values and creating a better designed chart depending on what dynamic value is passed in to it. Of course this would only work if you had a maximum value for the switch statement, but you said the dates range from 1 to 20, so it sounds like you do. CF can go through 20 case statements much faster than it can loop over a query.