SSRS 2008 Data sets

I am new to SSRS; What I would like to know is whether it is possible to have a dataset based on a dynamic stored procedure i.e the stored procedure requires an input parameter开发者_运维百科 from the report and then using that can call any of a set of 'sub-stored procedures' that all return datasets with the same columns.

Basically, no.

SSRS at design time looks at the output of the stored procedure. If it is variable then it may not parse it. The same happens if you use temp tables. Also, the output would have to be the same for each proc

Now, switching stored proc is the same using that parameter to show or hide data regions, each based on a different dataset/proc. This is how I'd do it.

Or different sub-reports (which is a data region) or a whole new reports

If the "sub stored procedures" are already written, then your approach could work by having the stored proc called by the report create a temp table, then populate it based on the passed parameter, then return data from the temp table.

If the "sub stored procedures" are not already written, another option is just to use a CASE statement and have multiple SELECTs in the stored proc called by the report.