Search code examples
sqlreporting-services

SSRS Single Multi-value Parameters from Two Columns

I have a view I am using in my SSRS report Datasets. I am looking for a solution to have a multi-value parameter to populate values from two separate columns from the view.

This is my view used in my Main Dataset:

enter image description here

I would like a single multi-value parameter drop down to pull the values from both the EvaluatingSC and the Ongoing SC columns:

This is my StaffRole Dataset for the parameter dropdown:

enter image description here

My main Dataset to pull the two columns from the view:

enter image description here

But when I setup the multi-value parameter with the StaffRole Dataset. I have to choose between the two columns:

enter image description here

enter image description here

Is there a way to combine both columns in a single multi-value parameter or is my only option to have a two multi-value parameters for each of the columns?

Solution

  • Create a new dataset to supply your parameter's available values list.

    Something like

    SELECT EvaluatingSC AS StaffRole FROM dbo.vStaffRole
UNION
SELECT OnGoingSC FROM dbo.vStaffRole

    This will give you a single column table with the staff roles in. There is no need to do a DISTINCT as UNION does this by default.