Reporting Services - get data into subreports

Question

I'm tasked with reporting on survey data using Reporting Services 2008.

My challenge is this:

• a survey has any number of questions
• a question is one of three types (a numerical eval, a yes/no question, or a free text)

To handle this, I decided to use subreports on my main report, e.g. I defined one report for each of the three question types, and now when I'm reporting on a survey, I basically dynamically create a RDL for the survey report, using the three question types as subreports.

That actually works quite nicely so far - but I'm facing one major problem: how do I get the data into the subreports?

The approach I see right now is to have each (sub)report per question type define its own data set, based on a shared data source, to extract the values from the database. I'm pretty sure this would work - but I am not very keen on having potentially 5, 10, 20 subreports going off to the database to get their data independently.

What I was hoping for was being able to go fetch the data once for the whole survey, on the "main" report, and then just feed the appropriate subset of data into each subreport, as its being rendered - but I can't seem to find any way to do this....

Am I missing something totally obvious? I haven't had much exposure to Reporting Services, and my last project with it was four years ago (with Reporting Services 2000) - so there's a good chance I am just blind for the obvious solution :-) Please let me know!

Thanks for any hints, pointers to good articles or blogs on Reporting Services, and any help at all!

Marc

Answer 1

The usual way is to pass parameters (like date range) from main report to subreports and then subreports take care of everything else. To improve performance, see if you can render subreports from cache or snapshot. The cache stores report with combination of parameters passed, so after first "database hit" some or most subreports may actually be returned from the cache.

Answer 2

If the parameters are the final data you require, then just use them and create a dummy dataset in the subreport - you could just have 'SELECT 1 AS DUMMY' as the sql (this is assuming that the subreports have distinct layouts from one another)

Or perhaps you can rethink the 'master' dataset with a function or table function?

It would still tax the sql server, but at least it would be doing it in one hit and the drain on the RS box would be less.

Answer 3

I struggled with the same issue. But there is a way you can achieve reasonable performance using "cached shared dataset". Basically subreports will use larger dataset including all rows for all subreports. By using "dataset filter" each subreport can filter out rows properly. This is only available for 2008 version however.