Report Builder 3.0 – Oracle Data Source Issue

How to solve Publish Reports via Report Builder 3.0 not able to run against the Oracle Data Source.

I had installed the x64 bit Oracle driver on the server, but was unable to get it to work using the Report Builder 3.0

image

When you open the Report builder and add a new Data Source for Oracle you can choose between the :

.NET Framework Data Provider for Oracle or a regular ODBC or OLE DB connection

As you can see the Oracle Data Source supports only till version 9i.

So if you want to use a more recent version of Oracle, you need to use the ODBC selection.

Anyhow the news here is that Report Builder 3.0 will ONLY recognize the 32bit drivers.

Lessons learned :

RB 3.0 Data Sources = x32 bit

SSRS Data Sources = x64 bit

If you publish reports to a SharePoint library make sure you have both version of the Oracle driver installed on the server side by side.

REMARKS:

1. The older Oracle versions have a different Join Types. The ANSI SQL based one, while MS SQL does not have by default.

So you take this in account when write your queries in the report Builder, which gives you an other challenge 🙂

2. Report Builder has a different way to access the Parameter values using a ‘:’ instead of an ‘@’ character in your Query statement

MS SQL =  … WHERE DatePart(year,sub.StartDate) = @Year

ORACLE = …. WHERE  To_Char( StartDate,’yyyy’)  = :Year

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: