Search

Advertisements

SSRS - How to repeat headers for each group

In this article , I am going to share about the information on
i) how to repeat headers for each group
ii.)how to overcome the error  :

"An error occurred during local report processing .
The definition of the report '/Report ' is invalid .
The tablix 'Tablix' has a detailed member with inner members . Detail members can only contain static inner members ."


For example , consider below sample data :

DECLARE @Tmp TABLE (Country VARCHAR(30),City VARCHAR(30),Population INT)
INSERT @Tmp SELECT 'India','CHN',100
INSERT @Tmp SELECT 'India','MUM',400
INSERT @Tmp SELECT 'India','KOL',300
INSERT @Tmp SELECT 'US','NY',100
INSERT @Tmp SELECT 'US','CAL',300
SELECT * FROM @Tmp






Create Data source (providing server name , login credentials & database name) and then create Dataset as shown below :

 


 Drag & drop List from Toolbox (or right-click on report body Insert - > List) on to the report body
and then drag & drop Table from Toolbox inside the List as shown below :



Then assign Dataset columns to the report Table columns :


After assigning columns , if we preview the report , we will end up with error as shown below :




Again goto the Design Pane , click on the List - > at the bottom , goto Row Groups - > =(Details) ->
in the drop down list click on Group Properties ... 




Under Group Properties , follow the settings as shown below :


Now again click on Preview pane , you will notice headers repeated for each group :




See Also :


 


1 comment:

eoinarmstrong said...

This is excellent, thank you! I used a modelled dataset, rather than a SQL one. Also, I had to manually type in the Group On expression; the field was not available in the combobox. Cheers again - I had spent a day looking up and trying other more long-winded solutions and it was driving me crazy!

Advertisements