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

Example of what I need (Variance)

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

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.