dpelan (1/6/2013)
I have a matrix reporting data by week by location. The matrix works great for this. The challenge, however, is I want to take my total from the matrix and provide a 3 month average - easy enough to do with another matrix. Then i need to take the last weeks data (matrix 1) compare it to the 3 month average (matrix 2) and calculate a variance. Then show the max value from the 3 months and calculate a variance against the last weeks data.for example
location A location B location C
Week 1 100 300 350
Week 2 550 350 700
Week 3 250 550 450
Total 900 1200 1500
Weekly Avg 300 400 500
Last Week Var -50 150 200
Max 550 550 700
Variance -54% 0% -35%
Any suggestions on how to accomplish the bottom calculations when you can't use fields from various matrices in a report?
Thanks in advance.
You can have a look at:
https://technet.microsoft.com/en-us/library/ms157334%28v=sql.100%29.aspx
https://www.simple-talk.com/sql/reporting-services/advanced-matrix-reporting-techniques/