Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSRS 2005 conditional / on-the-fly where clause Expand / Collapse
Author
Message
Posted Monday, April 20, 2009 11:10 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, July 13, 2010 12:32 PM
Points: 16, Visits: 94
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
Post #700814
Posted Monday, April 20, 2009 12:03 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 2:24 PM
Points: 11,990, Visits: 11,007
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #700875
Posted Monday, April 20, 2009 12:47 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, July 13, 2010 12:32 PM
Points: 16, Visits: 94

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%'))
Post #700899
Posted Monday, April 20, 2009 1:00 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 2:24 PM
Points: 11,990, Visits: 11,007
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.

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #700918
Posted Monday, April 20, 2009 1:23 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, July 13, 2010 12:32 PM
Points: 16, Visits: 94
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
Post #700938
Posted Monday, April 20, 2009 1:25 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 2:24 PM
Points: 11,990, Visits: 11,007
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #700940
Posted Monday, April 20, 2009 1:30 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, July 13, 2010 12:32 PM
Points: 16, Visits: 94
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
Post #700946
Posted Monday, April 20, 2009 1:38 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 2:24 PM
Points: 11,990, Visits: 11,007
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #700951
Posted Tuesday, April 21, 2009 2:41 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, July 13, 2010 12:32 PM
Points: 16, Visits: 94
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)
Post #701887
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse