SSRS matrix with multiple groups
I have a dataset that generates results in this.
Parent | NodeName | Value | ID |
---------------------------------------------
AAA | Name | blah1 | 1 |
AAA | val | 1 | 1 |
BBB | tag | tag2 | 2 |
BBB | Name | blah2 | 2 |
BBB | val | 2 | 2 |
AAA | tag | tag1 | 1 |
CCC | Name | blah3 | 3 |
CCC | tag | tag3 | 3 |
CCC | val | 3 | 3 |
AAA | Name | blah4 | 4 |
AAA | tag | tag4 | 4 |
AAA | val | 4 | 4 |
I would like to group this
AAA
-----------------------------------------
| Name | Val | Tag |
-----------------------------------------
| blah1 | 1 | tag1 |
| blah4 | 4 | tag4 |
BBB
-----------------------------------------
| Name | Val | Tag |
-----------------------------------------
| blah2 | 2 | tag2 |
CCC
-----------------------------------------
| Name | Val | Tag |
-----------------------------------------
| blah3 | 3 | tag3 |
I tried some of the tricks from this link but no luck so far. Thank you. http://agilebi.com/jjames/2010/09/10/how-to-pivot-data-in-ssrs/
I tried what @ jimmy8ball suggested in the comments. The design surface looked like this:
The final result looks like
As I only have three groups AAA, BBB and CCC at the moment. I created three different datasets with filters including only one group like AAA. The result looks like this.
I'm not sure what is the best way to do it. Any suggestions are greatly appreciated.
Another update based on @ Jimmy8ball. I can't seem to get it to still work according to @ Jimmy8ball's answer.
The result of my dataset looks like this:
My design surface looks like this.
My result, according to @ jimmy8ball's answer, looks like there is no second set of values for AAA. Any suggestions?
source to share
to update the previous answer, follow the setup below, i modified the matrix to display the NodeName as a split for each parent, as before, remove any auto counts or sums
using line number and section, here helps to maintain grouping levels, here is the sql i used
select parent+'-'+convert(varchar,ROW_NUMBER() over(partition by parent, nodename order by id)) as parent, nodename, value, id
from dbo.test1 t
source to share
One solution is to group by Parent, then ID for Maxtix rows and NodeName for columns (and value for values). It groups the data the way you need it, but the format is not exactly what you wanted.
If you want the column headings to repeat for each parent, one way is to use an external list or tablix to group.
- In your matrix, grouping by ID for rows and NodeName for columns
- Also create a list that has a group of parent groups. (You have no page breaks between group instances)
- Put the matrix in the list
- Add a text box for the parent value in the list box above the matrix.
source to share