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

Frustrated with Matrix Report Expand / Collapse
Author
Message
Posted Friday, July 4, 2014 5:18 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, July 5, 2014 1:33 PM
Points: 33, Visits: 81
Hi All,

I'm Getting a little frustrated with the matrix report not being able to achieve what we had in mind for our report. We are wanting to report on 3 year numbers for Sales and Customer numbers

We would like

Region | Yr1 - Sales | Yr2 - Sales | Yr3 - Sales | Yr1 - Numbers | Yr2 Numbers | Yr3 - Numbers

Basically we would like to group the sales together first, followed by the numbers second.

But I do not seem to be able to create the two groups, every time I try I get an output of

Region | Yr1 - Sales | Yr1 - Numbers | Yr2 - Sales | Yr2 - Numbers | Yr3 Sales | Yr3 - Numbers

I'm sure there is a simple solution to the problem, the best I can think of is Union ALL on a query that selects the same data but surely there is an easier way?

Thanks
Eliza
Post #1589450
Posted Monday, July 7, 2014 12:06 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Today @ 12:47 PM
Points: 710, Visits: 4,541
Eliza,

<cheat sheet>Region | Yr1 - Sales | Yr2 - Sales | Yr3 - Sales | Yr1 - Numbers | Yr2 Numbers | Yr3 - Numbers

What if you had a tablix with Region in the left column and then a nested matrix for Sales and another for Numbers? So in HTML-speak...

<table>
<tr>
<td>Region</td><td>SalesMatrix</td><td>NumbersMatrix</td>
</tr>

and you joined the two by the Region? (create a subreport and link by Region).
Post #1590046
Posted Monday, July 7, 2014 4:19 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 4:19 PM
Points: 1,786, Visits: 5,682
What is the structure of the dataset?

MM


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1590145
    Posted Thursday, July 10, 2014 4:54 AM


    SSC Journeyman

    SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

    Group: General Forum Members
    Last Login: 2 days ago @ 3:59 AM
    Points: 83, Visits: 683
    This is where Adjacent Groups come in handy.

    Just create an adjacent group for the same dimension, i.e. Years and then use the Sales measure under the first and the Numbers Measure under the second. You then have "region" on rows and two groups of year on columns, adjacent and not nested.





    I've attached an example from adventureworks, and the images since the imgur embedded links dont seem to work in my post.


      Post Attachments 
    Report1.rdl (4 views, 14.19 KB)
    Capture1.JPG (8 views, 16.19 KB)
    Capture2.JPG (5 views, 52.67 KB)
    Post #1591079
    « Prev Topic | Next Topic »

    Add to briefcase

    Permissions Expand / Collapse