Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
stonebreaker
Yak Posting Veteran
92 Posts |
 Posted - 2008-09-19 : 15:47:59
|
| I need to select a subset of available accounts to pass to a query to generate the report data set:select distinct i.*from interval_x iwhere dtime between @Start_Date and @End_Dateand i.account in ( @account)order by i.account, i.dtimeThe @Start_Date and @End_Date parameters work fine, but the @account parameter only works if I choose one and only one account value from the dropdown list. When I try to select two or more accounts, I get an error. I assume I need to format the list of accounts somehow, but I don't know where to start. Any help would be greatly appreciated. |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2008-09-19 : 16:23:04
|
| One common method is to use a table-valued function that parses a delimited string. Then you simply INNER JOIN to this function as you would a table. Your @account parameter is the argument to the function. Search this site for CSV and/or PARSE routines.Be One with the OptimizerTG |
 |
|
|
dexter.knudson
Constraint Violating Yak Guru
260 Posts |
Posted - 2008-09-21 : 23:16:11
|
| The concept you are trying should work if you have that code in a dataset in SSRS & have your paramater set to 'multi-value'. Please read http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=110163 for anyone who thinks differently before replying. Pls paste your error in & hopefully we can provide some advice based on it. |
|
|
|
stonebreaker
Yak Posting Veteran
92 Posts |
Posted - 2008-09-22 : 09:13:10
|
| OK, I got it working. Haven't figured out what I did wrong on the first try, but I followed the tutorial PRECISELY this time and substituted my own data for the AdventureWorks data. By running the query first from the data tab, as recommended in the tutorial, instead of manually creating the parameters, it automatically created the parameters. The revised query runs as such:select distinct i.*from interval_x iwhere (dtime between (@Start_Date) and (@End_Date)and i.account in ( @account))order by i.account, i.dtimeWAG says that by running the query from the data tab first as the tutorial recommends, it somehow changed the generated code, although again, that's just a WAG. Anyway, it works. I do appreciate the link to the parsing function. That's a useful function. |
|
|
|
dexter.knudson
Constraint Violating Yak Guru
260 Posts |
Posted - 2008-09-23 : 03:01:01
|
| If the case (eg UPPER, lower, Title) of your params in the "Report Parameters" area doesn't exactly match that in your query, this can cause problems. The error is something like "The value expression for query parameter '@xxx' refers to a non-existing report parameter 'xxx' |
|
|
|
|
|
|
|
|
Multi-value parameter issue