locked
Matrix table add year over year variance RRS feed

  • Question

  • I am trying to get add a variance column to a matrix report is ssrs 2008 r2. However I am unable to create the custom code needed. I have been working with many examples online but none seem to fit what I am trying to do. Any help would be great. I am trying to calculate the diff at a month level year over year. Like month 1 2013 – month 1 2014

    I have a matrix table with three column grouping

    1. Quarter
    2. Month
    3. Year

    Design view of table

    Quarter

    Month

    Year

    Customer

    Job Location

    Job id

    Example of what I need (Variance)

    1

    1

    variance

    1

    1

    variance

    1

    1

    2

    2

    2013

    2014

    2013

    2014

    Acme

    Acme Philadelphia

    jj123

    100

    150

    50

    150

    100

    -50

    Dataset

    select

    Fisc_year

    ,(case

    when datepart(month,dt) = 11 then 1

    when datepart(month,dt) = 12 then 2

    when datepart(month,dt) = 1 then 3

    when datepart(month,dt) = 2 then 4

    when datepart(month,dt) = 3 then 5

    when datepart(month,dt) = 4 then 6

    when datepart(month,dt) = 5 then 7

    when datepart(month,dt) = 6 then 8

    when datepart(month,dt) = 7 then 9

    when datepart(month,dt) = 8 then 10

    when datepart(month,dt) = 9 then 11

    when datepart(month,dt) = 10 then 12

    end) as Mnth

    ,fisc_qrtr as Qrt

    ,Customer_Name

    ,Job_Name

    ,sum(REVENUE) as REVENUE

    from tbl_whatever

    where Customer_name = @Customername

    and Fisc_year = @Fyear
    Monday, April 7, 2014 7:52 PM

Answers

All replies

  • Hi SQLtl,

    To achieve your requirement, we can directly use expression. For more details, please refer to the following steps:

    1. Right-click the column contains Year field to insert a column with Outside Group-Right.
    2. Then type the following expression to the corresponding cells:
      =Sum(iif(Fields!Year.Value="2014",Fields!REVENUE.value,0))-Sum(iif(Fields!Year.Value="2013",Fields!REVENUE.value,0))

    Besides, we can also use custom code to achieve the same goal, please see:
    http://social.msdn.microsoft.com/Forums/sqlserver/en-US/5f44a0b5-144b-4e6e-b81e-d9b025427748/how-to-get-the-difference-between-two-columns-in-a-column-group?forum=sqlreportingservices

    If there are any other questions, please feel free to ask.

    Thanks,
    Katherine Xiong

    If you have any feedback on our support, please click here.


    Katherine Xiong
    TechNet Community Support

    • Marked as answer by SQLtl Tuesday, April 8, 2014 1:37 PM
    Tuesday, April 8, 2014 9:13 AM
  • This worked for me.

    1. Right-click the column contains Year field to insert a column with Outside Group-Right.
    2. Then type the following expression to the corresponding cells:
      =Sum(iif(Fields!Year.Value="2014",Fields!REVENUE.value,0))-Sum(iif(Fields!Year.Value="2013",Fields!REVENUE.value,0))

    I did have to add val() to the formula for some reason

    =Sum(iif(Fields!Year.Value="2014",val(Fields!REVENUE.value),0))-Sum(iif(Fields!Year.Value="2013",val(Fields!REVENUE.value),0))

    Tuesday, April 8, 2014 1:37 PM