Reporting Services export to Excel with Multiple Worksheets

reporting-services

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!

Best 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

Related Solutions

Sql – Adding and Naming TABS to excel reports in Microsoft Reporting Services

To get around this issue from the web front end (which drove me crazy using the ReportViewer web control), I realised that SSRS returns the data as a byte array that you can then process. So capturing the stream in .NET you can write a method that does something like:

private byte[] ExportStream(string streamType, out string mimeType, out string extension)
{
    Microsoft.Reporting.WebForms.Warning[] warnings;
    string[] streamids;
    string encoding;
    string deviceInfo = "<DeviceInfo><SimplePageHeaders>True</SimplePageHeaders></DeviceInfo>"

    byte[] bytes = ReportViewerClientReport.ServerReport.Render(streamType,
                          deviceInfo, out mimeType, out encoding, out extension, out streamids, out warnings);

    return bytes;
}

Then post-process the output from this method with something like the fantastic NPOI library from http://npoi.codeplex.com/ either as bytes or create some kind of memory stream, then you can make it do whatever you want.

Also- note that SSRS (Sql Server Reporting Services) R2 will have this feature, but it comes out in about May 2010. http://www.bidn.com/blogs/bretupdegraff/bidn-blog/234/new-features-of-ssrs-2008-r2-part-1-naming-excel-sheets-when-exporting-reports

Excel – How to export a reporting services report to excel with embedded calculations in the excel columns

Formulas used in ReportViewer are automatically converted when rendering to Excel. You can also OmitFormulas using DeviceInfo if required, during the rendering process.

For more information on how the conversion takes place, check out http://msdn.microsoft.com/en-us/library/aa178946(SQL.80).aspx

Best Answer

Related Solutions

Sql – Adding and Naming TABS to excel reports in Microsoft Reporting Services

Excel – How to export a reporting services report to excel with embedded calculations in the excel columns

Related Topic