none
Custom Tab Names in SSRS Excel Export

    Question

  • Hello All:

    Inserting "friendly" tab names into an Excel export seems to be a long standing issue with SSRS.  Are there any current workarounds that may be used?  I am using SSRS 2008, and would like to apply custom tab names (not the report name).

    Thanks!

    Tim
    Monday, June 29, 2009 3:21 PM

Answers

  • I got close to implementing a workaround for this, but other priorities got in the way, and I was unable to fully test or implement the solution.

    It involves developing the report with a row to export a row at the very top left of the report, white background, white font, so the users won't see it during normal viewing.
    It is mostly viable for subscription based reporting, when exporting the report to a particular directory.
    Having an SSIS package poll that directory every x number of minutes to see if a file has arrived.
    If it has, run a stored procedure, and send in the filename.

    Then, from inside the stored procedure, open up an instance of OLE Automation to allow SQL Server to consume the excel file.

    Then look at the top rows of cells, where you would have put in the pre-defined tab names, rename the sheet, and save the file to another pre-defined location, email the file to a distribution list (also located in the top row), or whatever you like.

    It pretty much defeats the purpose of the ability to export the report, but it is technically a workaround, if your users are extremely picky.
    Enabling OLEAutomation is a slight security risk, but if it is that big of a deal, then it is probably worth it.

    Feel free to use my code, all I ask is that you tell me how well it ends up working!

    drop procedure proc_SSRS_Excel_RenameTabs
    go --exec proc_SSRS_Excel_RenameTabs 'C:\Projects\SSIS\SSRSmeta.xls', '', 0, 0, 0, 0, 0 create procedure SSRS_excel_RenameTabs ( @excel_full_file_name varchar(max) ,@convert_to_table_name varchar(50) ,@transfer_to_table bit=1 ,@clear_existing_records_first bit=1 ,@good int =null output ,@error_code int =null ,@error_description varchar(255) = null output ) as set nocount on/* ----------------------------------------- --testing variables declare @excel_full_file_name varchar(max) declare @convert_to_table_name varchar(50) declare @transfer_to_table bit=1 declare @clear_existing_records_first bit=1 declare @good int =null output declare @error_code int =null declare @error_description varchar(255) = null output -----------------------------------------*/ declare @command varchar(8000) -- copy excel file under temp and change worksheet name set @good=0 set @error_description = '' set @error_code=0 declare @object int ,@hr int ,@src varchar(255) exec @hr = master.dbo.sp_OACreate 'Excel.Application', @object out if @hr <> 0 begin exec master.dbo.sp_OAGetErrorInfo @object, @src out, @error_description out set @error_description = '1. ' + isnull(@error_description,'N/A') select hr=convert(varbinary(4),@hr), source=@src, description=@error_description set @error_code=1 set @good=0 goto error end exec @hr = master.dbo.sp_OASetProperty @object, 'DisplayAlerts', 'false' if @hr <> 0 begin exec master.dbo.sp_OAGetErrorInfo @object, @src out, @error_description out set @error_description = '2. ' + isnull(@error_description,'N/A') select hr=convert(varbinary(4),@hr), source=@src, description=@error_description set @error_code=2 set @good=0 goto error end declare @workbook int declare @workbook_path_save_as varchar(max) exec @hr = master.dbo.sp_oaMethod @Object,'WorkBooks.Open',@workbook out, @excel_full_file_name if @hr <> 0 begin exec master.dbo.sp_OAGetErrorInfo @object, @src out, @error_description out set @error_description = '3. ' + isnull(@error_description,'N/A') select hr=convert(varbinary(4),@hr), source=@src, description=@error_description set @error_code=3 set @good=0 goto error end DECLARE @sheetcount int --count of sheets in workbook DECLARE @cell_1_1 nvarchar(31) --variable to contain value of cell(1,1) DECLARE @ObjectStringCell_1_1 nvarchar(600) --variable to contain object string pointing to cell(1,1) DECLARE @ObjectStringSheetName nvarchar(31) exec @hr = master.dbo.sp_OAGetProperty @object, 'Workbooks(1).Worksheets.count', @sheetcount output while @sheetcount > 0 BEGIN PRINT 'inside while' --set object string pointing to value of cell(1,1) SET @ObjectStringCell_1_1 = 'Workbooks(1).Worksheets(' + cast(@sheetcount AS nvarchar(2)) + ').cells(1,1).Value' --put value of cell(1,1) into @cell_1_1 exec @hr = master.dbo.sp_OAGetProperty @object, @ObjectStringCell_1_1, @cell_1_1 output if @hr <> 0 begin exec master.dbo.sp_OAGetErrorInfo @object, @src out, @error_description out set @error_description = '6a. ' + isnull(@error_description,'put value of cell(1,1) into @cell_1_1') select hr=convert(varbinary(4),@hr), source=@src, description=@error_description set @error_code=6 set @good=0 goto error end ELSE if @Cell_1_1 is not null BEGIN --set current worksheet name to contents of @cell_1_1 set @ObjectStringSheetName = 'Workbooks(1).Worksheets(' + cast(@sheetcount AS nvarchar(2)) + ').Name' print @ObjectStringSheetName exec @hr = master.dbo.sp_OASetProperty @object, @ObjectStringSheetName, @cell_1_1 if @hr <> 0 begin exec master.dbo.sp_OAGetErrorInfo @object, @src out, @error_description out set @error_description = '6b. ' + isnull(@error_description,'set worksheet ' + cast(isnull(@sheetcount,'null') AS nvarchar(2)) + ' name to ' + isnull(@cell_1_1, 'null')) select hr=convert(varbinary(4),@hr), source=@src, description=@error_description set @error_code=4 set @good=0 goto error END END SET @sheetcount = @sheetcount - 1 --decrement sheet even if contents of cell(1,1) is null, happens sometimes due to extensive page breaks. Ignore sheet entirely. END exec @hr = master.dbo.sp_oaMethod @workbook ,'Save',null--,@workbook_path_save_as if @hr <> 0 begin exec master.dbo.sp_OAGetErrorInfo @object, @src out, @error_description out set @error_description = '5. ' + isnull(@error_description,'N/A') select hr=convert(varbinary(4),@hr), source=@src, description=@error_description set @error_code=5 set @good=0 goto error end exec @hr = master.dbo.sp_oaMethod @Object,'Application.Quit' --,@workbook out,@workbook_path if @hr <> 0 begin exec master.dbo.sp_OAGetErrorInfo @object, @src out, @error_description out set @error_description = '7. ' + isnull(@error_description,'N/A') select hr=convert(varbinary(4),@hr), source=@src, description=@error_description set @error_code=7 set @good=0 goto error end if @transfer_to_table = 1 begin if @clear_existing_records_first=1 begin set @command = 'delete from ' + @convert_to_table_name exec (@command) end -- copy records from excel into table: set @command = 'insert into ' + @convert_to_table_name + ' select * from ' + ' OpenRowSet(''MSDASQL'' '+ ', ''Driver={Microsoft Excel Driver (*.xls)};'+ 'DBQ=' + @workbook_path_save_as + ''''+ ',''SELECT * FROM [excel_data$]'')' print @command exec (@command) set @command = 'select * from ' + @convert_to_table_name exec (@command) end error: select @error_description as 'Error', @error_code as 'Error Code' exec @hr = master.dbo.sp_OADestroy @workbook if @hr <> 0 begin set @error_description = '7. ' + isnull(@error_description,'N/A') select hr=convert(varbinary(4),@hr), source=@src, description=@error_description set @error_code=7 set @good=0 end exec @hr = master.dbo.sp_OADestroy @object if @hr <> 0 begin set @error_description = '8. ' + isnull(@error_description,'N/A') select hr=convert(varbinary(4),@hr), source=@src, description=@error_description set @error_code=8 set @good=0 end go
    Monday, June 29, 2009 7:01 PM
  • Hello Tim,

    there is currently no built-in option to name the sheets of the Excel export.  However, since we have seen this request somewhat frequently, we are hoping to include a new feature to name pages / control sheet names in Excel export in the next major release of Reporting Services.  You can submit your vote here: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=235985

    Thanks,
    Robert


    Robert Bruckner   http://blogs.msdn.com/robertbruckner
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Monday, June 29, 2009 11:00 PM
    Owner

All replies

  • I got close to implementing a workaround for this, but other priorities got in the way, and I was unable to fully test or implement the solution.

    It involves developing the report with a row to export a row at the very top left of the report, white background, white font, so the users won't see it during normal viewing.
    It is mostly viable for subscription based reporting, when exporting the report to a particular directory.
    Having an SSIS package poll that directory every x number of minutes to see if a file has arrived.
    If it has, run a stored procedure, and send in the filename.

    Then, from inside the stored procedure, open up an instance of OLE Automation to allow SQL Server to consume the excel file.

    Then look at the top rows of cells, where you would have put in the pre-defined tab names, rename the sheet, and save the file to another pre-defined location, email the file to a distribution list (also located in the top row), or whatever you like.

    It pretty much defeats the purpose of the ability to export the report, but it is technically a workaround, if your users are extremely picky.
    Enabling OLEAutomation is a slight security risk, but if it is that big of a deal, then it is probably worth it.

    Feel free to use my code, all I ask is that you tell me how well it ends up working!

    drop procedure proc_SSRS_Excel_RenameTabs
    go --exec proc_SSRS_Excel_RenameTabs 'C:\Projects\SSIS\SSRSmeta.xls', '', 0, 0, 0, 0, 0 create procedure SSRS_excel_RenameTabs ( @excel_full_file_name varchar(max) ,@convert_to_table_name varchar(50) ,@transfer_to_table bit=1 ,@clear_existing_records_first bit=1 ,@good int =null output ,@error_code int =null ,@error_description varchar(255) = null output ) as set nocount on/* ----------------------------------------- --testing variables declare @excel_full_file_name varchar(max) declare @convert_to_table_name varchar(50) declare @transfer_to_table bit=1 declare @clear_existing_records_first bit=1 declare @good int =null output declare @error_code int =null declare @error_description varchar(255) = null output -----------------------------------------*/ declare @command varchar(8000) -- copy excel file under temp and change worksheet name set @good=0 set @error_description = '' set @error_code=0 declare @object int ,@hr int ,@src varchar(255) exec @hr = master.dbo.sp_OACreate 'Excel.Application', @object out if @hr <> 0 begin exec master.dbo.sp_OAGetErrorInfo @object, @src out, @error_description out set @error_description = '1. ' + isnull(@error_description,'N/A') select hr=convert(varbinary(4),@hr), source=@src, description=@error_description set @error_code=1 set @good=0 goto error end exec @hr = master.dbo.sp_OASetProperty @object, 'DisplayAlerts', 'false' if @hr <> 0 begin exec master.dbo.sp_OAGetErrorInfo @object, @src out, @error_description out set @error_description = '2. ' + isnull(@error_description,'N/A') select hr=convert(varbinary(4),@hr), source=@src, description=@error_description set @error_code=2 set @good=0 goto error end declare @workbook int declare @workbook_path_save_as varchar(max) exec @hr = master.dbo.sp_oaMethod @Object,'WorkBooks.Open',@workbook out, @excel_full_file_name if @hr <> 0 begin exec master.dbo.sp_OAGetErrorInfo @object, @src out, @error_description out set @error_description = '3. ' + isnull(@error_description,'N/A') select hr=convert(varbinary(4),@hr), source=@src, description=@error_description set @error_code=3 set @good=0 goto error end DECLARE @sheetcount int --count of sheets in workbook DECLARE @cell_1_1 nvarchar(31) --variable to contain value of cell(1,1) DECLARE @ObjectStringCell_1_1 nvarchar(600) --variable to contain object string pointing to cell(1,1) DECLARE @ObjectStringSheetName nvarchar(31) exec @hr = master.dbo.sp_OAGetProperty @object, 'Workbooks(1).Worksheets.count', @sheetcount output while @sheetcount > 0 BEGIN PRINT 'inside while' --set object string pointing to value of cell(1,1) SET @ObjectStringCell_1_1 = 'Workbooks(1).Worksheets(' + cast(@sheetcount AS nvarchar(2)) + ').cells(1,1).Value' --put value of cell(1,1) into @cell_1_1 exec @hr = master.dbo.sp_OAGetProperty @object, @ObjectStringCell_1_1, @cell_1_1 output if @hr <> 0 begin exec master.dbo.sp_OAGetErrorInfo @object, @src out, @error_description out set @error_description = '6a. ' + isnull(@error_description,'put value of cell(1,1) into @cell_1_1') select hr=convert(varbinary(4),@hr), source=@src, description=@error_description set @error_code=6 set @good=0 goto error end ELSE if @Cell_1_1 is not null BEGIN --set current worksheet name to contents of @cell_1_1 set @ObjectStringSheetName = 'Workbooks(1).Worksheets(' + cast(@sheetcount AS nvarchar(2)) + ').Name' print @ObjectStringSheetName exec @hr = master.dbo.sp_OASetProperty @object, @ObjectStringSheetName, @cell_1_1 if @hr <> 0 begin exec master.dbo.sp_OAGetErrorInfo @object, @src out, @error_description out set @error_description = '6b. ' + isnull(@error_description,'set worksheet ' + cast(isnull(@sheetcount,'null') AS nvarchar(2)) + ' name to ' + isnull(@cell_1_1, 'null')) select hr=convert(varbinary(4),@hr), source=@src, description=@error_description set @error_code=4 set @good=0 goto error END END SET @sheetcount = @sheetcount - 1 --decrement sheet even if contents of cell(1,1) is null, happens sometimes due to extensive page breaks. Ignore sheet entirely. END exec @hr = master.dbo.sp_oaMethod @workbook ,'Save',null--,@workbook_path_save_as if @hr <> 0 begin exec master.dbo.sp_OAGetErrorInfo @object, @src out, @error_description out set @error_description = '5. ' + isnull(@error_description,'N/A') select hr=convert(varbinary(4),@hr), source=@src, description=@error_description set @error_code=5 set @good=0 goto error end exec @hr = master.dbo.sp_oaMethod @Object,'Application.Quit' --,@workbook out,@workbook_path if @hr <> 0 begin exec master.dbo.sp_OAGetErrorInfo @object, @src out, @error_description out set @error_description = '7. ' + isnull(@error_description,'N/A') select hr=convert(varbinary(4),@hr), source=@src, description=@error_description set @error_code=7 set @good=0 goto error end if @transfer_to_table = 1 begin if @clear_existing_records_first=1 begin set @command = 'delete from ' + @convert_to_table_name exec (@command) end -- copy records from excel into table: set @command = 'insert into ' + @convert_to_table_name + ' select * from ' + ' OpenRowSet(''MSDASQL'' '+ ', ''Driver={Microsoft Excel Driver (*.xls)};'+ 'DBQ=' + @workbook_path_save_as + ''''+ ',''SELECT * FROM [excel_data$]'')' print @command exec (@command) set @command = 'select * from ' + @convert_to_table_name exec (@command) end error: select @error_description as 'Error', @error_code as 'Error Code' exec @hr = master.dbo.sp_OADestroy @workbook if @hr <> 0 begin set @error_description = '7. ' + isnull(@error_description,'N/A') select hr=convert(varbinary(4),@hr), source=@src, description=@error_description set @error_code=7 set @good=0 end exec @hr = master.dbo.sp_OADestroy @object if @hr <> 0 begin set @error_description = '8. ' + isnull(@error_description,'N/A') select hr=convert(varbinary(4),@hr), source=@src, description=@error_description set @error_code=8 set @good=0 end go
    Monday, June 29, 2009 7:01 PM
  • Hello Tim,

    there is currently no built-in option to name the sheets of the Excel export.  However, since we have seen this request somewhat frequently, we are hoping to include a new feature to name pages / control sheet names in Excel export in the next major release of Reporting Services.  You can submit your vote here: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=235985

    Thanks,
    Robert


    Robert Bruckner   http://blogs.msdn.com/robertbruckner
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Monday, June 29, 2009 11:00 PM
    Owner
  • There are many answers you will get for this issue in msdn.. but as specified in docs there is no solutions to this issue straight forward.... there are many alternatives you can use to sovle this problems.

    you can find how to apply XSLT transformatino in SSRS to get the sheets renamed.
    or you can use the web service api to run the report from external applications.... and then by used excel api to open the xls file and rename it etc....

    Monday, June 29, 2009 11:21 PM
  • Robert:

    Thank you for responding.  I have voted that this small but meaningful change be included in the next major release.

    Tim
    Tuesday, June 30, 2009 12:43 PM
  • Nektoinphx:

    Thank you for the code.  As time permits, I will try it out and advise.

    Tim
    Tuesday, June 30, 2009 12:45 PM
  • This feature was added in Report Builder 3.0.

    http://technet.microsoft.com/en-us/library/dd255234.aspx#WorksheetTabNames
    http://technet.microsoft.com/en-us/library/dd255278.aspx

    "To provide the sheet name, you set the PageName property of a table, matrix, list, group, or rectangle."

    "Use the InitialPage property to specify a default name for the worksheet tab name when you export the report, and use page breaks and the PageName property to provide different names for each worksheet. Each new report page, defined by a page break, is exported to a different worksheet named by the value of the PageName property."

    • Proposed as answer by e_8 Tuesday, June 21, 2011 8:58 PM
    Tuesday, November 02, 2010 11:58 PM
  • This feature was added in Report Builder 3.0.

    http://technet.microsoft.com/en-us/library/dd255234.aspx#WorksheetTabNames
    http://technet.microsoft.com/en-us/library/dd255278.aspx

    "To provide the sheet name, you set the PageName property of a table, matrix, list, group, or rectangle."

    "Use the InitialPage property to specify a default name for the worksheet tab name when you export the report, and use page breaks and the PageName property to provide different names for each worksheet. Each new report page, defined by a page break, is exported to a different worksheet named by the value of the PageName property."


    True, and thus also in SQL Server 2008 R2.  For a working example, have a look at this article: http://blog.hoegaerden.be/2011/03/23/where-the-sheets-have-a-name-ssrs-excel-export/

    MCITP SQL Server 2008 (DEV); MCTS SQL Server 2008 (BI, DEV & DBA)


    Wednesday, August 08, 2012 7:16 AM