How to filter (WHERE) only some report parameters in SQL Server Reporting Services (SSRS)-CodePudding

I need to filter a view in SQL Server Reporting Services (SSRS) with user supplied report parameters, however it should only apply those filters enabled by the user, that is where the report parameter IS NOT NULL.

I'm missing a EVERYTHING_INCLUDING_NULL statement or any other way to completely disable this filter clause.

Furthermore, only the < operator works, the = operator returns "Invalid length parameter passed to the SUBSTRING function".

Any suggestions?

Thanks!

SELECT
    *
    FROM
        [dbo].[my_view]

    WHERE
            [quantity] = ( CASE WHEN @rp_no_quantity_only = 1 THEN (NULL OR 0) ELSE EVERYTHING_INCLUDING_NULL END)
        AND
            [prefix] = ( CASE WHEN @rp_prefix IS NOT NULL THEN @rp_prefix ELSE EVERYTHING_INCLUDING_NULL END)
        AND
            LEFT( [article], CHARINDEX( '-', [article]) - 1) = ( CASE WHEN @rp_article IS NOT NULL THEN @rp_article ELSE EVERYTHING_INCLUDING_NULL END)
        AND
            [delivery_date] < ( CASE WHEN @rp_delivery_date IS NOT NULL THEN THEN @rp_delivery_date ELSE EVERYTHING_INCLUDING_NULL END)
        AND
            [invoice_date] < ( CASE WHEN @rp_invoice_date IS NOT NULL THEN @rp_invoice_date ELSE EVERYTHING_INCLUDING_NULL END)

ORDER BY
    ...

CodePudding user response：

I usually use an OR in each condition to check for the NULL. I checked David's link but it was a little unclear since it was answering a different question. I am a little fuzzy on what you're trying to do with the first condition, but it seems right - it will return only records with a NULL or 0 quantity if @rp_no_quantity_only = 1 otherwise it returns all.

SELECT *
    FROM
        [dbo].[my_view]
    WHERE
            (ISNULL([quantity], 0) = IIF(@rp_no_quantity_only = 1, 0, ISNULL([quantity], 0)))
        AND
            ([prefix] = @rp_prefix OR @rp_prefix IS NULL)
        AND
            (LEFT( [article], CHARINDEX( '-', [article]) - 1) = @rp_article OR @rp_article IS NULL)
        AND
            ([delivery_date] < @rp_delivery_date OR @rp_delivery_date IS NULL)
        AND
            ([invoice_date] < @rp_invoice_date OR @rp_invoice_date IS NULL)

ORDER BY