Conditional where statements for a multi-selection parameter
-
I have successfully created several report parameters and have been able to pass them through to the where clause. However, if a certain parameter is selected it directly relies on the value of another field to populate with the correct record. I am looking to conditionally change the where clause based on the users selection of the parameter value (labeled @test-2 below). The parameter, @test-2 field is a multi-selection field, with 4 possible selections. If 2 of the values are selected another field must be = to 'recvg%' and 2 others must be = to 'poi%'. My where clause works ONLY if 1 value is selected. If multiple values are selected I get an error.
PLEASE HELP
when the parameter (@test) =
* '1' then POP30300.TRXSORCE like 'recvg%'
* '3' then POP30300.TRXSORCE like 'recvg%'
* '2' then POP30300.TRXSORCE like 'poi%'
* '4' then POP30300.TRXSORCE like 'poi%'
The ssrs statement follows:
SELECT POP30310.PONUMBER, POP30310.ITEMNMBR, POP30310.ITEMDESC, POP30300.GLPOSTDT, POP30300.VENDORID, POP30300.VENDNAME, POP30300.TRXSORCE, POP10500.QTYSHPPD, GL00100.ACTNUMBR_2, GL00100.ACTNUMBR_1, GL00100.ACTNUMBR_3, GL00100.ACTNUMBR_4, POP30300.VOIDSTTS, GL00100.ACTDESCR, POP30310.EXTDCOST, POP10500.QTYINVCD, POP30300.POPTYPE, POP10100.POTYPE
FROM {oj (((NAMSA.dbo.POP30300 POP30300 INNER JOIN NAMSA.dbo.POP30310 POP30310 ON ((POP30300.POPRCTNM=POP30310.POPRCTNM) AND (POP30300.TRXSORCE=POP30310.TRXSORCE)) AND (POP30300.CURRNIDX=POP30310.CURRNIDX)) INNER JOIN NAMSA.dbo.POP10500 POP10500 ON (POP30310.POPRCTNM=POP10500.POPRCTNM) AND (POP30310.RCPTLNNM=POP10500.RCPTLNNM)) LEFT OUTER JOIN NAMSA.dbo.GL00100 GL00100 ON POP30310.INVINDX=GL00100.ACTINDX) LEFT OUTER JOIN NAMSA.dbo.POP10100 POP10100 ON POP10500.PONUMBER=POP10100.PONUMBER}
WHERE POP30300.VOIDSTTS=0 AND (POP30300.GLPOSTDT>=(@start)) AND (POP30300.GLPOSTDT<(@end)) and (GL00100.ACTNUMBR_1= (@location)) and (GL00100.ACTNUMBR_2= @department)) and (POP30300.POPTYPE in (@test))
ORDER BY GL00100.ACTNUMBR_3 -
any help on this would be greatly appreciated
-
Hey Man your Dynamics Great plain query is difficult to understand. But i think you are struggling with multivalue parameter. hope below link will help you.
http://msdn.microsoft.com/en-us/library/dd207127.aspx
If this is not helpful please revert back me with clear requirement
-----------------------------------------------------------------------------------------------------------------------------------------------------------
Please feel free to let me know if you are not clear or I’ve misunderstood anything.Thanks,
Arunkumar S P
-
The only records I want to be pulled if (@test) = 1 would also have to have a POP30300.TRXSORCE value like 'recvg%'
If (@test) = '2' then POP30300.TRXSORCE must also be like 'poi%'
Because I must use the 'in' clause, I am not sure how to modify the where statement. I can't simply add the following into the where clause, right? and (table.field ='1' and pop30300.trxsource like 'rev%')
-
Let me show you how to do this when @test-2 takes a single value.
WHEREPOP30300.VOIDSTTS = 0
ANDPOP30300.GLPOSTDT >= @start
ANDPOP30300.GLPOSTDT < @end
andGL00100.ACTNUMBR_1 = @location
andGL00100.ACTNUMBR_2 = @department
and1 =
case when (@test = '1' or @test-2 = '3') and POP30300.TRXSORCE like 'recvg%'
then 1
when (@test = '2' or @test-2 ='4') and POP30300.TRXSORCE like 'poi%'
then 1
else 0
end
ORDER BYGL00100.ACTNUMBR_3
Once you test the above code. for multi valued parameters option do this.
Create a Split function to split the comma separated param values to return a table containig one column with values in the table. for eg., 'a,b,c,d' will return a table1 with four rows with each row containg one column, with a,b,c,d
and in the place of @test-2 =1
@test-2 in (select value form table1 ) or something like that.
"Software changes. This is a rather obvious statement, but it is a fact that must be ever present in the minds of developers and architects. Although we tend to think of software development as chiefly an engineering exercise, the analogy breaks down very quickly. When was the last time someone asked the designers of the Empire State building to add ten new floors at the bottom, put a pool on the top, and have all of this done before Monday morning? " : Doug Purdy, Microsoft Corporation
-
thanks for your help. I will test this today
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply