cancel
Showing results for 
Search instead for 
Did you mean: 
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
Resolver I
Resolver I

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
Resolver I
Resolver I

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

Hi Hari,

 

Bless you, that worked.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.