SSRS Multi-Value Parameter

0
votes

I have an SSRS report based on Geographic Locations. The datasets return project records, with a regional area, electoral area and then a city level. I have created cascading parameters from region to electoral and then to city.

However, each project can have multiple cities under an electoral region selected. The user is requesting that the parameter filter only records that have that city selected. So, if the parameter selected are:

  • Region: RDEK Region
  • Area: City of Cranbrook
  • City: (Options are Cranbrook and Rockyview)

If they select only "Cranbrook", they want only project records that have onlyCranbrook selected and not projects that have Cranbrook and Rockyview selected.

Right now my report results will show all records with Cranbrook selected and other cities that are selected on the Project. For example, I would see all projects with Cranbrook selected and all projects that have Cranbrook and Rockyview selected.

Below is some more detail of my report.

  • Grouped by Regional, Electoral Area and Project Number.
  • Parameters :
    • @Regional - Available values from Regional Dataset (selecting all available)
    • @Area - Available values from Electoral Area Dataset (selecting all available in @Regional)
    • @City - Available values from City Dataset (selecting all values in @area)
    • @Status - All available statuses
    • @Fiscal Year - All available Fiscal years.

I have a Filter on my details Dataset Query (below) that filters only those cities in my @city parameter.

My dataset query: 

SELECT Filteredcbt_geographicarea.cbt_electoralareaidname
  , Filteredcbt_geographicarea.cbt_name AS City
  , Filteredcbt_project.cbt_projectnumber
  , Filteredcbt_geographicarea.cbt_electoralidname
  , Filteredcbt_projectfiscalyear.cbt_committedamount
  , Filteredcbt_projectfiscalyear.cbt_commitmentavailable
  , Filteredcbt_projectfiscalyear.cbt_commitmentdisbursed
  , Filteredcbt_project.cbt_organizationlegalnameidname
  , Filteredcbt_project.statuscodename
  , Filteredcbt_projectfiscalyear.cbt_projectnameid
  , Filteredcbt_projectfiscalyear.cbt_programfiscalyearidname
  , Filteredcbt_projectfiscalyear.cbt_fiscalyearidname
  , Filteredcbt_regionaldistrict.cbt_name AS [Regional District]
FROM Filteredcbt_regionaldistrict
    INNER JOIN Filteredcbt_electoralarea ON Filteredcbt_regionaldistrict.cbt_regionaldistrictid = Filteredcbt_electoralarea.cbt_regionaldistrictid
    INNER JOIN Filteredcbt_category
    INNER JOIN Filteredcbt_program ON Filteredcbt_category.cbt_categoryid = Filteredcbt_program.cbt_categoryid
    INNER JOIN Filteredcbt_project ON Filteredcbt_program.cbt_programid = Filteredcbt_project.cbt_programnameid
    INNER JOIN Filteredcbt_projectfiscalyear ON Filteredcbt_project.cbt_projectid = Filteredcbt_projectfiscalyear.cbt_projectnameid
    INNER JOIN Filteredcbt_cbt_project_cbt_geographicarea ON Filteredcbt_project.cbt_projectid = Filteredcbt_cbt_project_cbt_geographicarea.cbt_projectid
    INNER JOIN Filteredcbt_geographicarea ON Filteredcbt_cbt_project_cbt_geographicarea.cbt_geographicareaid = Filteredcbt_geographicarea.cbt_geographicareaid 
        ON Filteredcbt_electoralarea.cbt_electoralareaid = Filteredcbt_geographicarea.cbt_electoralareaid
WHERE        (Filteredcbt_geographicarea.cbt_electoralareaidname IN (@Area)) AND    (Filteredcbt_project.statuscodename IN (@Status)) AND 
                     (Filteredcbt_projectfiscalyear.cbt_fiscalyearidname IN (@FiscalYear)) AND (Filteredcbt_regionaldistrict.cbt_name IN (@Regional)) AND 
                     (Filteredcbt_geographicarea.cbt_name IN (@City)) AND (NOT EXISTS
                         (SELECT        cbt_electoralareaid, cbt_electoralareaidname, cbt_electoralid, cbt_electoralidname, cbt_geographicareaid, cbt_name 
                           FROM            Filteredcbt_geographicarea AS ga1
                           WHERE        (cbt_name NOT IN (@City)) AND (cbt_geographicareaid = Filteredcbt_cbt_project_cbt_geographicarea.cbt_geographicareaid)))

ORDER BY Filteredcbt_projectfiscalyear.cbt_fiscalyearidname
ORDER BY Filteredcbt_projectfiscalyear.cbt_fiscalyearidname;

Filteredcbt_cbt_project_cbt_geographicarea sample:

cbt_cbt_project_cbt_geographicareaid	cbt_geographicareaid	                cbt_projectid
A6D7292F-EA05-E411-BCEA-00155D0470B9	4EB4F611-4BD1-E311-8AB9-00155D067193	2C2FC521-EA05-E411                                                                              

998F9FE5-2806-E411-BCEA-00155D0470B9	38B4F611-4BD1-E311-8AB9-00155D067193	54FD66DD-2806-E411-

9A8F9FE5-2806-E411-BCEA-00155D0470B9	7EB4F611-4BD1-E311-8AB9-00155D067193	54FD66DD-2806-E411-

956F0BD1-2A06-E411-BCEA-00155D0470B9	5BB4F611-4BD1-E311-8AB9-00155D067193	82B478C7-2A06-E411-

54D3ED7F-2C06-E411-BCEA-00155D0470B9	54B4F611-4BD1-E311-8AB9-00155D067193	ADC83877-2C06-E411-

55D3ED7F-2C06-E411-BCEA-00155D0470B9	C8B4F611-4BD1-E311-8AB9-00155D067193	ADC83877-2C06-E411-
EBDC3544-2E06-E411-BCEA-00155D0470B9	5BB4F611-4BD1-E311-8AB9-00155D067193	40C1163C-2E06-E411-
70A9B479-AE06-E411-BCEA-00155D0470B9	54B4F611-4BD1-E311-8AB9-00155D067193	F24BE26E-AE06-E411-

616C21B3-B506-E411-BCEA-00155D0470B9	65B4F611-4BD1-E311-8AB9-00155D067193	C4389EAA-B506-E411-BCEA-
626C21B3-B506-E411-BCEA-00155D0470B9	99B4F611-4BD1-E311-8AB9-00155D067193	C4389EAA-B506-E411-BCEA-
636C21B3-B506-E411-BCEA-00155D0470B9	ABB4F611-4BD1-E311-8AB9-00155D067193	C4389EAA-B506-E411-BCEA-
646C21B3-B506-E411-BCEA-00155D0470B9	F7B4F611-4BD1-E311-8AB9-00155D067193	C4389EAA-B506-E411-BCEA-

Filteredcbt_geographicarea sample:

cbt_electoralareaid	                    cbt_electoralareaidname	cbt_geographicareaid         cbt_name

16B4F611-4BD1-E311-8AB9-00155D067193	Town of Creston	        2EB4F611-4BD1-E311-8AB9-00155D067193     Alice Siding

13B4F611-4BD1-E311-8AB9-00155D067193	RDCK Area I	            2FB4F611-4BD1-E311-8AB9-00155D067193   Thrums

0EB4F611-4BD1-E311-8AB9-00155D067193	RDCK Area D	            30B4F611-4BD1-E311-8AB9-00155D067193   Ainsworth Hot Springs

0EB4F611-4BD1-E311-8AB9-00155D067193	RDCK Area D	            31B4F611-4BD1-E311-8AB9-00155D067193   Argenta

Thank you, let me know if you need any other information.

2
sqlreporting-servicesparameters
This is more of a SQL problem. You will need to post the query you are using to get your dataset, and probably your table structure as well, in order for anyone to be able to help you. – Tab Alleman
Thank you Tab, I will post more information in my question. – WendyA
Thank you Jeroen for editing that post for me! – WendyA
I cannot figure this out... Is anyone able to assist? – WendyA

2 Answers

0
votes

Try adding this to your WHERE clause:

AND NOT EXISTS(
  SELECT * FROM Filteredcbt_geographicarea ga1
  WHERE ga1.cbt_name NOT IN (@City)
  AND ga1.cbt_geographicareaid=Filteredcbt_cbt_project_cbt_geographicarea.cbt_geographicareaid 
)

If it works, I'll add an explanation to my answer. : )

0
votes

If you add another table to your Joins with

INNER JOIN (SELECT Filteredcbt_geographicarea.cbt_geographicareaid, string_agg(Filteredcbt_geographicarea.cbt_name, ', ') AS City_List
        FROM Filteredcbt_geographicarea
        GROUP BY 1) City_List_Lookup ON City_List_Lookup.cbt_geographicareaid = Filteredcbt_cbt_project_cbt_geographicarea.cbt_geographicareaid

Using this table, you can compare the concatenated Cities to the parameter entered.

So your where clause becomes:

City_List_Lookup.City_List = (@City)

By concatenating the various cities together, you can look for situations where the entire list consists of only one city.