Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don'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.

Reply
DamianDavies
Frequent Visitor

Paginated report parameters and IBM i (iSeries) DB2 using ODBC error

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.

2 ACCEPTED SOLUTIONS
DamianDavies
Frequent Visitor

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;)

View solution in original post

DamianDavies
Frequent Visitor

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

 

DamianDavies_1-1671603674360.png

 

 

DamianDavies_0-1671603610407.png

 

View solution in original post

6 REPLIES 6
CJ74
Regular Visitor

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. 

 

CJ74_1-1720820113313.png

 

CJ74_0-1720819321300.png

 

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! 

SohaibS
Frequent Visitor

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. 

DamianDavies
Frequent Visitor

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

 

DamianDavies_1-1671603674360.png

 

 

DamianDavies_0-1671603610407.png

 

DamianDavies
Frequent Visitor

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;)

amitchandak
Super User
Super User

@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

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

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?

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.