sql-serverreporting-servicesreport-designer

SSRS - Group Columns Together

All:

I have the following data (see Raw Data attachment). Inside my SSRS Report, I have a column for "Name / Stream / Offer One / Offer Two" (Name is the column for the "Value" column shown in the image).

I need my SSRS Report to display one row of the "totals" for each Name. Then under this, I need a row for each Stream associated with the Name that displays the values for that stream.

For the SSRS Output (please see second screen shot). The data loads into my tablix, but I need one row for "BAG-CV" that displays the total for that Name. Then under this row, I need a row for "AR" stream with the data for.

If you see Name: JAC-FL, it has 5 streams listed under it. So I would need one row for the total of JAC-FL, then 5 rows under it listing all the streams associated with this name.

Hopefully this makes sense.

Raw Data

Report Output

Solution

  • The concept that you are describing is a nested parent/child group (two levels of grouping in a single table). This is quite easy to do and I'll walk you through the steps for your particular scenario thanks to the helpful description and screenshots that you posted.

    1. Create table: I would advise always starting with the innermost grouping and building the table outwards. In your case, the lowest data granularity is Stream/Offer1/Offer2 as these are displayed as raw details with no aggregation:

    enter image description here

    1. Add Parent Group For "Name": Click on the group at the bottom (or in the table) and add a parent group to the current table details group, making sure to include a group header (for the sum):

    Screenshot #1: enter image description here

    Screenshot #2:

    enter image description here

    1. Add Fields: Add the additional fields to your table, ensuring that the middle row is using SUM aggregation as per the screenshot: enter image description here

    2. Results: You should see the additional header row on the parent group with the desired SUM.

    enter image description here

    1. Cleanup: The example above just creates the most basic and bare functionality. I would suggest cleaning it up and adding a visibility toggle on the details group:

    enter image description here

    PS. I realize you probably aren't in control of the database schema, but wow, exclamation marks in column names would drive me up the wall :)