# Matrix table add year over year variance • ### 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