Prevent section headers from repeating in SSRS groups

Jay C picture Jay C · Sep 29, 2013 · Viewed 9k times · Source

I am using Visual Studio 2010 with SQL Server 2012

I have made an SSRS report called internationaldomestic.rdl. I have not begun decorating the report yet. I am first trying to get the layout correct. All is well so far except the group values repeat for section header purposes.

I have a sample report called portfolioappraisal.rdl which has a similar layout as internationaldomestic.rdl but the section headers do not repeat.This is my desired output. I cannot figure out how the designer of the sample report got this to work. I Have merged the cells where the group value field is but I can't prevent the detail from being repeated. Please see images below.

Here is the design tab of the report. Note the repeating detail which I cannot eliminate

report

Here is the preview tab. Note how for example Energy repeats over and over. I want it to appear only once at the top of the section.

report

Now here is the design of the sample report. Somehow they were able to get the detail denoted by the three lines to appear only once.

report

Here is the preview. I am not concerned about the design elements just now. I just want to replicate this type of layout with the section header appearing only once. For example Materials appears just once.

report

Here are links to the two rdl files.

International Domestic

https://dl.dropboxusercontent.com/u/87501202/InternationalDomestic.rdl

Portfolio Appraisal

https://dl.dropboxusercontent.com/u/87501202/PortfolioAppraisal.rdl

Thank you for your help.

Answer

Ian Preston picture Ian Preston · Sep 29, 2013

You need to create a Row Group header row, then move the Sector value into this Row Group header row. The header row will repeat once per group, unlike the Details group rows which repeat for every value in the Dataset.

Let's start with a very simple Dataset:

enter image description here

And create a table similar to yours. You can see Sector is repeating for each row:

enter image description here

enter image description here

As above, to solve this we need to add a Row Group header row. The easiest way to do this in the existing report is to click on the drop down for the Details group then click Add Total -> Before:

enter image description here

In this case this adds two Row Group header rows - clear the extra rows up and move Sector to the Row Group header row:

enter image description here

enter image description here

You can see that the Security details are in the Details row - this is the one with the three lines on the left. Sector is now in a Row Group header - there is only a bracket on the left.

If you look at your example screenshot, you can see you have two Details rows, i.e. two rows with the lines on the left of the tablix. Looking at your other report screenshot, there are multiple Row Group header rows, which is why that report is working as required.

Anyway, back to the new example report - we can see Sector is now displaying as required:

enter image description here

In the future, you can add Row Group header rows when you create a group, just make sure Add group header is ticked:

enter image description here