• Technologies

  • Instructors

  • How to Name Worksheets When Exporting SSRS reports to Excel

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


    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:


    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.


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


    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.


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

    You may also like:  Microsoft Second Shot Exams Are On!


    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.


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


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


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

    See what people are saying...

    1. Ron Bevilacqua

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

    2. Jolanda

      Thank you! Very useful!

    3. Sindhu

      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.