Reporting against Oracle using SQL Server Reporting Services

The following is a simple example of how to generate reports against Oracle Databases using Microsoft's SQL Server Reporting Services and ODAC.

For the purposes of this example, it is assumed that you already have a functioning Reporting Service that is up and running.

In order to use the full breadth of Oracle functionality, you will want to download and install ODAC (Oracle Data Access Components) onto the server where you have your current Reports Server. ODAC includes many tools that complement Visual Studio as well as the Instant Client, which allows for full SQL*Net support.

Next, you will need to set-up and test your Oracle networking files for the newly installed Oracle Instant Client.

I edited the following files as follows:

tnsnames.ora

JLC =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (address = (protocol = tcp)(host = rac1-vip)(port = 1525))
      (address = (protocol = tcp)(host = rac2-vip)(port = 1525))
      (LOAD_BALANCE = yes)
    )
    (CONNECT_DATA =
      (SERVER = dedicated)
      (SERVICE_NAME = jlc.colestock.test)
      (failover_mode =
        (type = select)
        (method = basic)
        (retries = 180)
        (delay = 5)
      )
    )
  )

sqlnet.ora

SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES)

Test the connection to the Oracle source:

C:\oracle\product\11.1.0.6>sqlplus report_user/password@JLC

SQL*Plus: Release 11.1.0.6.0 - Production on Wed Apr 2 12:44:52 2008

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SQL>

In my example, I will use the following Dataset for my sample report:

SQL> select player_id, player_name from
  2  cub_roster order by 1,2;

 PLAYER_ID PLAYER_NAME
---------- --------------------------------------------------
         1 Kosuke Fukudome
         9 Felix Pie
        12 Alfonso Soriano
        16 Aramis Ramirez
        25 Derrek Lee

Here are the steps to create a simple, sample report against the aforementioned Oracle data source and table:

Launch the Business Intelligence Development Studio:

Start --> All Programs --> Microsoft SQL Server 2005 --> SQL Server Business Intelligence Studio

Create a New Project:

File --> New --> Project
(specifying the 'Report Server Project' template and provide a Project name of your choosing)

Create a Data Connection to the Database in question:

Server Explorer --> Data Connections --> Add Connection...

Enter the Connection Information



Test the Connection



Afterwards, you should be able to see the database's objects



Create a Shared Data Source which will be used by the reports and deployed to the Reports Server:

Solution Explorer --> Shared Data Sources --> Add New Data Source

Enter General Information



Enter Credentials



Configure the TargetServerURL and OverwriteDataSources for All Configurations for the project:

Solution Explorer --> 'Project Name' --> Properties

Update Properties



Create a sample report:

Solution Explorer --> Reports --> Add New Report

Select the Data Source



Enter the Query



Select the Report Type



Group the Data



Choose the Table Style



Complete the Wizard



Modify the Layout of the Report to fit your needs



Build and Deploy the Project:

Build --> Build 'Project Name'

Build --> Deploy 'Project Name'

You should see something similar to the following output when doing so:

------ Build started: Project: CubRoster, Configuration: Debug ------
Build complete -- 0 errors, 0 warnings
------ Deploy started: Project: CubRoster, Configuration: Debug ------
Deploying to http://localhost/ReportServer$SS1
Deploying data source '/Data Sources/JLC'.
Deploying report '/CubRoster/CubRoster'.
Deploy complete -- 0 errors, 0 warnings
========== Build: 1 succeeded or up-to-date, 0 failed, 0 skipped ==========
========== Deploy: 1 succeeded, 0 failed, 0 skipped ==========

Test the report by navigating to the report's URL. This will result in a call to a URL similar to http://localhost/ReportServer$SS1/Pages/ReportViewer.aspx?%2fCubRoster%2fCubRoster&rs:Command=Render

Run and Review the Report

Labels: Reporting