SSRS difference/Varience Column between 2 years

Hi Experts,

I have an SSRS report that is grouped by Month for the rows, and Grouped by the Years for the Columns.  When I run the report, SSRS displays years 2008 and 2009, and the aggregated values for each month.  What I need to do next is calculate the Difference between the values for each Month.  I found this article ID: 25351542 but did not understand what to do.  Please keep the solutions within the scope of what SSRS can do.

My dataset already has all the table information created so I have the counts, Fiscal Year, And Month ready.  I just need to subtract the 2 report item values for each month in the Matrix.

cheers
Aaron GoodwinAsked:
Who is Participating?
 
Nico BontenbalConnect With a Mentor Commented:
Strange, that must be the difference between 2008 and 2008 R2 than. Try this than:
=sum(iif(Fields!FYYear.Value="FY2009",1,0)* Fields!Volume.Value)
-
sum(iif(Fields!FYYear.Value="FY2008",1,0)* Fields!Volume.Value)

Open in new window

I don't like this very much because it is one more thing you need to change in the design of the report every year. Wouldn't it be better to have one matrix with 4 years and a difference column for each year. This way you'll also have the difference between 2009 and 2010. We would need to fix the query also a bit so you could have parameters where you could enter a start and a end year and the query and report will adapt itself to these parameters.
1
 
Nico BontenbalCommented:
Can you upload the report as you have it now. Rename it to .txt or .xml before the upload because EE doesn't support .rdl.
0
 
Aaron GoodwinAuthor Commented:
No problem just saving it now
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
Nico BontenbalCommented:
I suppose you want the difference between, for example, jul 2008 and jul 2007 and not between may 2008 and may 2007. So it is previous month or same month previous year.
0
 
Aaron GoodwinAuthor Commented:
Hi Nicobo,

Yes same month previous year.....

Month         2008            2009           Varience
April              9                   5                   (4)
May               5                   9                    4
June             10                10                    0
FY-Compare-SSRS-CODE.txt
0
 
Nico BontenbalCommented:
Try this as the expression for the Difference column of the first Tablix.
=sum(iif(Fields!FYYear.Value = max(Fields!FYYear.Value, "MONTH"),1,0)* Fields!Volume.Value,"MONTH")
-
sum(iif(Fields!FYYear.Value = min(Fields!FYYear.Value, "MONTH"),1,0)* Fields!Volume.Value,"MONTH")

Open in new window

I got to go now. Let me know if it works and I'll explain it to you tomorrow what it does.
0
 
Aaron GoodwinAuthor Commented:
Trying
0
 
Aaron GoodwinAuthor Commented:
I got an error that I can't have nested aggregate functions within aggregate functions
SSRS-Error.docx
0
 
Aaron GoodwinAuthor Commented:
Nicobo, you Rock!

that was exactly what I needed.  My Boss wants the report in this specific format.  That is a great idea for the paramaters though...I may pursue that in another report for my own use.  Excellent suggestion.

Thank you very much!
0
 
Nico BontenbalCommented:
I've thinking about this a bit while walking the dog. I'd create a startyear and endyear parameter. Then with a while loop in the SQL query you fill the #FYVCompare table with the right data for each year. You can use dateadd and dateserial functions to get the right period.
The easiest way to get the differences if by doing this in the sql query also. When you have all the data in the #FYVCompare you only need 1 update query to get the values of the previous month in an extra column. With this data the report is easy. You could also group the rows on
=Year\2
or something so you'll have a layout similar to what you have now. If you always want 4 years only use the endyear parameter (with a default to the current year) and go 4 year back from there.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.