SSRS 2005 compare prior year column with current year to create a calculated column in matrix

Hello all,
I'm new to SSRS 2005 and I'm having a terrible time trying to figure this out.  This should be an easy one, but I need it ASAP.  So it's worth 500 points if you can help me out.  

All I need to do is take the current year sales and subtract it from the prior year sales in a SSRS 2005 matrix.  I think this can be done using the "Previous Function", but for the life of me I can't seem to get it to work.  The sales field is in the detail section of the matrix and has a value of "=Sum(Fields!Sales.Value)".

Please see the image I provided for a clearer understanding.

As I said, I'm new to SSRS 2005, so please give examples and make it as idiot proof as possible.
Thanks,
Dave
SSRS-PreviousFunction.bmp
LVL 2
Cristal638Asked:
Who is Participating?
 
Nico BontenbalCommented:
You might find your solution here:
http://ctp.social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/c9ee49e2-472d-4ef5-942f-0244e4d91cb2
Look at the post of Thursday, February 10, 2011 1:33 AM. This also has a link to a sample report.
At the top of this thread someone suggests to do this logic in the query. And I think that it a lot easier than doing it in the report. You can use a subquery to get the values for the previous year.
0
 
planoczCommented:
As a newbe  you  might what to do a tablix table and not a matrix table.
As for now try this in your textboxes/cells..
=(Fields!2010Sales.Value + Fields!2009Sales.Value)   '<---- Go into your 2010 column

=(Fields!2011Sales.Value + Fields!2010Sales.Value)   '<---- Go into your 2011 column
0
 
Cristal638Author Commented:
There is no 2010 or 2011 column to put them in.  They are columns that appear at runtime.  Like a crosstab or pivot.  

Please see the image I provided for a clearer understanding.

FYI - I'm using SSRS 2005.  In SSRS 2008 onward, Table and Matrix were replaced by a new object of "Tablix", which combined the properties of both the older objects. However the design interface still shows Table and Matrix on the available tools, these are both Tablix under the hood but with different default configurations (the "Matrix" shows column groups but the "Table" does not).

Thanks,
Dave
SSRS-PreviousFunction1.bmp
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Nico BontenbalCommented:
A different technique is described here:
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/MS-SQL_Reporting/Q_25351542.html
It needs some adaption because you only have years, making it a bit simpler.
0
 
Cristal638Author Commented:
Thank you for your help.  I ended up writing a stored procedure to deal with this cause I couldn't get the previous function to work in 2005.
0
 
Cristal638Author Commented:
I think the real solution to this issue is to upgrade to 2008 R2
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.