October 24, 2007 at 3:14 pm
I have a reporting services report that groups by the field [AgentCode] and inserts a page break after each AgentCode change. Now when I export this multi-paged report to Excel it places each [AgentCode] report into it's own worksheet tab inside of the Excel workbook and titles each tab with sheet1, sheet2, sheet3, etc...
So my question based on this scenario - Is there a way to name each worksheet tab inside of the Excel workbook with the [AgentCode] that it corresponds to by the grouping in reporting services?
So for instance, after exporting to an Excel workbook the user would see upon opening a workbook filled with AgentCodes for the worksheet tab titles.
If this has already been answered in a previous forum question I apologize. I've searched through the forums to no avail, along with the web which starting to make me think that this cannot be accomplished.
So I pose this question to the gathering of experts at SQLServerCentral.
Thank you for all your help in this matter.
Sincerely,
R. Black
October 26, 2007 at 9:36 pm
wow, i didnt even know it is possible to export to multiple spreadsheets... my export to excel returns me an error when the no of cases are huge... i seriously doubt it is a time out issue. any idea how to increase the timeout time for exporting reports?
October 30, 2007 at 2:56 am
R. Black,
Managing Excel tab names is a common question, but until recently, the answer has always been that SSRS out-of-the-box does not provide this level of control for multi-page reports.
However recently a post went up that describes in detail how to do this with by exporting the report as XML (Excel 2003 variety) and using XSLT to control the tab names. It may sound complicated, but the post gives great step-by-step instructions.
http://www.spacefold.com/lisa/post/Changing-the-Sheet-names-in-SQL-Server-RS-Excel-QnD-XSLT.aspx
Take a look at it and see if this will work for you.
Hope this helps,
Scott Thornburg
October 26, 2011 at 9:10 am
For those that read this post in the future, I just found an article that is helpful in resolving this issue, for SSRS 2008 R2 and above
http://www.sqlservercentral.com/articles/Reporting+Services+(SSRS)/74549/
October 28, 2011 at 10:02 am
Hi,
This is a new feature of SSRS2008 r2.
To name the worksheets with the group name in excel.
All you need to do is first of all is to make sure there is a page break between each of these groups.
To do this in the Row groups window right click on the group select group properties and then select the page breaks section, in here check the tick box for 'between each instance of the group'.
Next you need to name the worksheet, select the group in the row groups window then in the properties window for the group expand the group section, in the list there will be field titled 'PageName' click on the drop down select expression and then in the expression window enter the group name by selecting the value from dataset. Thats it!
Now when you export the report into Excel the sheets will be renamed with the GroupName.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply