Paginated Report Builder Oracle Data Source Connectivity issues

[wandasoozq]

Good morning,

I have downloaded the latest version of Paginated Reports (updated 10/28/2019). I have the 64-bit Oracle client installed, and I have no problem connecting to our Oracle datasource in PBI desktop and the service. However, I have been unable to connect to our database from the Report Builder. I have tried various formats of the connection string with no luck. The error I am receiving is "Unable to connect to the data source"...Attempt to load Oracle client libraries threw BadImageFormatException. This problem will occur when running in 64 bit mode with the 32 bit Oracle client components installed.

We have tried this on my current machine (which has both 64 bit and 32 bit installed) as well as a brand new machine with only 64 bit installed. The components were loaded from recommended downloads from PBI and verified.

Any help would be appreciated!!!

[michaeldaynes]

64 bit version now available.

https://powerbi.microsoft.com/en-us/blog/whats-new-in-the-june-update-of-power-bi-report-builder/

[rderekx]

i had same problem on 64bit laptop

i found this: https://docs.microsoft.com/en-us/sql/reporting-services/report-data/oracle-connection-type-ssrs?view...

after doing these steps

32-bit drivers for Report Builder

Report Builder uses Managed ODP.NET for authoring paginated reports. The following steps are only needed when using Oracle ODAC drivers 12.2 and later as they install by default to a non-machine-wide configuration for a new Oracle home installation. These steps assume you've installed the ODAC 18.x files to c:\oracle32 and the file version of your Oracle.ManagedDataAccess.dll is 4.122.18.3.

1. On the Oracle download site, install the oracle 32 bit odac Oracle UI

2. Register ODP.NET Managed Client to GAC:

   C:\oracle32\product\18.0.0\client_1\odp.net\bin\4\OraProvCfg.exe /action:gac /providerpath:C:\oracle32\product\18.0.0\client_1\odp.net\managed\common\Oracle.ManagedDataAccess.dll

3. Add ODP.NET Managed Client entries to machine.config:

   C:\oracle32\product\18.0.0\client_1\odp.net\bin\4\OraProvCfg.exe /action:config /force /product:odpm /frameworkversion:v4.0.30319 /productversion:4.122.18.3

Then i added all my oracles connections into TNSNAMESORA and PBI report builder works fine now for oracle connections

[Anonymous]

We are having the same issue with report builder for report server and an oracle connection.  Has the bug been resolved in Report Builder?  Thanks

[bradsy]

Hi wandasoozq,

Report Builder is 32 bit only, so you will definitely need the 32 bit drivers. It has been a little while since I had to configure Oracle, but I do recall it being the full install. I found this on my machine. There is probably a newer version to try.

Oracle Data Provider for .NET, Managed Driver for ODAC 12c Release 4

[wandasoozq]

Thank you. We have loaded all of the 32-bit drivers and are still unable to connect. We do not get an error message, but the connection attempt keeps churning. I can connect via the PBI desktop/service connections I have established, but I am still not able to do so natively.

[v-jobesa]

Hi,

Is this happening with latest version of PowerBI Report Builder v15.0.1517.0 ? Or the older Report Builder for SQL Server 2016?

[v-jobesa]

never mind... I see above you installed recent 10/18 version...

[wandasoozq]

Latest version (v15.0.1517.0)...

[v-jobesa]

Can you try the older Report Builder here:

https://www.microsoft.com/en-us/download/details.aspx?id=53613

Your issue might be related to a recently found bug which does not seem to afffect the older Report Builder above. 

[wandasoozq]

I downloaded the older version you suggested, and, out of the box, I am still unable to connect to my Oracle database. However, I am no longer getting the 32-bit/64-bit ODAC error that I was getting before. I am now just getting the normal "unable to connect to data source" error (which could be a lot of different things - version, connection string, drivers).  Thank you for the new direction. I will keep trying.

[onbi]

Were you successful in connecting to Oracle DB from Power BI Report Builder tool.

If so then, can you please share what had to be done to get it connected to Oracle DB?

[wandasoozq]

No, unfortunately, we were unable to connect natively. I can connect using the datasources I have set up in PBI desktop, but I still would like to connect the same (easy) way we can in the desktop version. We have another reporting tool that we use, so this has been placed on the back-burner.

[onbi]

It's surprising that Microsoft is not providing any support on how to connect this tool to Oracle DB. I've been looking around on how to connect to Power BI Report Builder for a day now and couldn't get proper direction.

Microsoft Support... please help!

[bradsy]

This article may help. https://www.codeproject.com/Articles/857489/Creating-Report-using-Report-Builder-and-Oracle-DB

[onbi]

Thank you @bradsy I tried with the approach mentioned in the article, but the Report Builder tool throws the error ORA-12154: TNS : could not resolve the connect identifier specified.

Not sure, if its the problem with the current version of Power BI Report Builder.

[bradsy]

Trying more, this might help? 

https://www.datavail.com/blog/ora-12154-tnscould-not-resolve-the-connect-identifier-specified/

[onbi]

Thanks once again @bradsy I checked the TNS_ADMIN and tnsnames.ora file and it looks correct. Also, I tried from SQL Developer and could connect from the tool as well. I tried connecting to my SQL Plus session using tnsnames.ora file, which exists in the same path set in TNS_ADMIN environment variable.

Not sure, what am I missing here.

[bradsy]

We did some more research. It looks like there is a bug with processing new reports that connect to Oracle. Existing reports seem to work fine. We have not figured out where the issue is, but if you have existing reports and you create a new report, check the connection string properties. There may be something there. We will fix for next update, which is looking to be around January time frame. 

If you are editing and working with existing reports and having a problem, that is a new issue, let us know.

[bradsy]

The older Report Builder looks to work correctly with Oracle, does not have the bug. Can you try that:

https://www.microsoft.com/en-us/download/details.aspx?id=53613

It does not work with Power BI Server however.

[onbi]

Thank you @bradsy  for the download link of the earlier version of Report Builder. I installed and tried to configure the Oracle connection. However, the "build" button is disabled when "Oracle Database" is selected as Data Source. It seems like the Connection Driver for Oracle is not available in this release