Problems making stacked graphs 100% dynamic

[devashanti]

One of my co-workers said there was no way to use one sql statement for the stacked bar graph (FC_2_3_StckdColumn2D.swf). The graph looks fine doing this part hardcoded and part dynamic method, but I had to add a link to for each stack to a tabular chart with detail data and I end up without a way to pass a date value as well as a stack value for the where clause in the chart sql. We are using the Open ACS environment with TCL and AOL server. 

This is a simplified example of my graph xml: (I am adding comments here to explain:

<graph caption='Proposition 1D State Match Funding FY 2007-08 Current Status' subcaption='' xAxisName='Timeline' yAxisName='Funding' numberPrefix='$' showNames='1' decimalPrecision='0'  yAxisMaxValue='500000000' numdivlines='9'>

<categories font='Arial' fontSize='10' fontColor='000000'>

  <multiple name="tcbc"> //variable 4 the months dataset

<category name='@tcbc.date_monyr@' /> //variable 4 mon/yr

  </multiple>

</categories>

<dataset seriesname='Stack1' color='666699' showValues='1' alpha='100'>

  <multiple name="s1"> //variable for 1st stack dataset

  <set value='@s1.total@' link="chart?passedvar=@s1details@" />

  </multiple>

</dataset>

<dataset seriesname='Stack2' color='6666CC' showValues='1' alpha='100'>

  <multiple name="s2"> //variable for 2nd stack dataset

  <set value='@s2.total@' link="chart?passedvar=@s2details@" />

  </multiple>

</dataset>

<dataset seriesname='Stack3' color='6699CC' showValues='1' alpha='100' >

  <multiple name="s3"> //variable for 3rdstack dataset

  <set value='@s3.total@' link="chart?passedvar=@s3details@" />

  </multiple>

</dataset>

</graph>

In my actual xml I have more stacks but this gives you the idea. Insead of one dataset there are separate datasets for the categories which in this case are months with years for each bar in the graph.

Then there is a separate dataset for each of the stack values which represent summed values/stack. I used an order by clause in my sql statements to match stack data to the appropriate bar in the graph. But there is no way that when a user clicks on a stack in the graph I can pass the appropriate date value for the bar they clicked as a variable to the chart's sql's where clause so they will get data specific to the stack value for the bar they clicked, instead they get details for all dates, and they are not happy with this.

I cannot believe Fusion Charts doesn't handle this. Like I said I would have done this as a single sql, but we are against the gun to get an application released. When I used one SQL all my stacks repeated in one bar instead of creating a new bar for each month/year.

Any ideas on how to solve this issue?

Edited February 12, 2008 by Guest