im building a report that has multiple tablix in it and i am grouping each one by name. i want to display each table, grouped by the name so that all of the tables for a particular name are on the same page. for example on the first page of the report i should have something like:
| name | address | city |
|---|---|---|
| bob | 123 main st | los angeles |
| name | hobbies | age |
|---|---|---|
| bob | fishing | 44 |
| name | net worth | salary |
|---|---|---|
| bob | $2,400,000 | $78,000 |
and then the second page of the report should have something like:
| name | hobbies | age |
|---|---|---|
| felicia | 54 gordon ct | atlanta |
| name | hobbies | age |
|---|---|---|
| felicia | yoga | 33 |
| name | net worth | salary |
|---|---|---|
| felicia | $750,000 | $224,000 |
where the data for each of these tables comes from either one large query or multiple queries(I've tried it both ways). I have tried multiple approaches to solve this but the one that got me the closest was creating 3 separate subreports (each grouped by name and with page breaks) and putting them all in a main report. When i do this the main report does not respect the page breaks in the subreports and instead puts all of the data for each subreport in a clump like this:
| name | address | city |
|---|---|---|
| bob | 123 main st | los angeles |
| felicia | 54 gordon ct | atlanta |
I have also tried creating 3 separate tablixes and putting them one after another with page breaks but the tables dont show up on the same page, so i end up with everything on separate pages
I've tried tinkering with group settings and also tried to use rectangles but get the same output. Can this be done in SSRS?
There are a few ways to do this but my preference would be using a single sub report.
Create a report that takes 'name' as a parameter (e.g. pName). Build the report as normal so it handles just the data that relates to the name parameter, so make sure your dataset query only returns data for the 'pName' provided. Include all three tablixes and whatever else you want. Test the report and then..
Create another report with a dataset that contains just a distinct list of the names you want to report on.
Add a tablix (table of list) to the report and set it's dataset to be the dataset containing the list of distinct names. This will generate one row per name. Inside the first textbox in the tablix, you need to insert a subreport. Go to the subreport properties, select the report you created at the start of this. In the subreport's parameters section set the pName parameter to the name field of the dataset.
Finally, in the rowgroups panel, go to the rowgroup properties (there will probably only a be a details row group- that's OK) and then set the pagebreak property to 'between each instance'
That should do it.