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

[DamianDavies]

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.

[DamianDavies · Accepted 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;)

[CJ74]

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! 

[SohaibS]

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 · Accepted solution]

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

[amitchandak]

@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

[DamianDavies]

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?