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!
This question is related to
reporting-services
The answer is
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
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.
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" & 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." & Chr(13) _
& "It appears to contain one or more " & Chr(13) _
& "illegal characters." & Chr(13)
Range("A1").Activate
10
Next i
End Sub
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.
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
On the group press F4 and look for the page name, on the properties and name your page this should solve your problem
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" & 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." & Chr(13) _
& "It appears to contain one or more " & Chr(13) _
& "illegal characters." & Chr(13)
Range("A1").Activate
10
Next i
End Sub
On the group press F4 and look for the page name, on the properties and name your page this should solve your problem
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.
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.
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
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.
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
Source: Stackoverflow.com