where field value in ('A','B','C') as a prameter

[R7Dave]

Hello

Is it possible to have report paramaters set up like this...

Report Parameter = @TERRITORYTYPE

If the user selects "Coastal", SQL query would be

where territory in (1,2,3)

If the user selects "Beach", SQL query would be

where territory in (4,5,6)

so on the data tab, it would be something like

Select * from table where landcode in @TERRITORYTYPE

...and base the results off which landcode was picked?

Is it possible to use the IN syntax?

Thanks in advance
Dave

 

[bburnell]

My guess is the easiest solution is to put it into a stored procedure and base the report off of that data. At least that's what I would do.
~Brett

--------------------------
Web/.net Programmer & DBA
Central PA

 

[RiverGuy]

Code your query like this:

DECLARE @Territories TABLE (TerritoryID INT, TerritoryType VARCHAR(50))

INSERT INTO @Territories SELECT 1, 'Coastal'
INSERT INTO @Territories SELECT 2, 'Coastal'
INSERT INTO @Territories SELECT 3, 'Coastal'
INSERT INTO @Territories SELECT 4, 'Beach'
INSERT INTO @Territories SELECT 5, 'Beach'
INSERT INTO @Territories SELECT 6, 'Beach'

Select a.* from table a
INNER JOIN @Territories b
  ON a.landcode = b.TerritoryID
WHERE b.TerritoryType = @TERRITORYTYPE

If this works for you, then I would recommend creating a permanent table in your database to store your Territory Groups instead of generating it on the fly each time.

 

[R7Dave]

Thanks Riverguy - I will try this. I did not see your post until now - thanks also to bburnell - I will combine both of these solutions.

Thanks
Dave