How to Name Worksheets in a Paginated Report Exported to Excel

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.

3 thoughts on “How to Name Worksheets in a Paginated Report Exported to Excel

  1. 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

  2. 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

  3. Pingback: Naming Worksheets in Power BI Paginated Report Excel Outputs – Curated SQL

Leave a Reply