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

    Crosstab Report


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

        • 1. Re: Crosstab Report
          jlig Community Member

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

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