April 20, 2009 at 11:10 am
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.
The normal sql select statement follows, but I am not sure how to do this in ssrs.
TABLE = A
Select * from A
Where type='A' and INV like 'abc%'
or
Select * from A
Where type='B' and INV like 'xyz%'
Please help!
My parameter for type is called @type
April 20, 2009 at 12:03 pm
Assuming that @type is the value you want it is just type = @type
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 20, 2009 at 12:47 pm
One thing that I forgot to mention is that the @type field is a multi-selection field, with 4 possible selections. 2 of the available values to select rely on 'abc%' and 2 rely on 'xyz%'.
My where clause works ONLY if 1 value is selected. If multiple values are selected I get an error. My where type value is listed below.
((@type = A and table.A like 'abc%') or (@type = C and table.A like 'abc%') or (@type = B and table.A like 'xyz%') or (@type = D and table.A like 'xyz%'))
April 20, 2009 at 1:00 pm
That is roughly clear as mud however the where clause you posted sounds like it should work...
((@type = A and table.A like 'abc%') or (@type = C and table.A like 'abc%') or (@type = B and table.A like 'xyz%') or (@type = D and table.A like 'xyz%'))
If that is accurate you could simplify it like this.
((@type = 'A' or @type = 'C') and table.A like 'abc%')
or
((@type = 'B' or @type = 'D') and table.A like 'xyz%')
looks like it should do what you want. :hehe:
or even (i think this is easier to read)
case @type
when 'A' then table.A like 'abc%'
when 'B' then table.A like 'xyz%'
when 'C' then table.A like 'abc%'
when 'D' then table.A like 'xyz%'
end
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 20, 2009 at 1:23 pm
I still receive the following error when selecting multiple values in the @type multi-select parameter. Invalid syntax near ','.
I only receive it when select more than one value in the @type drop down
April 20, 2009 at 1:25 pm
Are you saying that you a combobox that allows multi select? What is the value of @type at run time?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 20, 2009 at 1:30 pm
not sure. the value could be just 1 or 1,2 or 1,3 or 2,4. could be any combination. 1,2,3,4
April 20, 2009 at 1:38 pm
Gotcha. 😉
I believe you will need something like this.
select * from table
where type in (@type) and
case type
when 'A' then table.A like 'abc%'
when 'B' then table.A like 'xyz%'
when 'C' then table.A like 'abc%'
when 'D' then table.A like 'xyz%'
end
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 21, 2009 at 2:41 pm
I have attached the full query. I am now getting an error "must declare the scalar variable for @start" I have never worked with scalar variables.
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)) and (case test
when '1' then POP30300.TRXSORCE like 'recvg%'
when '3' then POP30300.TRXSORCE like 'recvg%'
when '2' then POP30300.TRXSORCE like 'poi%'
when '4' then POP30300.TRXSORCE like 'poi%'
end)
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply