Here’s an easy way to alter your group header on subsequent pages in SSRS, without using any custom code.
Step 1: Add a row number by your grouping to the data set query
SELECT ROW_NUMBER() OVER(PARTITION BY Layout_Code ORDER BY Product_ID) AS Row ,[Layout_Code] ,[Product_ID] ,[Variant_Code] FROM[DIM_Product]
Step 2: Be sure your Row Group header is set to Repeat on New Page
Do this by selecting the small triangle at the top right of the grouping pane and turning on Advanced Mode. Then select the Static member at the top of your group and set the RepeatOnNewPage property to True.
Step 3: Add the Row field in your report
Add the =First(Fields!Row.Value) field into the group header row of your report. Call the textbox RowGroup.
Add the Row field into the detail row of your report. Call the text box RowDetail.
These fields will be hidden later.
Step 4: Add the Group Header expression
Set the Group Header expression like this:
=iif(ReportItems!RowGroup.Value=ReportItems!RowDetail.Value, Fields!Layout_Code.Value, Fields!Layout_Code.Value + ” Continued”)
Notice in the preview of the report that on the first page of the report the RowGroup textbox = 1 and the RowDetal textbox = 1. The Iif statement dictates that the group header shows the Layout_Code value.
On page 2 of the report, since the Layout group continues and 1 <> 49, the work “Continued” is added to the group header.
This will carry on until a new group starts and the row number goes back to 1. You can go ahead and resize and hide the column once you have the logic working.
Hi Martina, this post was of GREAT help for us. But made it work using a small variation of your suggestion, so thank you very much!!
We weren’t unable to make it work when exporting to PDF. Could you provide any leads on this?
Sorry, I’m hearing about this issue with PDF. Next time I work on this I’ll investigate.
Hi,
I’ve tried the same thing, but the ReportItems!RowDetail.Value is returning always 1.
Any ideas?
HI MC,
I would guess there is an issue with the SQL query. In the example below, the row number will start over at the beginning of each Layout_Code grouping as defined by the PARTITION BY statement.
SELECT
ROW_NUMBER() OVER(PARTITION BY Layout_Code ORDER BY Product_ID) AS Row
,[Layout_Code]
,[Product_ID]
,[Variant_Code]
FROM[DIM_Product]
Hi, good article, the only thing that I see is the the render process for example in PDF that is different when is visualized in SSRS
Do you have an example where the PDF render process didn’t work correctly? I haven’t seen that.
Just wanted to say thank you for posting this. It’s extremely helpful. I’ve used it several times on long list type reports where the same group header gets repeated across multiple pages.
This will work in reportviewer mode. But when rendered in PDF the continued will not work. Is there any other way to achieve this that would be consistent .
Thanks
I’ve heard from a few of you about the PDF issue. Next time I work on this I’ll investigate. May not be a workaround.
Thanks for this article. I was wondering though if you can add the “continuted” to a Title row ABOVE the column headers. Thanks and nice work!