Crosstab Report

I use the following technique to produce Crosstabs in the ColdFusion Report Builder 9 :

(Note: The Pie & Bar charts above in my final report use their own querys)

----------------------------------------------------------------------------------------------------------

SELECT    tblLocation.location,  SUM(tblSalesReport.srQty) AS SumSale,
tblSalesReport.srPlanCost, tblCCReps.CCRepName, SUM(tblSalesReport.srQty)* .1 AS SumSales,
((SUM( (tblSalesReport.srQty) ) / ( SELECT SUM( (tblSalesReport.srQty)) FROM tblSalesReport)) * 1 ) AS percentage

  , SUM(IF(tblSalesCatg.scDesc = "Local Phone", (tblSalesReport.srQty),0)) AS `LocalPhone`
  , SUM(IF(tblSalesCatg.scDesc = "LD", (tblSalesReport.srQty),0)) AS `LD`
  , SUM(IF(tblSalesCatg.scDesc = "DU Internet", (tblSalesReport.srQty),0)) AS `DUInternet`
  , SUM(IF(tblSalesCatg.scDesc = "DSL", (tblSalesReport.srQty),0)) AS `DSL`
  , SUM(IF(tblSalesCatg.scDesc = "Wireless", (tblSalesReport.srQty),0)) AS `Wireless`
  , SUM(IF(tblSalesCatg.scDesc = "T-1", (tblSalesReport.srQty),0)) AS `T1`
  , SUM(IF(tblSalesCatg.scDesc = "CAI Fiber", (tblSalesReport.srQty),0)) AS `CAIFiber`
  , SUM(IF(tblSalesCatg.scDesc = "Bus Fiber", (tblSalesReport.srQty),0)) AS `BusFiber`
  , SUM(IF(tblSalesCatg.scDesc = "Resold Fiber", (tblSalesReport.srQty),0)) AS `ResoldFiber`
  , SUM(IF(tblSalesCatg.scDesc = "Cellular", (tblSalesReport.srQty),0)) AS `Cellular`
  , SUM(tblSalesReport.srQty) AS Total

FROM      tblCCReps RIGHT OUTER JOIN tblSalesReport ON tblCCReps.CCRepID = tblSalesReport.srRepCom LEFT OUTER JOIN tblCustType ON tblCustType.ctID = tblSalesReport.srCustType LEFT OUTER JOIN tblSalesCatg ON tblSalesCatg.scID = tblSalesReport.srSalesCatg LEFT OUTER JOIN tblCallPlan ON tblCallPlan.cpID = tblSalesReport.srCallPlan LEFT OUTER JOIN tblCarrier ON tblCarrier.crID = tblSalesReport.srCarrier LEFT OUTER JOIN tblLocation ON tblLocation.loID = tblSalesReport.srLocation LEFT OUTER JOIN tblConTerm ON tblConTerm.coID = tblSalesReport.srTerm

WHERE tblSalesCatg.scID IN ('1', '2', '3', '4','5', '6', '7','8', '9', '10') AND DATE(srCompDate) = CURDATE()

GROUP BY tblLocation.location, tblCCReps.CCRepName
ORDER BY tblLocation.location, tblCCReps.CCRepName

-----------------------------------------------------------------------------------

This is the Report Builder Layout in Design View

This is the Table relationship in Report Builder