Link to home
Start Free TrialLog in
Avatar of erikTsomik
erikTsomikFlag for United States of America

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
<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>

Open in new window



What I need to get is the data bars for each state for individual year
data.jpg
Avatar of _agx_
_agx_
Flag of United States of America image

1. Which version of CF? The charting engine changed in CF11.  
2. Which dbms? Can you modify the db query SQL, ie qGetDCSales?
Avatar of erikTsomik

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

<!--- 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>

Open in new window

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

Open in new window

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
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.
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 

Open in new window

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

Open in new window

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>

Open in new window

>  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


User generated image
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:
User generated image
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
Avatar of _agx_
_agx_
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial