locked
Grouping multiple tables (tablix) with different datasets - SSRS 2008 R2 RRS feed

  • Question

  • We have a report that was created in SSRS 2008 R2 that has 3 tables with different datasets that share a common ID that I want to use to group them.

    If we run the report passing only a single value for the grouped parameter then the report works perfectly.  What we need is for this report to allow multiple values to be selected for this parameter and for the report to run as if the user had selected each value one at a time and run the report with page breaks in between.  Currently, when we pass multiple selected values for the grouping parameter the report displays all values for table 1, then all values for table 2, then all values for table three as below:

    Table 1:

    Detail rows for Group Param Value 1 ...

    Detail rows for Group Param Value 2 ...

    Detail rows for Group Param Value 3 ...

    ....


    Table 3:

    Detail rows for Group Param Value 1 ...

    Detail rows for Group Param Value 2 ...

    Detail rows for Group Param Value 3 ...

    But we want it to render like this:

    Table 1:

    Detail rows for Group Param Value 1...

    Table 2:

    Detail rows for Group Param Value 1...

    Table 3:

    Detail rows for Group Param Value 1...

    ---PAGEBREAK----

    Table 1:

    Detail rows for Group Param Value 2...

    Table 2:

    Detail rows for Group Param Value 2...

    Table 3:

    Detail rows for Group Param Value 2...

    ---PAGEBREAK----

    Table 1:

    Detail rows for Group Param Value 3...

    Table 2:

    Detail rows for Group Param Value 3...

    Table 3:

    Detail rows for Group Param Value 3...

    etc

    The page breaks are needed so that when the report is exported to excel each individual report (by group param) will be on its own uniquely named tab.

    The report must export cleanly to excel and currently does for the single value passed.

    Any ideas????

    Tuesday, March 27, 2012 3:15 PM

Answers

  • I would consider using subreports for the 3 tables. 

    Create one table on your main page with 3 columns sized to fit each of your 3 subreports.  Create a parameter on this main report for ID.  Create a dataset that contains the ID values.  Group your table on ID and set it to page break after each ID.

    Create 3 other separate reports, one for each of the original tables.  Add the ID parameter to each of these 3 reports.

    Place 3 subreports in your main report, one in each cell of the table.  Configure the subreports and pass the value of the ID field in the main report to the subreport parameter. 

    Cheers,

    Martina


    Martina White

    • Proposed as answer by Mike Yin Thursday, March 29, 2012 2:02 AM
    • Marked as answer by Elvis Long Monday, April 2, 2012 5:14 AM
    Thursday, March 29, 2012 1:28 AM
  • Hi MinerDev,

    Based on your scenario, there are two workarounds for your reference.

    Workaround 1: If it is acceptable for you to align the three tables horizontally, you can place the three tables horizontally, and then add page breaks to the “Detail” group in the three tables separately. For the details steps, please refer to the “To add a page break to a row group in a table, matrix, or list” node in the following article:
    How to: Add a Page Break

    The report looks like:

    Workaround 2: If you need the tables to be aligned vertically, we need to join the datasets and then place the three tables in a list. To join the datasets to create a new dataset, we can write Transact-SQL query text or use the Graphical Query Designer. For the details information, please refer to the articles below:

    After you create the dataset, please refer to the steps below to design the report:

    1. Create the three tables by using the fields from the newly created dataset.
    2. Drag a “List” control to the Design surface. In the “Tablix Properties” pane, set the “DataSetName” of the list to the new dataset.
    3. Click the list, open the “Group Properties” dialog from the Grouping pane. Click “Add”, and select “[ID]” field in the drop-down list.
    4. Click the “Page Breaks” tab, check the “Between each instance of a group” option. And then drag the three tables to the list.

    The report looks like:

    Reference:
    Adding a List

    If you have any questions, please feel free to let me know.

    Regards,
    Mike Yin

      
    • Marked as answer by Elvis Long Monday, April 2, 2012 5:14 AM
    Friday, March 30, 2012 4:04 AM

All replies

  • I would consider using subreports for the 3 tables. 

    Create one table on your main page with 3 columns sized to fit each of your 3 subreports.  Create a parameter on this main report for ID.  Create a dataset that contains the ID values.  Group your table on ID and set it to page break after each ID.

    Create 3 other separate reports, one for each of the original tables.  Add the ID parameter to each of these 3 reports.

    Place 3 subreports in your main report, one in each cell of the table.  Configure the subreports and pass the value of the ID field in the main report to the subreport parameter. 

    Cheers,

    Martina


    Martina White

    • Proposed as answer by Mike Yin Thursday, March 29, 2012 2:02 AM
    • Marked as answer by Elvis Long Monday, April 2, 2012 5:14 AM
    Thursday, March 29, 2012 1:28 AM
  • Hi MinerDev,

    Based on your scenario, there are two workarounds for your reference.

    Workaround 1: If it is acceptable for you to align the three tables horizontally, you can place the three tables horizontally, and then add page breaks to the “Detail” group in the three tables separately. For the details steps, please refer to the “To add a page break to a row group in a table, matrix, or list” node in the following article:
    How to: Add a Page Break

    The report looks like:

    Workaround 2: If you need the tables to be aligned vertically, we need to join the datasets and then place the three tables in a list. To join the datasets to create a new dataset, we can write Transact-SQL query text or use the Graphical Query Designer. For the details information, please refer to the articles below:

    After you create the dataset, please refer to the steps below to design the report:

    1. Create the three tables by using the fields from the newly created dataset.
    2. Drag a “List” control to the Design surface. In the “Tablix Properties” pane, set the “DataSetName” of the list to the new dataset.
    3. Click the list, open the “Group Properties” dialog from the Grouping pane. Click “Add”, and select “[ID]” field in the drop-down list.
    4. Click the “Page Breaks” tab, check the “Between each instance of a group” option. And then drag the three tables to the list.

    The report looks like:

    Reference:
    Adding a List

    If you have any questions, please feel free to let me know.

    Regards,
    Mike Yin

      
    • Marked as answer by Elvis Long Monday, April 2, 2012 5:14 AM
    Friday, March 30, 2012 4:04 AM
  • These 2 workarounds could work, but only under very specific conditions.

    Workaround 1 will work only if all three of your datasets have the same number of IDs you are grouping on.  If one data set has no results for one of the IDs the page breaks will get out of synch and the IDs won't end up on the same page.  And if your tables have different numbers of rows so that one table overflows onto a second page for an ID, but another table does not overflow, again they would be out of synch. There is nothing in this workaround to keep the tables breaking on the same ID for each page.

    Workaround 2 could work, but depending on your queries the joins could be very cumbersome. Multiple rows for each dataset could result meaning that any summing in the tables may be multiplied by the extra rows, ending up with the wrong value in your sum.  Your example shows that you are not summing, but you would need to group by your detail row to make this workaround work, and if you have any duplicate details you will lose these on grouping.  I don't recommend this workaround unless your 3 datasets are very simple to join.

    Cheers,

    Martina


    Martina White

    Friday, March 30, 2012 12:54 PM
  • Hi Martina,

    I tried as per said by you but it is not working will u give some sample report on adventure works 2012 then it will be helpful for me. 

    my personal mail is is aswinirout1@gmail.com

    Thanks,

    Aswini Rout

    Thursday, October 15, 2015 5:24 PM
  • Hi MinerDev,

    Based on your scenario, there are two workarounds for your reference.

    Workaround 1: If it is acceptable for you to align the three tables horizontally, you can place the three tables horizontally, and then add page breaks to the “Detail” group in the three tables separately. For the details steps, please refer to the “To add a page break to a row group in a table, matrix, or list” node in the following article:
    How to: Add a Page Break

    The report looks like:

    Workaround 2: If you need the tables to be aligned vertically, we need to join the datasets and then place the three tables in a list. To join the datasets to create a new dataset, we can write Transact-SQL query text or use the Graphical Query Designer. For the details information, please refer to the articles below:

    After you create the dataset, please refer to the steps below to design the report:

    1. Create the three tables by using the fields from the newly created dataset.
    2. Drag a “List” control to the Design surface. In the “Tablix Properties” pane, set the “DataSetName” of the list to the new dataset.
    3. Click the list, open the “Group Properties” dialog from the Grouping pane. Click “Add”, and select “[ID]” field in the drop-down list.
    4. Click the “Page Breaks” tab, check the “Between each instance of a group” option. And then drag the three tables to the list.

    The report looks like:

    Reference:
    Adding a List

    If you have any questions, please feel free to let me know.

    Regards,
    Mike Yin

      
    thanks , this really helped me out!
    Wednesday, August 3, 2016 9:19 AM
  • Subreports as mentioned by Mike Yin helped me as well. Thanks!
    Monday, March 11, 2019 5:10 PM