Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Frustrated with Matrix Report Rate Topic Display Mode Topic Options
Author
 Message
 Posted Friday, July 4, 2014 5:18 PM
 SSC 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 numbersWe would likeRegion | Yr1 - Sales | Yr2 - Sales | Yr3 - Sales | Yr1 - Numbers | Yr2 Numbers | Yr3 - NumbersBasically 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 ofRegion | Yr1 - Sales | Yr1 - Numbers | Yr2 - Sales | Yr2 - Numbers | Yr3 Sales | Yr3 - NumbersI'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?ThanksEliza
Post #1589450
 Posted Monday, July 7, 2014 12:06 PM
 Right there with Babe Group: General Forum Members Last Login: Yesterday @ 9:51 PM Points: 745, Visits: 4,766
 Eliza,Region | Yr1 - Sales | Yr2 - Sales | Yr3 - Sales | Yr1 - Numbers | Yr2 Numbers | Yr3 - NumbersWhat 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...
RegionSalesMatrixNumbersMatrix
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 Group: General Forum Members Last Login: Yesterday @ 4:42 PM Points: 1,787, Visits: 5,695
Post #1590145
 Posted Thursday, July 10, 2014 4:54 AM
 SSC Journeyman Group: General Forum Members Last Login: Yesterday @ 2:49 AM Points: 83, Visits: 690
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

 Permissions