Parameters using 'ALL' union but still select multiples 1

[shenay921]

Hi There,

I am working in SSRS 2008. I have a parameter to pull 'All' of a set of records or it will pull one. What it won't do is allow someone to select two or more. It is either one or All. I found the standard 'Select All' was killing my report -- like 5 minutes. This 'All' workaround runs in less than 10 seconds. BUT I cannot select two or more. Any ideas on how I can set this parameter up to select two or more? Thanks!

--Main Dataset 'WHERE' clause:
WHERE (ai.entered_datetime BETWEEN @StartDate AND @FinishDate)
AND (ai.applicant_status IN(@Status) or @Status ='All')

--Parameter query:
SELECT DISTINCT status_id
, status_label
FROM applicant_status
UNION
SELECT 'All', '_All'
ORDER BY status_label

 

[RiverGuy]

When you create parameters in SSRS, your parameters are either a single value or an array of values if multiselect. You're attempting to use this one parameter in both ways.

Before your new 'All' logic, your performance issues were probably caused by a lack of proper indexing on your table. The way you have it set up now, you might not always look up on the applicant_status column, so sometimes your report will be fast. So what I would do would be to get rid of the new All logic, put the parameter back to multiselect, and index your table properly.

 

[shenay921]

Thanks for the response! I will study on that idea that the parameters are either a single value or an array of values.

I had already tried the indexing and even adjusted the query to pull fewer records but the performance was still poor when selecting all.