This question comes up every few years in SQL Server Reporting Services. Of course, in Power BI Paginated Reports, we have the same features. A couple of days ago, Karthik posted this question as a comment to my post titled Chapter 7 – Advanced Report Design:
I am working on a SSRS report where the grouping is done to group the records in to multiple tabs/worksheets. When the report is exported to excel, the worksheets has the default name (Sheet1, Sheet2, Sheet3,…). Here I want to override the default worksheet name with (Tab1, Tab2, Tab3, …). The number of tabs/worksheets varies each time depending on the parameter selection by the user. How to address this? any suggestions please.
There are a couple of requirements to set this up. First, you must drive report pagination using a Group and then set a Page Break on the Group. This figure shows that I am grouping my table data region on the Brand field and then setting a page break on that group. You can get to the Page Break settings by using the little arrow next to the group item in the Row Groups pane or you can use the properties window like I’m doing here.
Next, you want to give the page a name under the PageBreak properties. Expand PageBreak and then use the Expression builder in the PageName property to use the grouped field name in an expression. In this case, I am referencing the Brand field.
The result is shown here when I open the file resulting from exporting this report to Excel. You can see that the worksheet tabs are titled with each Brand value and each sheet contains that product information group by the corresponding brand.
I was able to implement the design outlined above, and when I export my excel file it has separate tabs per category as designed, but the category doesn’t populate each sheet name correctly in excel. It repeats the first category, and then assigned the same category to the next tab with an increment. IE store 070, store 070 (1), store 070 (2), etc… Has anyone else experienced this?
Is there a way to keep the headers on each worksheet per group? For example I have a group by location on each worksheet. When I export to excel it shows the locations on individual worksheets but the header does not come across.
Hello Paul,
Thank you for the amazing and informative article. I am trying to export a table in the paginated report to the CSV file, I cannot see the complete data. Only the data that is present on the page 1 is getting exported to the CSV file. Is there a way to export the complete data?
Thanks in advance,
Phani
Hello Paul,
This is an amazing write up. I stumbled on your ”Airline Ontime Performance Tracker – Power BI” video on Youtube from 5 years ago, I am really impressed and i would love to learn how you build the OTP tracker report or if you could direct me to resources that could help me understand it.
I recently went for a technical test that required me to track OTP and i didn’t do so well and I felt bad and I always endavour to improve continously. This is one of the reason i want to further understand how you develop the report. I will be glad if you reply my messages. Thanks
Hi Paul,
Thank you for your response. I was able to get the worksheet named as Tab1, Tab2, Tab3,…
The problem I had was that the grouping was done based on a different field name “Title” from the table data. I was wondering how to get a custom expression with incremental value in the PageName property. But while I was reading your response carefully the phrase “First, you must drive report pagination using a Group” sparked the thought to bring in a additional field in the table data.
So this is what I did in order to have Tab1, Tab2, Tab3, … as worksheet names, here I had to bring in this field (below line of script) in the table data.
, ‘Tab’ + CAST(ROW_NUMBER() OVER (ORDER BY TITLE) AS VARCHAR(10)) AS TABNAME
Again, thank you for your response.
Thanks
Karthik