I was working with a customer recently creating a report whose data is primarily exported to MS Excel. They needed the report to display monthly data in one worksheet, year-to-date data in another worksheet and detail data in a third worksheet.
I decided to organize my report into three independent sub-reports, one for each worksheet and place them on a main report in rectangles so I can force a page break between each. This creates a new worksheet for each sub-report just like the customer wanted. The PageName property in SSRS 2008 R2 is great because it allows the various worksheets in Excel to use whatever name is defined in the PageName property and that makes for a very happy customer.
So, I’ve created each report and run them with success, the customer liked the format and approved the sub-reports for accuracy. When I place them in the main report and run it in BIDS I my result is:
“Error: Subreport could not be shown.”
After some digging it turns out this is a known issue with SSRS 2008 R2 when you have a sub-report that uses a shared dataset. The problem only exists in BIDS and if you deploy to your SSRS server it will run fine. But design is a bummer since you cannot preview your format and layout changes in the IDE.
If you too want this fixed, please go to Connect and vote this as important.