Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
I am trying to build query parameters into a Power BI Report Builder paginated report from an iSeries DB2/400 database but it gives the following error:
ERROR [42S22] [IBM][System i Access ODBC Driver][DB2 for i5/OS]SQL0206 - Column or global variable @PCCODE not found.
Queries without parameters work fine. An example simple select query:
select * from PPGLDPKP where PKGLAC = @PCCODE
I have scoured the Internet for help 😞
Any ideas or work arounds much appreciated.
Thank you.
Solved! Go to Solution.
UPDATE:
I tried setting up the connection to the iSeries going via SQL as a linked server. It worked and accepted the query parameters and sent them to the source database fine. However, the performance was absolutely atrocious!!
I have worked around the problem by filtering the query to a small set of data and then using report filters to fine-tune it further. This works acceptably well for the small record sets I've been testing on so far.
d;)
Final solution:
1. Create your parameters
2. In the query, use ? for every parameter
3. On the dataset properties parameters page you will get multiple ? parameter slots. Set each to the required parameter in order. The order is important
Apologies if I missunderstood the OP's question, but I have found a workaround that I haven't come across yet. Figured I would share it here in case someone else is in a similar bind with parameters in a DB2 query. I was also trying to figure out how to use a CTE in my query to overcome the issue of trying to use one parameter in a CASE statement inside a WHERE clause. Since every instance of ? needs its own parameter, I was stuck trying to figure out how to accomplish this with the one parameter where the user chooses between 4 options. The answer is similar to @DamianDavies response. Add a new parameter for each instance of ? in the wuery and reference each one to the same parameter.
I hope this helps.
If this is common knowledge, then hopefully someone else as clueless as me will come across this and learn from my frustration!
I am also using a similar setup iSeries and AS400/db2 server to build a paginated report with Report Parameters. I finally have mine working.
1st create the Parameter with name PCCODE. You can leave default settings on this I believe, my column is an integer, but i left it on text and it works.
2nd Add the parameter reference to your sql
select * from PPGLDPKP where PKGLAC = 'Parameters!PCCODE.Value'
This worked for me, hopefully it will work for you too.
Final solution:
1. Create your parameters
2. In the query, use ? for every parameter
3. On the dataset properties parameters page you will get multiple ? parameter slots. Set each to the required parameter in order. The order is important
UPDATE:
I tried setting up the connection to the iSeries going via SQL as a linked server. It worked and accepted the query parameters and sent them to the source database fine. However, the performance was absolutely atrocious!!
I have worked around the problem by filtering the query to a small set of data and then using report filters to fine-tune it further. This works acceptably well for the small record sets I've been testing on so far.
d;)
@DamianDavies , You have m parameter try like
"select * from PPGLDPKP where PKGLAC =" _param
First, write a query and then modify the code in advance query
Thanks @amitchandak the Paginated Reports Builder doesn't use Power Query Editor as far as I know. I think that's what you're referring to by using an "m parameter" and advanced editor?
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
145 | |
79 | |
63 | |
52 | |
47 |
User | Count |
---|---|
216 | |
89 | |
76 | |
67 | |
60 |