February 11, 2015 at 1:41 pm
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
February 12, 2015 at 10:42 pm
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,','))
February 12, 2015 at 11:08 pm
Here's Jeff Moden's DelimitedSplit8K function for doing that.
February 23, 2015 at 8:47 am
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