Creating Report using Report Builder and Oracle DB - Part 1

• Source Code for Part 1 - 8 KB

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):



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.
   
   
    

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.
   
   
   
   Click on Environment Variable button, you will get another popup as shown below.
   
   
   
   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.
   
   
    

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.
   
   
    
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.
   
   
    
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.
   
   

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:
   
   
    
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.
   
   
    
3. Our connection is succeeded, next we will create Dataset, to do that right click on Datasets then click on Add Dataset.
   
   
    
4. Select stored procedure name from dropdown or you can type stored procedure name also then click OK.
   
   
    
5. As you will click on OK button, you would be able to see dataset is added in Datasets folder as shown below.
   
   
    
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.
   
   
    
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.
   
   
    
8. Click on Run button in tab or press F5 to run the report. Report would be created for you.
   
   
   
   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.