?
Solved

SSRS difference/Varience Column between 2 years

Posted on 2011-05-05
10
Medium Priority
?
4,253 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:Aaron Goodwin
  • 5
  • 5
10 Comments
 
LVL 22

Expert Comment

by:Nico Bontenbal
ID: 35698818
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
 

Author Comment

by:Aaron Goodwin
ID: 35698953
No problem just saving it now
0
 
LVL 22

Expert Comment

by:Nico Bontenbal
ID: 35698990
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
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 

Author Comment

by:Aaron Goodwin
ID: 35699050
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
 
LVL 22

Expert Comment

by:Nico Bontenbal
ID: 35699449
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
 

Author Comment

by:Aaron Goodwin
ID: 35699462
Trying
0
 

Author Comment

by:Aaron Goodwin
ID: 35699517
I got an error that I can't have nested aggregate functions within aggregate functions
SSRS-Error.docx
0
 
LVL 22

Accepted Solution

by:
Nico Bontenbal earned 2000 total points
ID: 35700111
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.
0
 

Author Closing Comment

by:Aaron Goodwin
ID: 35700207
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
 
LVL 22

Expert Comment

by:Nico Bontenbal
ID: 35700453
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

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Earlier I wrote an article about the new lookup functions (http://www.experts-exchange.com/A_3433.html) that ship with SQL Server 2008 R2.  In this article I’m going to show you another new feature of SSRS 2008 R2, this time in the vis…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Loops Section Overview
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question