Anyone know how i can do this with flex charts??

grahamcooke

Hi,

Really hope someone can help, have to demo an application tomorrow and so far i have not been able to get this to work!

I am trying to plot 3 lines on one chart representing a baseline year, previous year and current year. I have retrieved my data and i know that's working fine.

I have tried with a basic data set like this:

SELECT top 1000 sum(con_kwh) as con_kwh, fiscal_month, fiscal_year
from vw_TotalKwh_BY_MONTH_SITE
WHERE site_code = '05-SPTA-50'
and fiscal_year in (2009, 2011, 2012)
group by con_kwh, fiscal_month, fiscal_year
order by fiscal_month, fiscal_year

con_kwh             fiscal_month      fiscal_year
3072       1              2009
2703       1              2011
2523       1              2012
3057       2              2009
2688       2              2011
2502       2              2012
3039       3              2009
2673       3              2011
2487       3              2012
3024       4              2009
2658       4              2011
2472       4              2012
3009       5              2009
2643       5              2011
2457       5              2012
2994       6              2009
2628       6              2011
2442       6              2012
2979       7              2009
2613       7              2011
2427       7              2012
2964       8              2009
2598       8              2011
2412       8              2012
2949       9              2009
2583       9              2011
2397       9              2012
2934       10           2009
2568       10           2011
2484       10           2012
2919       11           2009
2556       11           2011
2904       12           2009
2538       12           2011

and have tried pivoting the data to get three rows for each year like this:

SELECT fiscal_year, [1] AS 'Apr',  [2] AS 'May', [3] AS 'Jun'
    , [4] AS 'Jul', [5] AS 'Aug', [6] AS 'Sep', [7] AS 'Oct', [8] AS 'Nov'
    , [9] AS 'Dec', [10] AS 'Jan', [11] AS 'Feb', [12] AS 'Mar'
    FROM (
      SELECT top 1000 fiscal_year, fiscal_month, sum(con_kwh)
from vw_TotalKwh_BY_MONTH_SITE
WHERE site_code = '05-SPTA-50'
and fiscal_year in (2009, 2011, 2012)
group by fiscal_month, fiscal_year, con_kwh)
AS energy (fiscal_year, fiscal_month, con_kwh)
PIVOT(sum(con_kwh) FOR fiscal_month IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12])) AS P
    order by fiscal_year


fiscal_year          Apr         May       Jun         Jul           Aug        Sep        Oct         Nov        Dec        Jan         Feb        Mar
2009                       3072       3057       3039       3024       3009       2994       2979       2964       2949       2934       2919       2904
2011                       2703       2688       2673       2658       2643       2628       2613       2598       2583       2568       2556       2538
2012                       2523       2502       2487       2472       2457       2442       2427       2412       2397       2484       NULL     NULL

When i use the first data set i set the horizontal axis to fiscal_month and create 3 line series making the data provider of each an array that i created based on the master set containing only that years data. I get all three years worth of months on the xaxis though (so numbers 1-12 repeated  3 times and then one line representing consumption)

I thought the pivoted data would work but i cannot get a line to show at all as i am not sure how to set the x and y axis.

I have not really used flx charting before so this is all new to me. I'm sure this is easy but I genuinely have no idea what to do. If someone could help point me in the right direction you would literally be saving my life!!

Hope someone can help!

grahamcooke

For anyone reading this who has a similar issue, I worked it out. I was on the right track with the pivot but I needed to pivot the data differently. The years needed to be columns not rows!

Mxml code for chart:

<mx:LineChart id="chart"
                                      dataProvider="{dp}"
                                      height="100%" width="100%"
                                      showDataTips="true">
                  <!--<mx:LineChart id="chart"
                                      height="100%" width="100%"
                                      showDataTips="true">-->
                        <mx:horizontalAxis>
                              <mx:CategoryAxis categoryField="fiscal_month" id="month"/>
                        </mx:horizontalAxis>
                        <mx:series>
                              <!--<mx:LineSeries yField="baseYear" dataProvider="{baseYear}"/>
                              <mx:LineSeries yField="prevYear" dataProvider="{prevYear}"/>
                              <mx:LineSeries yField="currYear" dataProvider="{currYear}"/>-->
                              <mx:LineSeries yField="baseYear" displayName="Base Year"/>
                              <mx:LineSeries yField="prevYear" displayName="Previous Year"/>
                              <mx:LineSeries yField="currYear" displayName="Current Year"/>
                        </mx:series>
                  </mx:LineChart>

SQL for pivot:
SELECT fiscal_month, [2009] AS 'baseYear',  [2011] AS 'prevYear', [2012] AS 'currYear'
    FROM (
    SELECT top 1000 fiscal_year, fiscal_month, sum(con_kwh)
      FROM  vw_TotalKwh_BY_MONTH_SITE
      WHERE report_at_level = 'DTE'
      and fiscal_year in (2009, 2011, 2012)
      group by REPORT_AT_TITLE, fiscal_month, fiscal_year, con_kwh)
      AS energy (fiscal_year, fiscal_month, con_kwh)
    PIVOT(sum(con_kwh) FOR fiscal_year IN ([2009], [2011], [2012])) as P
    order by fiscal_month

Result data set (passed to Mxml from webservice and set as data source {dp} for chart )
fiscal_month      baseYear             prevYear              currYear
1              28663    25237    23557
2              28523    25079    23361
3              28373    24939    23203
4              28233    24799    23063
5              28093    24659    22923
6              27953    24519    22783
7              27813    24379    22643
8              27673    24239    22422
9              27533    24099    22310
10           27393    23959    21955
11           27253    23847    NULL
12           27113    23697    NULL


Hope this helps someone!!