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.
See Question&Answers more detail:os