Pass dataset to subreport with SQL Server Reporting Services

Question

I'm using SQL Server Reporting Services and the report designer that comes with Visual Studio. I've got a really big report. It's actually so large that Visual Studio hangs (sometimes for hours at a time) or just crashes when I make changes.

There is preciously little I can do to solve the problem, so I've decided to just move the bottom half of the report into a sub-report. So, I started with one enormous, unresponsive report and ended with two small, manageable reports -- surprisingly, this actually works.

One problem: my subreport uses the same data as my main report. Right now, it populates its dataset by re-querying the database. The extra round-trip to the database causes the report to take twice as long to generate; up from 45 minutes to 1 1/2 hours to generate.

I'd like to avoid hitting the database again, and instead use the same dataset in both reports.

How can I share or pass a dataset between a report and subreport?

Answer 1

I'm pretty sure you can't. You're probably better off looking for ways to redesign the report entirely so that it's not so large... not to mention the various problems with subreports when exporting to excel.

Answer 2

I have several reports that the SQL is so complex in that it locks up Visual Studio when I try to edit it. In these reports I go straight into the Code view and edit the XML directly, which works. I also do this when Visual Studio mysteriously makes columns slightly wider than I set them at. However, I doubt you'd want to go down this path if you are editing the layout of the report too much.

Instead of running your query in the report, would it be possible to build a table using a stored procedure that both reports use? The first report runs the stored procedure to build the table and then both reports simply query the report. Watch for concurrency problems if the report can be run by multiple users.

Answer 3

Hi,

Have you tried using a list within a list where both lists use the same dataset and then filter the inner list to display only records linked to the the outer list?

As far as the execution time, 45 minutes seems like an awful long time in the first place. I'm assuming you've done some analysis of the execution plan to verify your query or stored procedure is using meaningful indexes?

Hope this helps,

Bill

Answer 4

You can do it using a dummy parameter:

i. Create a parameter in your main report 'MyData' and tick 'internal'

ii. Set default value of 'MyData' to your data-set

iii. Set the sub-report parameter with the expression

=Parameters!MyData.Value

Hope this helps, Duncan

Answer 5

If you create a table, you can merge all the cells of the details row and put a subreport as the contents. Then set the parameter of the subreport to the field you want to run the subreport against.

Jason

Answer 6

Sir, this work in some extend, how we can do like having the group on dataset and for each group there is link to another data set.

I have Productivity of Sales people for each department I wanted to display the default valus which are coming from another data set.