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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
oj606481
Frequent Visitor

Paginated Reports, Not all variables bound

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.

 

.Screenshot 2021-05-20 104623.png

1 ACCEPTED SOLUTION
Hariharan_R
Solution Sage
Solution Sage

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

View solution in original post

2 REPLIES 2
Hariharan_R
Solution Sage
Solution Sage

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

Hi Hari,

 

Bless you, that worked.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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