Reporting Services export to Excel with Multiple Worksheets

Question

I m currently building a MS Reporting Services Report that gets rendered to excel  I m trying to figure out how to set up the report so that it creates multiple Worksheets  Tabs  of the data on the report  I understand that entering a page break in the report gives you a new worksheet  however  is there a way to name each tab instead of it default to  worksheet 1  worksheet 2  etc   Anyone know how this is done   Thanks in advance

User · Answer

I found a simple way around this in 2005  Here are my steps    Create a string parameter with values    Y    and    N    called    PageBreaks     Add a group level above the group  value  which was used to split the data to the multiple sheets in Excel   Inserted into the first textbox field for this group  the expression for the    PageBreaks    as such      IIF Parameters PageBreaks Value  Y  Fields DISP PROJECT Value      Note  If the parameter     Y    then you will get the multiple sheets for each different value   Otherwise the field is NULL for every group record  which causes only one page break at the end   Change the visibility hidden value of the new grouping row to    True     NOTE  When you do this it will also determine whether or not you have a page break in the view  but my users love it since they have the control

User · Answer

To late for the original asker of the question  but with SQL Server 2008 R2 this is now possible   Set the property  Pagebreak  on the tablix or table or other element to force a new tab  and then set the property  Pagename  on both the element before the pagebreak and the element after the pagebreak  These names will appear on the tabs when the report is exported to Excel   Read about it here  http   technet microsoft com en-us library dd255278 aspx

User · Answer

To late for the original asker of the question  but with SQL Server 2008 R2 this is now possible   Set the property  Pagebreak  on the tablix or table or other element to force a new tab  and then set the property  Pagename  on both the element before the pagebreak and the element after the pagebreak  These names will appear on the tabs when the report is exported to Excel   Read about it here  http   technet microsoft com en-us library dd255278 aspx

User · Answer

As  huttelihut pointed out  this is now possible as of SQL Server 2008 R2 - Read More Here  Prior to 2008 R2 it does t appear possible but MSDN Social has some suggested workarounds

User · Answer

Group your report data based on the category that you want your sheets to be based on   Specify that you want that grouping to start a new page for every new category   Each page becomes a new worksheet in the Excel workbook   Note   I use SQL Server 2003 and Excel 2003

User · Answer

Here are screenshots for SQL Server 2008 R2  using SSRS Report Designer in Visual Studio 2010    I have done screenshots as some of the dialogs are not easy to find   1  Add the group    2  Specify the field you want to group on    3  Now click on the group in the  Row Groups  selector  directly below the report designer     4  F4 to select property pane  expand  Group  and set Group   PageBreak   BreakLocation    Between   then enter the expression you want for Group   PageName    5  Here is an example expression    Here is the result of the report exported to Excel  with tabs named according to the PageName expression

User · Answer

On the group press F4 and look for the page name  on the properties and name your page this should solve your problem

User · Answer

As  huttelihut pointed out  this is now possible as of SQL Server 2008 R2 - Read More Here  Prior to 2008 R2 it does t appear possible but MSDN Social has some suggested workarounds

User · Answer

As  huttelihut pointed out  this is now possible as of SQL Server 2008 R2 - Read More Here  Prior to 2008 R2 it does t appear possible but MSDN Social has some suggested workarounds

User · Answer

I found a simple way around this in 2005  Here are my steps    Create a string parameter with values    Y    and    N    called    PageBreaks     Add a group level above the group  value  which was used to split the data to the multiple sheets in Excel   Inserted into the first textbox field for this group  the expression for the    PageBreaks    as such      IIF Parameters PageBreaks Value  Y  Fields DISP PROJECT Value      Note  If the parameter     Y    then you will get the multiple sheets for each different value   Otherwise the field is NULL for every group record  which causes only one page break at the end   Change the visibility hidden value of the new grouping row to    True     NOTE  When you do this it will also determine whether or not you have a page break in the view  but my users love it since they have the control

User · Answer

Put the tab name on the page header or group TableRow1 in your report so that it will appear in the  A1  position on each Excel sheet   Then run this macro in your Excel workbook   Sub SelectSheet           For i   1 To ThisWorkbook Sheets Count         mysheet    Sheet   amp  i         On Error GoTo 10         Sheets mysheet  Select         Set Target   Range  A1           If Target      Then Exit Sub         On Error GoTo Badname         ActiveSheet Name   Left Target  31          GoTo 10 Badname          MsgBox  Please revise the entry in A1    amp  Chr 13             amp   It appears to contain one or more    amp  Chr 13             amp   illegal characters    amp  Chr 13          Range  A1   Activate 10         Next i End Sub

User · Answer

Here are screenshots for SQL Server 2008 R2  using SSRS Report Designer in Visual Studio 2010    I have done screenshots as some of the dialogs are not easy to find   1  Add the group    2  Specify the field you want to group on    3  Now click on the group in the  Row Groups  selector  directly below the report designer     4  F4 to select property pane  expand  Group  and set Group   PageBreak   BreakLocation    Between   then enter the expression you want for Group   PageName    5  Here is an example expression    Here is the result of the report exported to Excel  with tabs named according to the PageName expression

User · Answer

The solution from Edward worked for me   If you want the whole tablix on one sheet with a constant name  specify the PageName in the tablix s Properties   If you set the PageName in the tablix s Properties  you can not use data from the tablix s dataset in your expression   If you want rows from the tablix grouped into sheets  or you want one sheet with a name based on the data   specify the PageName in the Group Header

User · Answer

As  huttelihut pointed out  this is now possible as of SQL Server 2008 R2 - Read More Here  Prior to 2008 R2 it does t appear possible but MSDN Social has some suggested workarounds

User · Answer

Group your report data based on the category that you want your sheets to be based on   Specify that you want that grouping to start a new page for every new category   Each page becomes a new worksheet in the Excel workbook   Note   I use SQL Server 2003 and Excel 2003

User · Answer

The solution from Edward worked for me   If you want the whole tablix on one sheet with a constant name  specify the PageName in the tablix s Properties   If you set the PageName in the tablix s Properties  you can not use data from the tablix s dataset in your expression   If you want rows from the tablix grouped into sheets  or you want one sheet with a name based on the data   specify the PageName in the Group Header

User · Answer

Put the tab name on the page header or group TableRow1 in your report so that it will appear in the  A1  position on each Excel sheet   Then run this macro in your Excel workbook   Sub SelectSheet           For i   1 To ThisWorkbook Sheets Count         mysheet    Sheet   amp  i         On Error GoTo 10         Sheets mysheet  Select         Set Target   Range  A1           If Target      Then Exit Sub         On Error GoTo Badname         ActiveSheet Name   Left Target  31          GoTo 10 Badname          MsgBox  Please revise the entry in A1    amp  Chr 13             amp   It appears to contain one or more    amp  Chr 13             amp   illegal characters    amp  Chr 13          Range  A1   Activate 10         Next i End Sub

User · Answer

On the group press F4 and look for the page name  on the properties and name your page this should solve your problem

[reporting-services] Reporting Services export to Excel with Multiple Worksheets

Examples related to reporting-services