Query Parameters in Paginated Report(Text Editor) against DB2 database

[SowjanyaB]

Hi All,

We are using SQL Queries in Power BI Report builder for reports development aganist DB2 database.

I am facing an issue to pass values using parameters in SQL Query.

select distinct ACCT_ID from V_ACCT_EMP_REL
where EMPLOYEE_ID = @EmployeeID

ERROR [42S22] [IBM][CLI Driver][DB2/AIX64] SQL0206N "@EMPLOYEEID" is not valid in the context where it is used. SQLSTATE=42703

What should be the syntax here to use parameters in the SQL Query?

Thanks!!

[v-xuxinyi-msft]

Hi @SowjanyaB 

Did sevenhills 's method solve your problem? If yes, could you please mark it as solution? This will help more users who are facing the same or similar difficulties. Thank you!

If you still have questions, please feel free to ask me.

Best Regards,
Yulia Xu

[SowjanyaB]

I should pass only ? not ?EmployeeID. In this case, if i am going to use same parameter multiple times, it will be difficult to maintain the code. It is creating ambiguity when more than one parameter should be used multiple times.

Replacing @EmployeeID with ? worked for DB2 but it didnt suffice for the complete requirement.

[SowjanyaB]

JOIN(Parameters!pCategory.Value, “,”) is not working when multiple values are selected.

[SowjanyaB]

When i use ? it works.

Yes, i have configured dataset parameter to report parameters in Paramaters section.

I havent used for multivalue yet.

What if i have multiple parameters that should be used in more than one place, how should that be used?

For Ex:

select distinct ACCT_ID from V_ACCT_EMP_REL
where EMPLOYEE_ID = @EmployeeID

Union 

select distinct ACCT_ID from V_EMPLOYEE_DIM
where EMPLOYEE_ID = @EmployeeID

and ACCT = @ACCTID

[sevenhills]

As per my knowledge, SSRS - DB2 - Parameterized query does not use "@", instead uses "?". That is why I suggested to use "?".

See if this link gives you some direction: https://community.fabric.microsoft.com/t5/Desktop/Paginated-report-parameters-and-IBM-i-iSeries-DB2-...

For multi-value parameters, typically we use JOIN(Parameters!pCategory.Value, “,”)

[sevenhills]

Try these steps
~ Replace @EmployeeID with ? 
~ Are you passing the parameter value as null ?
~ curious, is it mulit valued parameter? then use JOIN
~ silly to ask, but, did you configure the dataset parameter value to report parameter value?

 See if this works !