Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL Report with multiple matrix Expand / Collapse
Author
Message
Posted Sunday, January 6, 2013 6:27 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 16, 2013 11:22 AM
Points: 9, Visits: 70
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.
Post #1403386
Posted Sunday, January 6, 2013 11:12 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 1:53 AM
Points: 467, Visits: 867
Name the cells so they are easy to remember e.g. MonthTotal and then refer to those cells directly in an expression:

=ReportItems!MonthTotal.value

Post #1403410
Posted Monday, January 7, 2013 12:49 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 16, 2013 11:22 AM
Points: 9, Visits: 70
I have tried that but a matrix doesn't allow it. I get the following error message - "Report item expressions can only refer to to other report items within the same grouping scope or a containing grouping scope."
Post #1403825
Posted Monday, January 7, 2013 3:18 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 1:53 AM
Points: 467, Visits: 867
You can use multiple groups in the same tablix that aren't nested within each other. That way your 3 month average doesn't need to be in a second tablix.

You can also add totals or variances in cells that are outside any grouping at all yet still within the same tablix.
Post #1403892
Posted Tuesday, January 8, 2013 9:28 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 16, 2013 11:22 AM
Points: 9, Visits: 70
Excuse my ignorance, but does this work in SQL 2005? If so how do i add another line in my matrix below the total line to to the calcualtions.

Thanks in advance.
Post #1404333
Posted Tuesday, January 8, 2013 4:53 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 1:53 AM
Points: 467, Visits: 867
My apologies I didn't see that it was for BIDS 2005. I've logged on to one of my old SQL Server 2005 machines and luckily it had BIDS 2005 installed on it so I could try.

Wow I forget how painfully bad that version was. The jump to 2008 is light years ahead. I can't work out how to do what I was suggesting in 2005, I'm sorry.

You may have to do something in the SQL to make it work, like make use of pivot/unpivot and cube/rollup.
Post #1404498
Posted Tuesday, January 8, 2013 6:54 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 16, 2013 11:22 AM
Points: 9, Visits: 70
Thanks for checking. I ended up creating a summary table in sql and will populate summary data there for calculations and then report.

I appreciate the time and effort.

Post #1404517
Posted Tuesday, January 8, 2013 8:06 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 1:53 AM
Points: 467, Visits: 867
I think that you've done it the best way possible with 2005.
Post #1404526
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse