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
Super User
Super User

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
Super User
Super User

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
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.