ReportServer Forum

Enrique

Member

Registered: 2020-12-04

Pass values from parameter to Main Sql / Dynamic List

Hello Report Server Team,

I have one report for dynamic list that is like this example below

/*Oracle SQL1*/
Select
column1,
column2,
column3
from table
where column4 IN ${Parameter}

Back in the parameter configuration I have the following

Parameter = Datasource parameter
inside the parameter there's the following Oracle SQL

/*Oracle SQL2 Parameter*/
Select
case
when ${_RS_USER_EMAIL} like 'myemailaddress@domain.com'
then '('||'''variant1'''||','||'''variant2'''||')'     /*On this section of the code I did format the string to be able to be used inside the Oracle SQL1 but it doesn't work I think is because it is formated as string ¿do you know a way that i can format to be able to use ?*/
from dual
end as "username"

Sven Seidel

Member

Registered: 2023-09-07

Re: Pass values from parameter to Main Sql / Dynamic List

/*Oracle SQL2 Parameter*/
Select
case
when ${_RS_USER_EMAIL} like 'myemailaddress@domain.com'
then '('||'''variant1'''||','||'''variant2'''||')'     /*On this section of the code I did format the string to be able to be used inside the Oracle SQL1 but it doesn't work I think is because it is formated as string ¿do you know a way that i can format to be able to use ?*/
from dual
end as "username"

The syntax of this sql is wrong. Correct is:
/*Oracle SQL2 Parameter*/
Select
case
when ${_RS_USER_EMAIL} like 'myemailaddress@domain.com'
then '('||'''variant1'''||','||'''variant2'''||')' 
else 'others'
end as "username"
from dual

Enrique

Member

Registered: 2020-12-04

Re: Pass values from parameter to Main Sql / Dynamic List

done!!!; but still even if the email user is correct and found when i use that value in the parameter on the main query, nothing happens, but if i change the parameter placeholder from ${Parameter} to ('xxxx','yyyyy') the sql execute correctly.

eduardo

Administrator

Registered: 2016-11-01

Website

Re: Pass values from parameter to Main Sql / Dynamic List

Hi Enrique,

if you define ${Parameter} as explained in the parameter section: https://reportserver.net/en/guides/admi … arameters/

for example:
SELECT * FROM T_AGG_ORDER WHERE $X{IN, OR_CUSTOMERNUMBER, P_CUSTNUM}

or

SELECT * FROM T_AGG_ORDER WHERE OR_CUSTOMERNUMBER = $P{P_CUSTNUM}

etc, depending on your specific parameter $P{P_CUSTNUM}, it should work. Pls double check the documentation.

Regards,
Eduardo