locked
How do I get the last 12 months to show for a revenue column in Report Builder? RRS feed

  • Question

  • Here is the current expression code for revenue, not filtered by date:

    =iif(Fields!ContractAmt.Value = 0 OR Fields!JTDEstimate.Value = 0, 0 ,Fields!ContractAmt.Value * (Fields!JTDCost.Value / Fields!JTDEstimate.Value))

    Here is the SQL query from the dataset called DivisionWorkload, which it is pulling from in the tablix property. Although I am confused because there is another dataset called Revenue that has fields for each Revenue month. How do I make the tablix pull from different datasets? Here is the DivisionWorkload code.

    SELECT 
    JCJM.JCCo AS Company, 
    JCJM.Contract, 
    JCJM.Description, 
    JCJM.ProjectMgr, 
    JCMP.Name, 
    JCCM.Department, 
    JCCM.ContractAmt, 
    JCCM.BilledAmt, 
    SUM(JCCP.CurrEstCost) AS JTDEstimate, 
    SUM(JCCP.ActualCost) AS JTDCost


    FROM JCJM 
    INNER JOIN JCMP ON JCJM.JCCo = JCMP.JCCo AND JCJM.ProjectMgr = JCMP.ProjectMgr 
    INNER JOIN JCCM ON JCJM.JCCo = JCCM.JCCo AND JCJM.Contract = JCCM.Contract
    INNER JOIN JCCP ON JCJM.JCCo = JCCP.JCCo AND JCJM.Contract = JCCP.Job

    WHERE JCJM.JCCo = 30 AND JCCM.Department = @Department

    GROUP BY
    JCJM.JCCo,
    JCJM.Contract, 
    JCJM.Description, 
    JCJM.ProjectMgr, 
    JCMP.Name, 
    JCCM.Department, 
    JCCM.ContractAmt, 
    JCCM.BilledAmt



    Tuesday, October 10, 2017 4:01 PM

All replies

  • Hi Shea Kennisher,

    In the report, you are using two datesets, right? How are those two datasets like? And you would like to use two datasets field in one expression for the column "Revenue"? 

    If the two datasets have the common field, you could combine them in the SSRS dataset query and merge them into one dataset. Also, you could add a report parameter "Revenue month", and filter the column "Revenue" based on the "Revenue month" parameter. 

    Best Regards,

    Henry 

    • Proposed as answer by Henry Jiang Thursday, October 19, 2017 9:50 AM
    Wednesday, October 11, 2017 6:22 AM