Click here to Skip to main content
12,458,160 members (57,810 online)
Click here to Skip to main content
Add your own
alternative version

Stats

10.2K views
120 downloads
6 bookmarked
Posted

Creating Report using Report Builder and Oracle DB - Part 1

, 28 Dec 2014 CPOL
Rate this:
Please Sign up or sign in to vote.
In this article series (consist of two parts), I am sharing how to create report using Report Builder by fetching data from Oracle DB. As per requirement, before getting the data we need to run a stored procedure which will get data from table based on given parameter and then we need

Introduction

In this article series (consist of two parts), I am sharing how to create report using Report Builder by fetching data from Oracle DB. As per requirement, before getting the data we need to run a stored procedure which will get data from table based on given parameter and then we need to run populate data in Report Builder with proper formatting.

When we get a chance to work with new tools, we stumble against simple steps and spent more time to achieve results. So I thought to share some of those basic steps to work with Report Builder while fetching data from Oracle DB. This article is for beginners to Report Builder 3.0 and trying to connect with Oracle Database 11g using Oracle SQL Developer.

Outlines

To keep the size of article reasonable, this article is divided into two parts. In this first part we will start by working with SQL Developer and installing Oracle client 11g and Setting it up. Then we will create Table and Stored Procedure in Oracle DB using SQL Developer. Finally in this part, using Report Builder we will create a report which will show data from Oracle DB.

In Part 2, we will provide parameters to report, perform few formatting tasks and look into advance options available.

Contents of both articles are arranged as per following outlines:

Part 1:

  • Overview of Demo
  • Test Connection using SQL Developer
  • Setting up Oracle Client
  • Create new Table and SP
  • Creating Report

Part 2

  • Creating Report with Parameters
  • Formatting Report
  • Formatting using Code
  • More Options
  • References

Overview of Demo

In this demo we have used SQL Developer, Oracle client 11g and Report Builder 3.0. After completing the part 2 of demo application, we will able to see final report as per below screenshot: (click on the image to get more clear and full size view):

Final Report with Export Options

Why we are using Stored Procedure: We can get data for report in Report Builder using Select query itself. I have choosen SP because in enterpise applcations, it is more common scenario to get data based on various parameter passed to a Stored Procedure. And it is more complex than using select query. So I hope using SP with parameters (demostarted in Part 2) I would able to make this demostartion more helpful.

Test Connection using SQL Developer

First run SQL Developer and then follow below steps to connect with Oracle DB Server:

  1. Download Oracle SQL Developer, and run. First click on Connections then choose "New connection". You will get a popup window where you need to fill following information:
    • Connection Name
    • Username
    • Password
    • Hostname
    • Port
    • SID(Server Name)
    Note: Above information you will need in below steps so make a note of what information you are filling in “Select Database Connection” window.

    Coonect to DB using SQL Developer
     

Setting up Oracle Client

Once you are able to connect with Oracle DB Server using SQL Developer you are confirmed about server connection parameters.
Warning: In order to access Oracle DB via Report Builder you need to install Oracle Client 11g for Windows if it is not there already. Otherwise you will get error roughly saying that Report Builder cannot connect with Oracle DB.

There are following pre-requisite steps to enable report builder to connect with Oracle DB.

  • Install Oracle Client
  • Set Environment Variable
  • Configuring tnsnames.oRA file

We will take following steps to set up Oracle Client:

  1. You need to download and install the Oracle Client software which is available on Oracle website. You need to choose appropriate setup file as per your Operating System details.
     
  2. Adding Environment Variable: Now you need to add Environment Variable for Oracle. While you will install Oracle Client 11g, it will create a folder called “app” in your C or D drive based on setting. In my case it is created that folder in D derive.
    Now look for "sample" folder path which you need while adding Environment Variable. For my case path of sample folder is like:
    D:\app\userName\product\11.2.0\client_1\network\admin\sample
    Go to your system property, click on Advanced System Setting, you will get a popup as shown below.

    Set Environment Variable

    Click on Environment Variable button, you will get another popup as shown below.

    Create New Environment Variable

    Now Click on New button, you will see next popup, fill below information as: In First Textbox, Variable Name: TNS_ADMIN In Second Textbox, Variable Value: copy path till “sample” folder as explained above and then Click OK. We are done with environment setup.
     
  3. Configuring tnsnames.oRA file: tnsnames.oRA is a configuration file that contains database servers address for establishing connections to Oracle DBs. We can provide alias which maps to a database. Let us define our database address by providing alias.

    Go to this path: D:\app\username\product\11.2.0\client_1\network\admin\sample. Then open “sample” folder. There you will get tnsnames.oRA file, open this file in notepad add your server related details using alias as shown in below in screenshot.
    Note: HOST, PORT and SERVICE_NAME(SERVER NAME) must be same as you have filled in SQL Developer while connecting to Oracle DB.

    Set Environment Variable
     

Create a new Table and SP

Now using SQL Developer we will create a table and stored procedure which we would be using for Report. You can download the attached zip file with this article which is having sample data and code snippets.

  1. Create Table with following fields as shown in below screenshot and fill some data in this table.

    Create Table
     
  2. Create a stored procedure called SP_GETEMPLOYEES. In stored procedure, we will use SYS_REFCURSOR as out parameter. Following is the code:
    CREATE OR REPLACE
    PROCEDURE SP_GETEMPLOYEES (e_recordset OUT SYS_REFCURSOR)IS
    BEGIN
        OPEN e_recordset FOR
        SELECT * FROM EMPLOYEE;
     END SP_GETEMPLOYEES;
    
    Since this stored procedure will have only a simple select query to fetch data from Employee table as shown below.

    Create Stored Procedure
     
  3. Now we will execute “SP_GETEMPLOYEES” stored procedure to verify that our stored procedure is working fine or not. You can see the syntax to execute the stored procedure. The code to execute SP is given below:
    var c refcursor;
    execute SP_GETEMPLOYEES(:c);
    print c;
    
    As you can see in below screenshot we executed the stored procedure and able to see the data also.

    Execute Stored Procedure

Creating Report

Now we will create a report in Report Builder and use “SP_GETEMPLOYEES” stored procedure to fetch data. To do that we will follow below steps:

  1. Open report builder, right click on DataSource then click on "Add Data Source", give it appropriate name. Click on “Use a connection embedded in my report” radio button then select connection type as “Oracle”. Below is the screenshot for above operation:

    Add DataSource in Report Builder
     
  2. Click on “Build” button as shown in above screenshot, enter Server name. Here server name would be alias of connection string which you have given in tnsnames.oRA file. Here our alias is “reportest”. Then enter user name and password. Click on “Test Connection”. You will get popup as Test results” shown below.
    Note : If test Connection is failed, verify you have filled correct information in tnsnames.oRA file and closed brackets properly in that file.

    Test Connection
     
  3. Our connection is succeeded, next we will create Dataset, to do that right click on Datasets then click on Add Dataset.

    Add Data Source
     
  4. Select stored procedure name from dropdown or you can type stored procedure name also then click OK.

    Select SP as Data Source
     
  5. As you will click on OK button, you would be able to see dataset is added in Datasets folder as shown below.

    Add Data Source
     
  6. Now we will create a report. Go to table tab of Ribbon of Report Builder and click on Table Wizard, “choose a dataset” click on next.

    Choose Dataset
     
  7. Arrange fields which you want to show in report. Here we will select all fields. Select the fields from “Available fields” area and drop on “Values” area as show below. Then click on Finish button.

    Arrange Fields
     
  8. Click on Run button in tab or press F5 to run the report. Report would be created for you.

    Add Data Source

    As of now we are able to create a simple report. In next part of this article we will add parameter to SP and do some formatting work.

Conclusion

In this article we learned how to create a simple report using Report Builder while fetching data from Oracle DB using Stored Procedure. In Part 2 we will add parameter to Stored Procedure to filter data, do some formatting in Report and look into advance options available. Your comments and suggestions are most welcome. Thanks.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Snesh Prajapati
Software Developer
India India
I am a Software Developer working on Microsoft technologies. My interest is exploring and sharing the awesomeness of emerging technologies.

You may also be interested in...

Comments and Discussions

 
QuestionNeed concepts of RICE cpmonets in oracle apps Pin
Member 125719317-Jun-16 21:42
memberMember 125719317-Jun-16 21:42 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.160826.1 | Last Updated 28 Dec 2014
Article Copyright 2014 by Snesh Prajapati
Everything else Copyright © CodeProject, 1999-2016
Layout: fixed | fluid