Lets say we have to show summarized data and detailed data in a report and the summarized data should repeat on all the pages of the report. This is quite common requirement that clients are asking for. Let’s see how to acheive this. There are many ways of doing this but I will demonstrate one way here i.e making use of property “Repeat Page Header”. I have choosen Adventure Works database which is available in almost all computers who are working or learning MSBI. PFB the steps that you have to follow to acheive the same.
- Take a Report and add Data Source.
- Define a dataset and pfb the Screenshot of the query I am using to demonstrate the same here.
- Take two TABLE controls into the report and add the data fields as shown below.
- For the first table I added a ROW GROUP to show summarized data and here we are grouping data based on English Product Category Name as shown in below pic.
- If you Preview the report then it will have two tables one with summarized data and other with detailed data as shown below.
- In the above screen shot we can clearly see the summarized table and detailed table and clients want to see this summarized table in all the pages. Now Merge all the cells of the header of TABLE 2 by selecting all HEADER columns and by selecting the menu item MERGE which is present on RIGHT CLICK as shown in the below screenshot.
- Delete the Header text of Table 2 and CUT the table 1 and PASTE it on Merged Header part and align the columns as shown in below screenshot.
- Now Select the Table 2 and set the property “RepeatColumnHeaders” to TRUE.
- Now click on the SMALL TRIANGLE on Groups and click on “Advanced Mode” and once you select that you can see STATIC in Row Groups and Column Groups as shown below.
- Select STATIC item from Row Groups section and press F4 for properties and select “RepeatonNewPage” to TRUE as shown below.
- Now Preview the report and you can see t able on data in all the pages as shown in below Screenshot.