SSRS "not in" takes an extremely time if at all to run

SSRS - SQL 2008 SSRS Version 3.0. Need to run a NOT IN, but takes an extremely long time to run

Have seen the "Stacia" post about using a filter and I don't seem to have it right. http://blog.datainspirations.com/2011/01/20/working-with-reporting-services-filters-part-4-creating-a-not-in-filter/

In the Tablix of the report, I have put in the for the Filter the following:

=Iif(InStr(Join(Parameters!FilterList.Value,","),Fields!Status.Value)=0,True,False)

What I am trying to do is to show records that are not Cancelled.

Even doing a simple filter:

Expression: [Status]
Operator <>
Value Cancelled

Still seems to take an extremely long time.

We could have the following Status conditions:

Cancelled
Closed
Open
Resolved

If I take this the opposite way and look for all tickets that are cancelled (filter) in SSRS, the results come back pretty quickly.

UPDATE: if I put in the Filter for the Query for the Report:

Closed
Open
Resolved

And just simply leave Cancelled out, that should work OK. Thanks.

Solution

Is your filter on the Group, the MATRIX or the dataset? It might run quicker on the dataset since it would only check once. The problem depends on how many records you have though - the checks have to be made for each row.

You can try doing an IN statement to see if it's any faster. Create a new dataset that has the Status values but excludes the chosen one(s).

SELECT * FROM (
    SELECT 'Cancelled' AS STATUS_TYPE 
    UNION 
    SELECT 'Resolved' 
    UNION 
    SELECT 'Open' 
    UNION 
    SELECT 'Closed' ) AS X 
WHERE STATUS_TYPE NOT IN (@StatusType)

Then create another parameter based on the dataset with the available and default values as the Status_Type column. You can make it hidden when you get it working so users won't see it if it does work faster.