Realized I forgot to include the CFChart:
<cfchart
chartwidth="800"
chartheight="450"
yaxistitle="Times Presented"
>
<cfchartseries
type="bar"
query="qryGetProducts"
valuecolumn="PCTotal"
itemcolumn="CategoryName"
paintstyle="shade"
>
</cfchartseries>
</cfchart>
- Nick
-----Original Message-----
From: Nick Sweeney [mailto:[email protected]]
Sent: Wednesday, August 18, 2010 3:33 PM
To: cf-newbie
Subject: Cfquery with CfChart - Get Columns with Zero Value
Ok - I think it's just late in the day here - but I can't get this query to
work...
I am building a "Sales Report" application where users(salesmen) mark down
what Products they "demonstrated" on what "sales call". Then I want to
querry the "Sales Calls" to show what products they demoed and what they
didn't over a set period of time. My current query works- but it doesn't
return the products they DIDN't demo... Only the ones they did. (And I would
like a list of both)
There are THREE Tables to Query:
ProductCategory = The Product List (Oven, cabinet, etc)
SalesReports = The Sales Report
ProductCategoryLink = The LInk between the ProductCategory and the
SalesReports
Thanks In Advance - Nick
Query as follows:
<cfquery name="qryGetProducts" datasource="#Application.DSN#"
username="#Application.username#" password="#Application.password#">
SELECT P.CategoryName
,L.PCID
,S.SRID
,COUNT(L.PCID) AS PCTotal
FROM ProductCategory P
Left Outer Join ProductCategoryLink L ON P.PCID = L.PCID
LEFT Outer Join SalesReports S ON S.SRID = L.SRID
WHERE S.DateOfVisit BETWEEN '#variables.StartDay#' AND '#variables.EndDay#'
AND S.ESID = #variables.ThisUser#
GROUP BY L.PCID
ORDER BY P.CategoryName ASC
</cfquery>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion
Archive:
http://www.houseoffusion.com/groups/cf-newbie/message.cfm/messageid:5066
Subscription: http://www.houseoffusion.com/groups/cf-newbie/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-newbie/unsubscribe.cfm
