Filters:

  • 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:

    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 Second Shot Exams Are On!

    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

    See what people are saying...

    1. Ron Bevilacqua

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

    2. Jolanda

      Thank you! Very useful!

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