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

    Crosstab Report

    stone2dbonetone Community Member

      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.