How to store multiple value in one available value in SSRS reporting

Question

Currently i wanted to search multiple value in one available value which configured in SSRS.

Now user required to click one by one to filter their report as per below. enter image description here

However they requested to group red highlighted into 'CCB KV' and green highlighted into 'CCB N' therefore instead user click one by one and they can directly click 'CCB KV' to filter those red highlight.

Currently we expect to use the available value option below to create 'CCB KV' and store those red highlighted and not require any script change. However it seems like available option only allow single value to store instead of multiple value and the query is using where clause as 'ce.branch_code in (@branchCode)' to obtain the result.

enter image description here

Seek you advise whether available value able to store multiple value.

Answer 1

The easiest way would be to add a table to your database containing the relationship between the parameter value and the values you actually want to search for.

For example you could create a dataset with the following in your dataset query.

CREATE TABLE myLookupTable(AreaName varchar(50), LocationName varchar(50))
INSERT INTO myLookupTable VALUES 
('Area ABC', 'Tower A'),
('Area ABC', 'Tower B'),
('Area XYZ', 'Tower X'),
('Area XYZ', 'Tower Y')

For you parameter list you could then create a dataset in your report something like

SELECT DISTINCT AreaName from myLookupTable ORDER BY AreaName

Set the available values of your parameter to point to this dataset.

Then in you main dataset, just add a join in to this new table

SELECT a.* FROM myMainTable a 
    JOIN myLookupTable b on a.LocationName = b.LocationName
    WHERE b.AreaName IN (@myParameterName)