I was recently asked if it was possible to name an excel tab based on a page in a reporting service report. Honestly, I did not know if it was possible. My initial response was, I don’t think so. Not being 100% sure, I started searching the web and I stumbled across the What’s New (Reporting Services) site. The site provides a great list of all the new features in SQL Server Reporting Services (SSRS) R2. Not too far down the list I saw the subtitle, Naming Excel Worksheet Tabs. So, how do you do it?
In this example (download my example), I used a report that has a grouping on Countries and Stores. Each country will represent the title for each tab in the Excel spreadsheet.
To start, right click on the Country grouping in the Row Groups section of the Report Designer.
Select Group Properties from the dialogue box that appears and the following screen will open.
Select Page Breaks from the left navigation section and on the Page Break Options screen check the box labeled Between each instance of a group. Then on the Business Intelligence Development Studio (BIDS) menu bar choose View –> Properties. The properties window will open in your development environment. Back in the Row Group section click Country, then in the properties window expand the Group Property.
In the PageName property you will type the expression of the value that will represents the tab names. In this example I have decided to use the Country field as the name for each tab. Click the drop down in the PageName property textbox and choose expression. In the Expression window select Fields from the Category Section and double click Country in the Values section. You screen should resemble the following screenshot:
Now preview your report and export it to excel. Once you open it you will notice that you have a tab for each country.
As always if you have any questions or concerns regarding this post please feel free to email me at pleblanc@pragmaticworks.com.
Talk to you soon,
Patrick LeBlanc
Founder www.TSQLScripts.com and www.SQLLunch.com.
Visit www.BIDN.com, Bring Business Intelligence to your company.
