Reporting Services使用多个工作表导出到Excel

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.

我目前正在构建一个MS Reporting Services报告,该报告可以渲染为excel。我正在试图弄清楚如何设置报告,以便它在报告上创建多个数据表(选项卡)。我知道在报告中输入分页符会为您提供一个新的工作表,但是,有没有办法命名每个选项卡而不是默认为(工作表1,工作表2)等。

Anyone know how this is done?

谁知道这是怎么做到的?

Thanks in advance!

提前致谢!

8 个解决方案

#1

As @huttelihut pointed out, this is now possible as of SQL Server 2008 R2 - Read More Here

正如@huttelihut指出的那样,现在可以从SQL Server 2008 R2开始 - 在此处阅读更多内容

Prior to 2008 R2 it does't appear possible but MSDN Social has some suggested workarounds.

在2008 R2之前,它似乎不可能,但MSDN Social有一些建议的解决方法。

#2

To late for the original asker of the question, but with SQL Server 2008 R2 this is now possible:

对于问题的原始提问者来说,但是对于SQL Server 2008 R2,现在可以:

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.

在Tablix或表或其他元素上设置属性“Pagebreak”以强制使用新选项卡,然后在分页前的元素和分页后的元素上设置属性“Pagename”。将报表导出到Excel时,这些名称将显示在选项卡上。

Read about it here: http://technet.microsoft.com/en-us/library/dd255278.aspx

在这里阅读:http://technet.microsoft.com/en-us/library/dd255278.aspx

#3

Here are screenshots for SQL Server 2008 R2, using SSRS Report Designer in Visual Studio 2010.

以下是使用Visual Studio 2010中的SSRS报表设计器的SQL Server 2008 R2的屏幕截图。

I have done screenshots as some of the dialogs are not easy to find.

我已经完成了截图,因为有些对话框不容易找到。

1: Add the group

1:添加组

2: Specify the field you want to group on

2:指定要分组的字段

3: Now click on the group in the 'Row Groups' selector, directly below the report designer

3:现在单击报表设计器正下方“行组”选择器中的组

4: F4 to select property pane; expand 'Group' and set Group > PageBreak > BreakLocation = 'Between', then enter the expression you want for Group > PageName

4:F4选择属性窗格;展开“组”并设置Group> PageBreak> BreakLocation ='Between',然后为Group> PageName输入所需的表达式

5: Here is an example expression

5:这是一个示例表达式

Here is the result of the report exported to Excel, with tabs named according to the PageName expression

以下是导出到Excel的报表的结果,其中的选项卡根据PageName表达式命名

#4

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.

如果希望一个工作表上的整个Tablix具有常量名称,请在Tablix的属性中指定PageName。如果在Tablix的属性中设置PageName,则不能使用表达式中tablix数据集中的数据。

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.

如果希望Tablix中的行分组为工作表(或者您希望一个工作表具有基于数据的名称),请在“组头”中指定PageName。

#5

I found a simple way around this in 2005. Here are my steps:

我在2005年找到了一个简单的方法。这是我的步骤:

1. Create a string parameter with values ‘Y’ and ‘N’ called ‘PageBreaks’.

创建一个字符串参数,其值为'Y'和'N',称为'PageBreaks'。

2. Add a group level above the group (value) which was used to split the data to the multiple sheets in Excel.

在组(值)上方添加组级别,该组级别用于将数据拆分为Excel中的多个工作表。

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

插入到该组的第一个文本框字段中,'PageBreaks'的表达式为...... = IIF(参数!PageBreaks.Value =“Y”,Fields!DISP_PROJECT.Value,“”)注意:如果参数='Y '那么你将获得每个不同值的多张表。否则,每个组记录的字段为NULL(最后只导致一个分页符)。

4. Change the visibility hidden value of the new grouping row to ‘True’.

将新分组行的可见性隐藏值更改为“True”。

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

注意:执行此操作时,它还将确定您是否在视图中有分页符,但我的用户喜欢它,因为他们有控件。

#6

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.

将选项卡名称放在页眉或报表中的TableRow1组中,以使其显示在每个Excel工作表的“A1”位置。然后在Excel工作簿中运行此宏。

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

#7

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.

根据您希望工作表所基于的类别对报表数据进行分组。指定您希望该分组为每个新类别启动新页面。每个页面都成为Excel工作簿中的新工作表。

Note: I use SQL Server 2003 and Excel 2003.

注意:我使用SQL Server 2003和Excel 2003。

#8

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

在组中按F4并查找页面名称,在属性和命名页面上,这应该可以解决您的问题