Problems Using A Multivalued Parameter List

  • Hello All,

    I’m having trouble getting my multivalued parameter to work as expected.

    My assumption is that a multivalued parameter allows you to select 1 or more items from the listed items in the dropdown and filter the query based on those items. At least this is my understanding.

    In my report I have 2 multivalued parameters that works fine if I select 1 item from each parameter group. But if I select more than one item in either of the parameter groups, I get zero records returned.

    Both parameters gets its values from a query. The queries are basic:

    Select Distinct A

    From Table

    Order By A

    And then there’s the Select All option in the dropdown list. The other assumption is that by selecting that option, it’s like using the IN clause in your query – where colA IN (@parameter) – and it builds the list from the parameter list.

    The main query looks like this:

    select distinct

    a.Appl_IdntAS [MOTS ID],

    vs.[SERVER NAME]AS [SERVER NAME],

    sv.os_verAS [OS VERSION],

    sv.status_nameAS [STATUS NAME],

    a2.Lst_Nm + ', ' + a2.fst_NmAS [VP IT],

    a3.Lst_Nm + ', ' + a3.fst_NmAS [Director],

    a.Lst_Nm + ', ' + a.fst_NmAS [Technical Director]

    from [witt info].dbo.VIEW_SERVERS vs

    LEFT OUTER JOIN [witt info].dbo.servers_v sv on vs.[server id] = sv.server_id

    INNER JOIN [WITT Info].dbo.MOTS_APPLICATION_W_SERVERS_AND_SERVER_ID_v mas ON vs.[server id] = mas.Server_id

    INNER JOIN [witt info].dbo.MOTS_APPLICATION_CONTACTS a ON mas.appl_idnt = a.Appl_Idnt

    INNER JOIN [witt info].dbo.MOTS_APPLICATION_CONTACTS a2 ON (a2.Appl_Idnt = a.Appl_Idnt AND a2.Role_Desc = 'VP IT')

    INNER JOIN [witt info].dbo.MOTS_APPLICATION_CONTACTS a3 ON (a3.Appl_Idnt = a.Appl_Idnt AND a3.Role_Desc = 'Director')

    where sv.STATUS_NAME IN ('Production', 'Development', 'Test')

    AND sv.group_name <> 'Other Wintel'

    AND a.Role_Desc = 'Technical Director'

    GROUP BY a.Appl_Idnt, vs.[SERVER NAME], sv.os_ver, sv.status_name, a2.Lst_Nm + ', ' + a2.fst_Nm, a3.Lst_Nm + ', ' + a3.fst_Nm, a.Lst_Nm + ', ' + a.fst_Nm

    HAVING (a2.Lst_Nm + ', ' + a2.fst_Nm IN (@vpit) AND sv.os_ver IN (@os))

    order by a.Appl_Idnt, vs.[server name], sv.STATUS_NAME

    Am I doing something wrong in my query to support multiple selections from the parameter list? The user wants to be able to either select 1 item in both parameter lists or all items in both parameter lists.

    I’m using SQL Server 2012 sp2.

    Thanks in advance for your input!

    Ronnie

  • Not sure if this will help...

    but from my experience i know that you will need to create a Split Function and then pass the value in the query.

    i usually just use this Split Function :

    CREATE FUNCTION [dbo].[fn_REPORT_Split]

    (

    @List nvarchar(max),

    @SplitOn nvarchar(max)

    )

    RETURNS @RtnValue table

    (

    Id int identity(1,1),

    Value nvarchar(100)

    )

    AS

    BEGIN

    While (Charindex(@SplitOn,@List)>0)

    Begin

    Insert Into @RtnValue (value)

    Select

    Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1)))

    Set @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List))

    End

    Insert Into @RtnValue (Value)

    Select Value = ltrim(rtrim(@List))

    Return

    END

    GO

    And then i call the function like this :

    Select *

    from wherever

    where something in (select Value FROM dbo.fn_REPORT_Split(@YourParameter,','))

  • Here's Jeff Moden's DelimitedSplit8K function for doing that.

    http://www.sqlservercentral.com/articles/Tally+Table/72993/

  • Thanks Guys!!

    Sorry, I've been kind of out of the loop but it would be rude for me not to express my appreciation.

    I ended up making some changes in the report setting instead of at the query level.

    I first removed the WHERE clause that filtered for the parameters. Then I added a filter to the Tablix properties and an expression (=Parameters!Field.Value where "Field" is the parameter that you are filtering on) and that appeared to have worked.

    But again, thanks for your input on this!

    Ronnie

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply