Dolores M
Dolores M

Reputation: 1

SSRS Search Multi-Select Parameter using Text Paramater where the resulting items in Multi-select list are filterable

I've create two parameters p_search and p_destination. Have two datasets, main data set and destination dataset.

The Main Dataset query to pull all data for report has this where clause: where destination IN(@p_destination)

The p_search parameter is just a text box that allows blank values.

The p_destination parameter is a multi-value list box that uses the following dataset where clause. where destinationname Like '%' + @p_search + '%'.

When I run the query, fill in the p_search text box and hit tab, the results in the multi-select drop down are correct.

Then I select a few of the values in the The results list and hit run report.

The report run results instead of showing the specific ones selected in the multi-select dropdown, show all of the search results.

Solution suggestions. I used this solution by Alan Schofield to get this far: How to add a search based multivalued parameter in SSRS?

Main Dataset Query:

 -------------------4B--------------------
     SELECT tp.[Constituent ID]
     ,tp.[Preferred Name]
     ,tp.[Last Name]
     ,tp.[First Name]
     ,tp.[Constituent Preferred Class Year]
     ,tc.[Contribution ID]
    ,tc.ContNumSort
    ,tc.[Contribution Date]
  ,tc.elcn_contributiontype
  ,tc.ConTypeName
  ,tc.elcn_solicitationmethodid
  ,tc.[Solicitation Method]
  ,tc.[Destination ID]
  ,tc.Destination
  ,tc.[Campaign ID]
  ,tc.Campaign
  FROM #temp_contributions tc LEFT JOIN  #temp_porgs tp  ON 
  Tc.PERORGID=tp.PerOrgID
  WHERE tc.statuscode=1
  AND tc.destination IN(@p_destination)
order by tp.[Full Name], tc.ContNumSort

The @p_destination parameter dataset

Select elcn_destinationid, Elcn_destination
FROM elcn_destination
WHERE elcn_destination LIKE '%' + @p_search + '%'

Upvotes: 0

Views: 39

Answers (0)

Related Questions