Skip navigation
stone2dbonetone
Currently Being Moderated

Crosstab Report

Dec 29, 2010 7:51 AM

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

 
Replies
  • Currently Being Moderated
    Feb 9, 2011 8:11 AM   in reply to stone2dbonetone

    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

     
    |
    Mark as:

More Like This

  • Retrieving data ...

Bookmarked By (0)

Answers + Points = Status

  • 10 points awarded for Correct Answers
  • 5 points awarded for Helpful Answers
  • 10,000+ points
  • 1,001-10,000 points
  • 501-1,000 points
  • 5-500 points