How do you make one report parameter inherit a value from another parameter? Suppose {key1} is a database parameter with a query like
select t1.id, t1.label
from table1 t1
and we want another parameter, call it {key2} to do something like
select t2.id, t2.label
from table2 t2
where t2.somecolumn = {key1.id}
What is the right syntax in key2’s query such that it uses the selected value of key1?
Hi PT,
pls check here:
https://reportserver.net/en/guides/admin/chapters/parameter-cascading/
You can set the dependent parameter on the general configuration page of the datasource parameter. In your case, you can add key1 as a dependent parameter of your key2’s parameter in your key2’s configuration screen.
When this is set, you can use the dependent parameter as any other parameter using the syntax explained here: https://reportserver.net/en/guides/admin/chapters/using-parameters/
Regards,
Eduardo
Eduardo thank you for your response. I have followed your instructions and discovered that it works only when key1 is a simple type parameter (i.e. a text parameter). In that case, the following query will indeed work, for parameter key2:
select t2.id, t2.label
from table2 t2
where t2.somecolumn = ${key1}
However, when trying the same but this time with key1 configured as a database parameter (according to documentation, a database parameter must return TWO columns whereby the first one is assumed to be an ID and the second is assumed to be a description), then the above query returns an “invalid column type” exception.
I’ve solved this myself. The problem was that I had mistakenly configured {key1} to be a multi-select parameter, hence the column type error. Using the $X{IN, t2.somecolumn, key1} expression in the where clause fixed. The original query also worked as is when I changed key1 into a single select parameter.
Thanks