SSRS - Report Based on Parameter Selection

0
votes

We have a web based report we are moving to SSRS. In the SSRS report, I need one report to be able to call three different stored procedures based on the selection of a parameter value, and the output / report display would also be different.

I believe I can create three different datasets for each procedure but I don't want all three to actually run when the user selects "View Report", as that would be excessive execution.

How could I get only one procedure to be called and only the correct report section to show based on the parameter?

Let me try and give an example if the above doesn't make sense:

Parameter: Report Type Values: Day / Month / Year

If the user selects "Day" for the report type parameter, I need to call "SP_ReportDay". If the user selects "Month", I need to call "SP_ReportMonth" and if the user selects "Year" I need to call "SP_ReportYear".

Each report type would have different data displayed. It isn't the same columns for ecah report and the data displayed is different.

How would I show / hide sections based on the selection criteria along with calling the correct procedure?

1
sql-serverreporting-services
How do you plan to handle the different columns? This sounds like it would be better off as three reports. Maybe using subreports you can have it appear as one report to the user. – Joe C
I agree three reports would be better, but unfortunately my direction is one report. I can look into the subreport option – A M C

1 Answers

1
votes

If the result sets are different, you don't have many choices besides running all three sprocs. BUT you can add a parameter to each sproc that tells them, respectively, not to actually return any results. Your report can set this parameter at run-time. You'll have to modify the body of your sprocs accordingly.