Friends,
In this post we are gonna discuss about how to export data from a report to multiple sheets WITH NAMES to sheets of an EXCEL. This is a real cool option we have in SSRS. To demonstrate the same I designed a report and the preview of the same is given below.
The above one is a MATRIX format report and if I export the same into EXCEL then all the records will be exported into the same SHEET of excel as shown below.
In the above pic, you can see that all the data is loaded into the same sheet. Now let us see how can we load data into different sheets of an excel. To do so firstly we need to identify how the data or when the data needs to be broke and pushed to next sheet. In this example I will push EACH COLOR information into one sheet. PFB the steps.
- Right click on the GROUP color and select Group Properties as shown below.
- Select the page “Page Breaks” and check the option “Between Each instance of the group” as shown below/
- Now set the Page Name property by selecting the group and clicking F4. Set the value to the Field COLOR as shown below.
- Now preview the report and you can see the report is broken into pieces based on color.
- Export the report and see the data in EXCEL. you can see each color data is pushed to different sheets as shown below.
This is it sirji .. Hope you understood the magic ..
Regards,
Roopesh Babu V
Dude, I can’t explain to you how much this just helped me! I’ve been struggling for almost 4 hours because my sheet names replicate the first record found.
Saved my life!
Hi Melanie,
I have the same problem as you.
you found a solution
Tank’s for your help 🙂
I have created a cube in SSSAS. And Now I want to create reporrt using that cube.
In report I have to create cascading parameters (Example: I have two parameter country and city.
So If I select country then their respective Citys should come in City parameter )
So any one of you could you please let me know that How Can I create cacscading parameter?
Should I create some hierarchy in SSAS?
Or I have to do something at report level ??
please help me out
Please the solution to my problem ??
Yes i have checked this and i did not find the PageName option in properties pane.Please help me how to see this pagename property in property tab
Try with initial pagename property which u can find in properties pane when you click on light yellow area(outside body) of the report or sby simple selecting the REPORT in dropdown present in top of the properties window .