Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
100 | |
80 | |
63 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |