I came around a report, where i need to export the report to Excel in such a way that when it gets exported every Group comes on a different sheet dynamically with the corresponding Group name. This requirement seems difficult at first, however its pretty easy to achieve it.
Let's take an example to demonstrate this situation. I have a student table, same as in my earlier blog.
I have created a sample report in SSRS where the dataset is the student table, as shown below:
As you can see in the picture above, that I have taken a tablix, with Gender as the Parent Group and below that Class as a Child Group. Here, I want Gender group to be displayed as two separate sheets in Excel with first sheet name as ‘Female’ and second sheet name as ‘Male’. This should be dynamic behavior.
Now our first step is to bring the distinct rows of the Gender group to different sheets of Excel document. To achieve this, open the Group Properties for Gender Group, and go to Page Breaks section, as shown below:
Our next step will be to give Group names to the sheets in excel. To achieve this, select the properties for Gender group Tablix member. Select Gender group and Press F4, to open the properties explorer. In that expand the Group property and set appropriate column name for PageName property as shown below:
Go to Preview and save the report in Excel format. When you will open the excel report, you can see the different sheets created with Gender values, as shown below:
See, how easy its now in SQL Server Reporting Services 2008 to give dynamic names to the sheets in Excel.
Please let me know your feedback on this post.