Hi Tess Network,
I found some very helpful information in the "Custom Report Setup" document describing how to establish a dependant dropdown parameter in a custom report. The syntax for the "where" field is something like column = <<p1>> in order to build a dependancy on parameter 1.
Is there a way to build a dependancy for a parameter which can have multiple selections? I thought the syntax in this desired case would be like column in (<<p1>>) but that causes an error. Any advice is appreciated.
I think you might be making the same mistake SQL developers make when they do
DECLARE @list varchar(30) = '1,2,3,4,5'; SELECT * FROM table WHERE column IN (@list);
The variable is just a string! And a column IN (1, 2, 3) syntax in T-SQL is actually translated by the query parser into column = 1 OR column = 2 OR column = 3 and so on. So the above code example just translates to column = '1,2,3,4,5'.
column IN (1, 2, 3)
column = 1 OR column = 2 OR column = 3
column = '1,2,3,4,5'
Tessitura report multiple select parameters actually create comma-delimited strings (which is why you usually need to parse them in your stored procedures with dbo.FT_SPLIT_LIST()). So when you use the <<p1>> syntax in a dependent parameter, you've got the same problem.
dbo.FT_SPLIT_LIST()
<<p1>>
I've never tried to do a parameter dependent upon a multiple-select before, but if you can't get a subquery on dbo.FT_SPLIT_LIST() to work, the old fashioned way of using CHARINDEX(',42,',','+<<p1>>+',') might be more fruitful.
CHARINDEX(',42,',','+<<p1>>+',')
Nick is right.
Here is an example of a statement that is used on one of the custom reprots I have ingherited where it limits the dropdown to only show production season in the Seasons you have selected in <<p3>>
For Parameter 4 our where clause is
inv_no in (select prod_season_no From T_prod_season where charindex(','+convert(varchar,season)+',',','+<<p3>>+',')>0)
Hope that helps
Mark