erikTsomik
asked on
cfchart display
I created a query and I am trying to group the result to display the data into the chart with a grouped input.
I want to group data on the display based on the state and the year.
The data result is:
cfml
What I need to get is the data bars for each state for individual year
data.jpg
I want to group data on the display based on the state and the year.
The data result is:
cfml
<cfchart format="flash"
xaxistitle="Year"
title ="DC Sales for 2014-2015"
chartWidth="600"
showBorder="true"
showmarkers="true"
seriesplacement="cluster"
showlegend="true"
yaxistitle="Count">
<cfloop query="qGetDCSales" group="availStateCode">
<cfchartseries type="bar" >
<!---<cfloop group="yearOrdered">--->
<cfloop>
<cfchartdata item="#qGetDCSales.availStateCode#" value="#qGetDCSales.orders#">
</cfloop>
<!---</cfloop>--->
</cfchartseries>
</cfloop>
</cfchart>
What I need to get is the data bars for each state for individual year
data.jpg
ASKER
I am on CF 10 and using SQL Server . and Yes I can modify the query
Ok. I noticed "CA" doesn't have data for all years. Due to the way cfchart handles missing values, it'll cause some weirdness in the chart. You either need to use a custom style to enable "interpolation" (easier) OR use SQL to fill in the missing values.
Here's a CF10 example using a "style". Assumes the db query results are ordered by AvailStateCode, YearOrdered
Trycfm.com / CF 10 Example
Here's a CF10 example using a "style". Assumes the db query results are ordered by AvailStateCode, YearOrdered
Trycfm.com / CF 10 Example
<!--- Add this to handle years with missing values in data --->
<cfsavecontent variable="style"><?xml version="1.0" encoding="UTF-8"?>
<frameChart is3D="false" isInterpolated="true">
<frame xDepth="3" yDepth="1" />
</frameChart>
</cfsavecontent>
<cfchart format="flash"
xaxistitle="Year"
title ="DC Sales for 2014-2015"
showBorder="true"
showmarkers="true"
showlegend="true"
style="#style#">
<!--- Query MUST be ordered by AvailStateCode, then YearOrdered --->
<cfloop query="qGetDCSales" group="AvailStateCode">
<cfchartseries type="bar" seriesLabel="#AvailStateCode#">
<cfloop>
<cfchartdata item="#YearOrdered#" value="#Orders#">
</cfloop>
</cfchartseries>
</cfloop>
</cfchart>
To fill in the missing values in your db query, try something like this. Note, the use of a CROSS JOIN. Those are handy for generating all combinations when a small number of records is involved, ie 2 or 3 yrs and 50 or less states.
SELECT codes.AvailStateCode, yrs.YearsOrdered, COALESCE(t.Orders, 0) AS Orders
FROM
(
SELECT DISTINCT AvailStateCode
FROM YourTable
) codes
CROSS JOIN
(
SELECT DISTINCT YearsOrdered
FROM YourTable
) yrs
LEFT JOIN YourTable t
ON t.AvailStateCode = codes.AvailStateCode AND t.YearsOrdered = yrs.YearsOrdered
ASKER
I have your suggestion. I still can not get it to work . it shows the same values and it does not show the CA at all .
Screenshot_1.jpg
Screenshot_1.jpg
Couple things
1) The query results aren't ordered correctly. When you use "group" the query MUST be ordered the same way as the CF group statements or it won't work.
2) Make sure you're using the "style" attribute, ie style="#style#" (not sure if the code is doing that)
3) To get the X-axis ordered by YearsOrdered ASC, you'll probably need to fill in the missing values with the SQL query above. To that end, can you post your current SQL as text?
EDIT: BTW, unless you need this query for something else on the page, it can be simplified for the chart.
1) The query results aren't ordered correctly. When you use "group" the query MUST be ordered the same way as the CF group statements or it won't work.
2) Make sure you're using the "style" attribute, ie style="#style#" (not sure if the code is doing that)
3) To get the X-axis ordered by YearsOrdered ASC, you'll probably need to fill in the missing values with the SQL query above. To that end, can you post your current SQL as text?
EDIT: BTW, unless you need this query for something else on the page, it can be simplified for the chart.
ASKER
here is the query
SELECT T.yearOrdered,t.Orders,t.availStateCode
FROM (
SELECT YEAR(orderDate) as yearOrdered, Count(*) as Orders ,LAS.availStateCode,LI.officeStateKey
FROM Orders O
inner join lineitems LI on Li.orderKey = O.orderKey
inner join lkup_availState LAS on LAS.availStateKey = LI.officeStateKey
where O.void <> 'Y'
and Li.productID = 'DC'
and CONVERT(date,O.orderDate,120) >= '1/1/2014' and CONVERT(date,O.orderDate,120) < '1/1/2016'
GROUP BY LI.officeStateKey,YEAR(O.orderDate),LAS.availStateCode
)T
ORDER BY t.officeStateKey,t.yearOrdered,T.availStateCode
Try fixing steps #1 (sorting) and #2 (use style attribute) first. Then we can add in the missing state/years. Use this slightly simplified version of the query (not tested):
SELECT T.yearOrdered,t.Orders,t.availStateCode
FROM (
SELECT YEAR(orderDate) as yearOrdered, Count(*) as Orders, LAS.availStateCode
FROM Orders O
inner join lineitems LI on Li.orderKey = O.orderKey
inner join lkup_availState LAS on LAS.availStateKey = LI.officeStateKey
where O.void <> 'Y'
and Li.productID = 'DC'
--- avoid CONVERT because it prevents the db from using indexes on that column
and O.orderDate >= '2014-01-01'
and O.orderDate < '2016-01-01'
GROUP BY YEAR(O.orderDate), LAS.availStateCode
)T
ORDER BY T.availStateCode, t.yearOrdered
ASKER
I tried changing the query as provided and used the style sheet as recommended but no luck.
<cfsavecontent variable="style"><?xml version="1.0" encoding="UTF-8"?>
<frameChart is3D="false" isInterpolated="true">
<frame xDepth="3" yDepth="1" />
</frameChart>
</cfsavecontent>
<cfchart format="flash"
xaxistitle="Year"
title ="DC Sales for 2014-2015"
chartWidth="600"
showBorder="true"
showmarkers="true"
showlegend="true"
style="#style#"
yaxistitle="Count">
<cfloop query="qGetDCSales" group="AvailStateCode">
<cfchartseries type="bar" seriesLabel="#AvailStateCode#">
<cfloop>
<cfchartdata item="#qGetDCSales.yearOrdered#" value="#qGetDCSales.orders#">
</cfloop>
</cfchartseries>
</cfloop>
</cfchart>
> it shows the same values and it does not show the CA at all .
What's your full CF version number? What does your latest chart look like? With the new query it shouldn't repeat the values, and with the data from your dump the chart should look like this (CF10,0,16,293499). EDIT: Run the sample code in your environment just to verify.
* Important, the code won't work correctly unless the query results are sorted by AvailStateCode code FIRST, then YearOrdered
It only seems like some of the values aren't visible due to the large difference between the highest and lowest value (ie 3215 vs 8). The full range won't be visible on the chart unless it's much taller. Notice if you set the height to 800 or 1200, the lower values (ie 8, 11, ...) are just visible on the x-axis:
What's your full CF version number? What does your latest chart look like? With the new query it shouldn't repeat the values, and with the data from your dump the chart should look like this (CF10,0,16,293499). EDIT: Run the sample code in your environment just to verify.
* Important, the code won't work correctly unless the query results are sorted by AvailStateCode code FIRST, then YearOrdered
It only seems like some of the values aren't visible due to the large difference between the highest and lowest value (ie 3215 vs 8). The full range won't be visible on the chart unless it's much taller. Notice if you set the height to 800 or 1200, the lower values (ie 8, 11, ...) are just visible on the x-axis:
ASKER
Is there a way to go around this and to show the small values. Maybe changing the Y count to a smaller number by 100
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
2. Which dbms? Can you modify the db query SQL, ie qGetDCSales?