[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2639
  • Last Modified:

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
0
Cristal638
Asked:
Cristal638
  • 3
  • 2
2 Solutions
 
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
 
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now