TBSupport
asked on
SSRS Parameter Allowing Only One Selection
Hello:
If we run an SSRS report, we can select a value in the parameter and the report previews successfully. But, if choose more than one selection of choices for the parameter, the preview displays no data.
What do we need to modify to allow for the report to show data for multiple values? We have this set up for the parameter, but we are not getting the expected behavior.
Thanks!
TBSupport
If we run an SSRS report, we can select a value in the parameter and the report previews successfully. But, if choose more than one selection of choices for the parameter, the preview displays no data.
What do we need to modify to allow for the report to show data for multiple values? We have this set up for the parameter, but we are not getting the expected behavior.
Thanks!
TBSupport
If you created the report in BIDS, open the project and then open the report.
You can then see the dataset.
Look as the properties, look at the filters.
You may see the filter already set to filter the data based upon your parameter.
To get multiple results you need to have the sign change from "=" to "in".
When you use the in clause you also need to have the parameter show without the (0) at the end. (this is the default option when the parameter is chosen).
This is a reasonably hard thing to explain without screen captures, sorry.
I hope this helps.
You can then see the dataset.
Look as the properties, look at the filters.
You may see the filter already set to filter the data based upon your parameter.
To get multiple results you need to have the sign change from "=" to "in".
When you use the in clause you also need to have the parameter show without the (0) at the end. (this is the default option when the parameter is chosen).
This is a reasonably hard thing to explain without screen captures, sorry.
I hope this helps.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hello:
I have tried these suggestion but no success. I did not realize that multi-value parameters are "tricky" when a stored procedure is the data source.
Any other options?
Thanks!
TBSupport
I have tried these suggestion but no success. I did not realize that multi-value parameters are "tricky" when a stored procedure is the data source.
Any other options?
Thanks!
TBSupport
Can you tell us a bit more details about your problems. What does the data look like, what data types do the tables have. What is the stored procedure like and how is the parameter set up on the report.
If you are using Inline query in your Dataset then simply write query with Where clause having In parameter like
Select * from table where id in (SELECT * FROM [dbo].[fn_MVParam](@parame
But if you are using Stored procedure you have to use Split and Join.
Split in the Dataset where you have to use function to split the value and in SSRS parameter use Join function with "," Separator ( =join(Parameters!<your param name>.Value,",") ).
The Split function for your SP is here
Open in new window