Thursday, March 22, 2012

Does Reporting Services allow you to select a dataset dynamically?

I have a remote report ...
I need to call one of two stored procedures depending on which parameters were passed to the report. (Both stored procedures return the same fields.)
Does SQL Reporting Services allow you to switch the dataset or stored procedure name dynamically? If so, where do I put the logic? Right now I have it working with one stored procedure.
I'm thinking that I should just make one stored procedure that takes all parameters and calls one of the other two stored procedures. Do I have any options besides this?

Thanks in advance!
Your query can be expression-based, e.g.; =Iif(Parameters!SomeParameterValue = 0, "EXEC sp1 1<parameter list>", EXEC sp2 1<parameter list>"). Alternatively, if the sp choice can be based on a configuration convention, you can use the Report Server web.config file to store the config value. The later case is demonstrated by the DynamicConnection report in this download. The report uses a dynamic connection string but the same approach can be applied to the query.|||That worked. Thanks!
sql

No comments:

Post a Comment