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.

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

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

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

Leave a Reply