How do I store this for SSRS to work

[jymm]

If I have a query that says

select *
from SalesTable 
where Territory in ('05', '20')

I get what I would expect (all sales in that territory). So now I want to store the '05', '20' in a table so that I can change the territory

so hopefully the code would look like

select *
from SalesTable 
where Territory in (@TheseTerritories)

only problem I am running into is - what do I put in @TheseTerritories in the database?
I have tried storing these into the DB without success
1) 05 20
2) '05', '20'
3) ('05', '20')
and many other options - so I am missing something silly - Storing a single value (ex just 05) works just peachy... so what am I missing? How can I store the list that the 'in' clause would work?

 

[jymm]

storing this in seperate records works - so although I am still a bit curious - this is not important anymore - just more of a pain to maintain.

 

[RiverGuy]

You pretty much have it correctly coded. You need to make sure your parameter is set up as a multi-valued parameter.

Take a look at this article.

http://msdn.microsoft.com/en-us/library/aa337292.aspx.

You can get details here on requirements needed to create a query like you need which will be re-written at the time of execution.

Especially these two statements

You can define a multivalue parameter for any report parameter that you create. However, if you want to pass multiple parameter values back to a data source by using the query, the following requirements must be satisfied:

* The data source must be SQL Server, Oracle, Analysis Services, SAP BI NetWeaver, or Hyperion Essbase.
* The data source cannot be a stored procedure. Reporting Services does not support passing a multivalue parameter array to a stored procedure.
* The query must use an IN clause to specify the parameter.

The report server rewrites queries for data sources that cannot process parameters as an array. Rewriting the query is necessary to produce the intended result. A query rewrite is triggered when a parameter is defined as multivalued and the query uses an IN statement to specify the parameter. If you build a query that does not include the IN statement, realize that you are circumventing the logic the report server provides to support multivalued parameters.

 

[jymm]

got many many reports with multi values - this is/was going to be a hidden parameter

I did not need to know HOW to CODE it - needed to know what the DATA should look like.

so I guess the question still is how would I store many values in one database field that would work with the IN clause. What I tried above did not work (ok so I changed the code to select MANY records from the table instead of just one, but that will be a maintenance nightmare for this application).

 

[RiverGuy]

I don't understand. Why would you store a WHERE/IN clause in a database? If Territories 5 & 20 together make up a region for instance, you would have a Regions table and add RegionID to your Territory table

Regions
-------
RegionID PK
RegionName

Territory
-------
TerritoryID PK
TerritoryName
RegionID FK


Data:
TerritoryID     TerritoryName     RegionID
-----------     -------------     --------
05              Territory 05      1
20              Territory 20      1
50              Territory 50      2
60              Territory 60      2

Then

SELECT s.*
FROM SalesTable s
INNER JOIN Territory t
ON s.TerritoryID = t.TerritoryID
WHERE t.RegionID = 1

 

[jymm]

essentially it is a question of access to the data. Where as I get what countries the user needs to access through AD fields I needed to limit other users in another way.

Some users need access to customers & sales in territory 05 only. Some need only 20 - still others need access to 01, 02, 03, 04.... and others need access to only 05 & 20. and in my companies crazy case - there is no logic such that I could just use a region.

and by access - well, it could be online or I REALLY wanted to ALSO use this field and some security tables that I have out there to know how they want their reports emailed to them (HTM, PDF...). This way they would get only the approved territories.

so I am really looking for how to store it - not how to redesign it at this time, but thanks for the suggestions.

 

[RiverGuy]

Well just store it record by record. If you have to end up creating a new table, then you would still get the same effect.

TerritoryAccess
---------------
UserID INT/VARCHAR/whatever
TerritoryID VARCHAR

Data:

UserID        TerritoryID
-------       ------------
1             05
1             20
2             05
3             20

Query:

SELECT s.*
FROM SalesTable s
INNER JOIN TerritoryAcces t
ON SUSER_SNAME() = t.UserID
AND s.TerritoryID = t.TerritoryID

I just don't really think there is a good way to store a delimited list of values in a column and use those as a WHERE clause without using dynamic SQL or hacking some code together in your SSRS Query Expression. I would definitely advise against using dynamic SQL and I wouldn't be too crazy about a Query Expression that cobbled together either.

 

[jymm]

yup - that is what I ended up doing - that last table though is going to be a nightmare to maintain. Just thought it would be easier to have a list in one record in one table of the db.