SSRS 2008 R2 - all parameters multi value AND optional

Question

I've got a report that has 6 parameters. All parameters need to be optional and 3 have to be multi-value. One of the optional parameters is a dropdown, the rest are manually keyed in text boxes. The Where clause below works when there are multiple @VendorNum values and one @FullJA value, but fails with multiple @FullJA values regardless of the @VendorNum count.

Parameters: @VendorNum - keyed manually by user (space delimited) - optional, can be multivalue @FullJA - keyed manually by user (space delimited) - optional, can be multivalue @BU - optional, can be multivalue - when @JA is populated, this will auto-populate, if @JA isn't populated it's a dropdown with all selected. @JA3 - keyed by user - optional, single value @StartDate and @EndDate - optional single values

    select * from some_table
    WHERE
    /*FULL JA*/
    (
        SUBSTRING(VendorNum, PATINDEX('%[^0]%', VendorNum    + '.'), LEN(VendorNum)
        ) IN (@VendorNum)
        AND LEFT(JA, 7) IN (@FullJA)
        AND BU IN(@BU)
        AND @JA3 IS NULL
        )
OR
    /*DATE RANGE*/
    (
        SUBSTRING(VendorNum, PATINDEX('%[^0]%', VendorNum + '.'), LEN(VendorNum)
        ) IN (@VendorNum)
        AND LEN(ISNULL(CONVERT(VARCHAR(20), Cleared_When), '0')) >= @ClearedOnly
        AND ad.Audit_Publish_Date >= ISNULL(@StartDate, '2015-01-01')
        AND ad.Audit_Publish_Date <= ISNULL(@EndDate, '2025-12-31')
        AND BU IN (@BU)
        AND @FullJA IS NULL
        AND @JA3 IS NULL
        )

    /*BUS UNIT AND JA3*/
    OR (
        SUBSTRING(VendorNum, PATINDEX('%[^0]%', VendorNum + '.'), LEN(VendorNum)
        ) IN (@VendorNum)
        AND BU IN (@BU)
        AND ad.Audit_Publish_Date >= ISNULL(@StartDate, '2015-01-01')
        AND ad.Audit_Publish_Date <= ISNULL(@EndDate, '2025-12-31')
        AND LEFT(JA, 3) = (@JA3)
        AND @FullJA IS NULL
        )
    /*BUS UNIT ONLY*/
    OR (
        SUBSTRING(VendorNum, PATINDEX('%[^0]%', VendorNum + '.'), LEN(VendorNum)
        ) IN (@VendorNum)
        AND BU IN (@BU)
        AND ad.Audit_Publish_Date >= ISNULL(@StartDate, '2015-01-01')
        AND ad.Audit_Publish_Date <= ISNULL(@EndDate, '2025-12-31')
        AND @JA3 IS NULL
        AND @FullJA IS NULL
        )

The dataset parameter values for @FullJA and @VendorNum are both =IIF(InStr(Parameters!FullJA.Value," ")>0,SPLIT(Parameters!FullJA.Value," "),Parameters!FullJA.Value) and all params are set as NOT multivalue, with nulls allowed.

Any help would be greatly appreciated. I've written over 200 reports for this project and this is the only one that is really grinding my gears!

Thanks!

Answer 1

I would approach this by building up some temp tables / table variables, to hold the potentially multi-valued variables, and then joining to those tables. This has the advantage of you being able to insert all possible values, in the case they have omitted the variable. So, you'd split your strings and put them into those tables (something along the lines of this example) if given the variable, and otherwise just do an insert into to populate your temp table / table variable.

For a split function, I prefer something like this:

create FUNCTION [dbo].[Split] (@sep VARCHAR(32), @s VARCHAR(MAX))
RETURNS TABLE
AS
    RETURN
    (
        SELECT r.value('.','VARCHAR(MAX)') as Item
        FROM (SELECT CONVERT(XML, N'<root><r>' + REPLACE(REPLACE(REPLACE(@s,'& ','&amp; '),'<','&lt;'), @sep, '</r><r>') + '</r></root>') as valxml) x
        CROSS APPLY x.valxml.nodes('//root/r') AS RECORDS(r)
    )
GO
GRANT SELECT
    ON OBJECT::[dbo].[Split] TO PUBLIC
    AS [dbo];

I would then put those variables into a table using something like this (my separator is a ", "):

select ltrim(rtrim(ppl.Item)) as PersonName
into #gppl
from dbo.Split(', ', @PersonListForCompare) as ppl

You would do something more like:

select ltrim(rtrim(vnd.Item)) as VendorNum
into #vendorNums
from dbo.Split(', ', @VendorNum) as vnd

You would then join to that temp table just like any other table & use it to limit your results that way. In your case, you want to put in all vendors (possibly) if they didn't give you any input. So, you'd do something like:

create table #vendorNums (VendorName varchar(64))   --I have no idea, here, what this data looks like

if @VendorNum is not null and datalength(@VendorNum) > 0
    insert into into #vendorNums (VendorNum)
    select ltrim(rtrim(vnd.Item))
    from dbo.Split(', ', @VendorNum) as vnd
else
    insert into into #vendorNums (VendorNum)
    select VendorNum
    from dbo.Vendors

That said, I think that you could use your select from dbo.Split directly as a table in a join, rather than putting it into the temp table. Only problem would be you'd have to be sure you had data in there to split, or else you're going to have a bunch of combinations to get the right match-up of null parameters vs. filled ones.