Query that returns multiple result sets

Question

I have a query that will return an arbitrary number of result sets, all with the same columns - i.e. one for a Manager and then one each employee said Manager is responsible for.

Running the SQL in Mgmt Studio works fine - my 'master' and 'details' tables are successfully rendered. However if I simply enter the same SQL in a dataset query in SSRS Report Wizard, only the first result set gets reported on.

How can I get my one Master + all Details result sets rendered as separate tables in the one report? The key thing is that the query will generate an arbitrary number of result sets.

Answer 1

Sadly, from the 2008 documentation:

...A result set from a relational database, which can result from running database commands, stored procedures, or user-defined functions. If multiple result sets are retrieved through a single query, only the first result set is processed, and all other result sets are ignored.

So I'm afraid you're limited to more convoluted options. I'd suggest either:

• Returning all your result sets UNION ALLed into one large result set, with an extra column to indicate which manager/employee each section of the results is for, or
• Creating a "master" report and accompanying code that displays a subreport for each of the managers/employees you're reporting on. The subreport would use a version of your stored procedure which renders your report results for one person at a time.

The latter will probably be the cleaner approach.

Answer 2

You might want to branch the results based on a parameter. And allow users to toggle results based on that parameter.