2 Replies Latest reply on Feb 17, 2011 7:29 AM by stone2dbonetone

    Crosstab Report


      Can this be done in CF Report Builder 9?  If so, how?

        • 1. Re: Crosstab Report
          jlig Level 1

          CF Report builder Final Report.jpg

          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

          CF Report builder layout.jpg

          This is the Table relationship in Report BuilderCF Query table relationships.jpg

          • 2. Re: Crosstab Report
            stone2dbonetone Level 1

            Thanks, but I do not know in advance what the column names will be.  I need a dynamic solution.