Frustrated with Matrix Report

• Eliza

  SSC Eights!

  Points: 967

  More actions

  July 4, 2014 at 5:18 pm

  #287577

  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

• pietlinden

  SSC Guru

  Points: 63500

  More actions

  July 7, 2014 at 12:06 pm

  #1727519

  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).

• mister.magoo

  SSC-Forever

  Points: 47174

  More actions

  July 7, 2014 at 4:19 pm

  #1727605

  What is the structure of the dataset?

  MM
  

  ---

  
  select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

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