Paginated Reports, Not all variables bound

[oj606481]

Greetings, 

I am working on a paginated report in Power BI Report Builder, and getting the attached error "A data source used by this report returned an error. An exception encountered while accessing the target data source ERROR [07001] [Oracle][ODBC][Ora]ORA-01008: not all variables bound", screenshot shown below.

I am using the ODBC(Preview) data source in Report Builder, with a simplified query structure like:

SELECT
*
FROM <table_1>

WHERE <field_1> BETWEEN :p_variable_1 AND :p_variable_2
AND (<field_2>= :p_variable_3 or <field_3>= :p_variable_3 or <field_4>= :p_variable_3 or <field_5>= :p_variable_3);

If I remove the parameters and hard code the values, the query works fine.

I am also able to run the query in SQL Developer without issues, and if I change the Data Source to "Oracle Database" the query also runs without issues.

I had initally tried Publshing the paginated report with the Data Source sourse as Oracle Database, which runs on my local machine, but when published to the server I get "A data source used by this report returned an error. An exception encountered while creating the target data source Unable to find the requested .Net Framework Data Provider. It may not be installed." (We have .NET Framework 4.5). It went on to specify Additional steps are required to view this report, which brought me to instructions on setting up and using ODBC, which I took to mean I can only use Paginated Reports with an ODBC Data Source. If that assumption is incorrect I am open to correction, whatever works.

TL;DR - Trying to publish a paginated report in Power BI Report Builder using ODBC but encountering "ERROR [07001] [Oracle][ODBC][Ora]ORA-01008: not all variables bound". When using Oracle DB as the Data Source yields "Unable to find the requested .Net Framework Data Provider. It may not be installed". 

Any help would be appreciated with getting this running.

.

[bradleylarsen]

Solution:  Within Oracle, pump the parameter's value into a CTE (WITH Clause), cross join that into your query, and access it from there.  Report Builder and Paginated Reports seem to have their own SQL compiler that isn't 100% compatible.  For example, real numbers will cause a strange error.  

Example:

WITH w_Variables as (SELECT :param1 as myParam from dual)

SELECT sysdate as A_Date, w_Variables.myParam 

FROM dual 

     CROSS JOIN w_Variables

WHERE w_Variables.myParam = 'Y' 

-- Answering 'Y' will return a result, answering anything else won't.

<RANT>I've developed hundreds of reports and now suddenly, this.  Must have been part of my laptop reload last month when I reloade deverything.  MICROSOFT, TEST YOUR ....</RANT>  LOL

[Hariharan_R · Accepted solution]

Hi

  instead of using :Parametername, use ? (Question Mark). Usually ODBC look for ? wherever you need to define the parameters.

SELECT
*
FROM <table_1>

WHERE <field_1> BETWEEN ? AND ?
AND (<field_2>=? or <field_3>= ? or <field_4>=? or <field_5>= ?);

Map the ? with appropriate variables in Parameters section of the dataset.

Thanks

Hari

[PeterTran]

It works.

My question is how it knows to map the ? question to the correct parameter.

Thank you Hari

[oj606481]

Hi Hari,

Bless you, that worked.