This is a long sought after and requested feature, and finally in 2008 R2 it’s here.
The property that you are looking to set is:
PageName
Set this and then on export to excel the tab will use the page name.
This can be set on certain objects in the report and can use Expressions to set the name.
Objects that contain this property include:
- Tablix
- Rectangle
As an example of how it works, I have a report that runs as 2 sub reports. Each subreport has a tablix that has the page name property set.
When the report exports to excel I get 2 tabs each named as defined in the page name property.
Another more complex example is using a tablix with grouping. Set the tablix to page break on the element that you are grouping by and then set that element as the value for your page name. Each time the grouping changes it will render in a new page and if exported to excel each tab will appear with the name of the group.
This post goes into it in more detail
http://blogs.msdn.com/b/robertbruckner/archive/2010/05/16/report-design-naming-excel-worksheets.aspx
Cheers
Valuable info. Lucky me I found your site by accident, and I’m stunned why this twist of fate didn’t happened in advance! I bookmarked it.
A note to add is that this all work ONLY FROM REPORT DESIGNER 3.
All options are available in visual studio but they dont work. If you open the same rdl in report designer it changes it behind the scenes somehow.
Not true. I have done this a number of times and have the excel reports to prove it. Perhaps 1. You are doing something wrong; or 2. Something has changed with Service packs that means it does’t work any more. If you think Report Designer 3 makes the difference here is a test you can do. The .rdl file is essentially a text file. Get the VS created one and the working Report Designer 3 version and then do a file comparison. You should pick up any differences.
Edit – after posting the other comment below, I suspect this was the issue. You were deploying to 2008 only in visual studio and should change your project configuration to deploy to 2008 R2.
The issue with it not working in Visual Studio is due to a Project Property. It appears that if the Project Property ‘TargetServerVersion’ is set to SQL Server 2008 is stripes the PageName Tag out when building. If you change this to SQL Server 2008 R2 it works fine. Must be an upgrade issue.
First line of the blog explains this one:
“This is a long sought after and requested feature, and finally in 2008 R2 it’s here”
It is a feature of 2008 R2 only, so if you set your target deployment to only 2008, the server would not support it so it removes the PageNam Tag from the RDL.
As you have rightly pointed out you need to deploy to 2008 R2 for this feature to work.