Forum Discussion
Query Parameters in Paginated Report(Text Editor) against DB2 database
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!!
6 Replies
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 !
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 = @EmployeeIDUnion
select distinct ACCT_ID from V_EMPLOYEE_DIM
where EMPLOYEE_ID = @EmployeeIDand ACCT = @ACCTID
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-using-ODBC/td-p/2925587For multi-value parameters, typically we use JOIN(Parameters!pCategory.Value, “,”)
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 XuI 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.
JOIN(Parameters!pCategory.Value, “,”) is not working when multiple values are selected.
