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
Best Answer
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.