How to Name Worksheets When Exporting SSRS reports to Excel

Home > Blogs > SQL Server > How to Name Worksheets When Exporting SSRS reports to Excel

How to Name Worksheets When Exporting SSRS reports to Excel

1 4Peter Avila
Added by February 18, 2015

Let’s say we have the following report that shows total sales by product category by territory:

001-SQL-server-SSRS-report-to-excel

When we export this report to Excel, we’d like each territory to appear in its own worksheet and each worksheet named after its territory:

002-SQL-server-SSRS-report-to-excel

How do we make this work? Easy! 1) Put every group on its own page, and 2) name each page using the same field the group uses.

Step 1: Put each group on its own page

To put each group on its own page, open the group’s property window.

003-SQL-server-SSRS-report-to-excel

Then, in the Page Breaks category, put a check mark in the Between each instance of a group check box.

004-SQL-server-SSRS-report-to-excel

Click OK to complete this step.

Step 2: Name the pages of the group

With the group selected in the Row Groups panel, press F4 to open the Properties window.

005-SQL-server-SSRS-report-to-excel

Next, expand the Group property and look for the Page Name sub-property. From its dropdown, select <Expression…>.

You may also like:  Microsoft White Paper - SQL Server 2016 and Windows Server 2016 Better Together

006-SQL-server-SSRS-report-to-excel

In the Expression dialog, select the Fields category and then double-click on the same field the group uses; in this case that would be the Territory field.

007-SQL-server-SSRS-report-to-excel

A reference to the field appears in the window at the top of the Expression dialog.

008-SQL-server-SSRS-report-to-excel

Click OK and that’s it! Now, when you export the report to Excel, the worksheet names will match the group names!

Enjoy!

Peter Avila
SQL Server Instructor – Interface Technical Training
Phoenix, AZ

Videos You May Like

Agile Methodology in Project Management

0 72 0

In this video, you will gain an understanding of Agile and Scrum Master Certification terminologies and concepts to help you make better decisions in your Project Management capabilities. Whether you’re a developer looking to obtain an Agile or Scrum Master Certification, or you’re a Project Manager/Product Owner who is attempting to get your product or … Continue reading Agile Methodology in Project Management

Subnetting a TCP/IP Network using the Magic Box Method

0 525 2

In this session, we are going to look at how to subnet a Class B address into multiple network segments using what's called The Magic Box. Let's run through this real quick.

Detailed Forensic Investigation of Malware Infections – April 21, 2015

2 257 2

In this IT Security training video, Security expert Mike Danseglio (CISSP / CEH) will perform several malware investigations including rootkits, botnets, viruses, and browser toolbars.

Write a Comment

See what people are saying...

  1. Silvia

    Thank you!!!

  2. Ron Bevilacqua

    I’ve spent some time hunting for this solution … this works perfectly!!
    Thanks.
    Ron.

  3. Jolanda

    Thank you! Very useful!

  4. Sindhu

    Hi,
    Firstly, thanks for the excellent post.
    I am trying this approach to group data by month part of a date created column.

    But when i export it to excel, I see only one row per sheet even though I have 50 rows per month in database.
    The expression I am using = fields!.month.value

    I also tried this expression directly on the datecreated column. In this case, i still get only one row per sheet, but I see excel sheets named appril , april2, april3 with each one record.

    Any suggestion when working with date time columns?

    Thanks in Advance!

Share your thoughts...

Please fill out the comment form below to post a reply.