Click here to Skip to main content
14,040,447 members
Click here to Skip to main content
Add your own
alternative version

Stats

16.4K views
151 downloads
4 bookmarked
Posted 28 Dec 2014
Licenced CPOL

Creating Report using Report Builder and Oracle DB - Part 2

, 28 Dec 2014
Rate this:
Please Sign up or sign in to vote.
In this article series, I am sharing how to create report using Report Builder by fetching data from Oracle DB.

Introduction

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

This is the second part of the article series which has two parts. In the first part, we saw how to do setup for SQL developer, Oracle Client and Report Builder. Then, we created Table and SP in Oracle DB using SQL Developer. Finally, using Report Builder, we created simple Report which will show data from Oracle DB.

Now here in Part 2, we will provide parameter to report and enable Stored Procedure to filter data based on given parameter. Then we will do formatting for the report and finally look into more options available like Tablix and Functions. The final report of the demo application will be as shown in the following figure (click on the image to get fill size image):

Final Report with Export Options

This article is for beginners to Report Builder 3.0 and I suggest that before reading this part, please have a look at Part 1.

Outlines

To keep the size of the article reasonable, this article is divided into two parts. Contents of both articles are arranged as per the 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

Creating Report with Parameters

In part 1, we just created a simple report which does not provide any filter. Let's say if user wants a facility to generate report having employees of a particular department. To achieve that, first we need to update Stored Procedure so that it can take an input parameter and then we need to modify report accordingly. Following are the steps to implement this requirement.

  1. Replace the stored procedure called “SP_GETEMPLOYEES” so that it can accept department name as input. We will use SYS_REFCURSOR as out parameter. This stored procedure will have only a simple select query to fetch data from Employee table base on given department as given below:
    CREATE OR REPLACE
    PROCEDURE SP_GETEMPLOYEES (DEPARTMENTNAME varchar2, e_recordset OUT SYS_REFCURSOR)IS
    BEGIN
    OPEN e_recordset FOR
    SELECT * FROM EMPLOYEE where DEPARTMENT = DEPARTMENTNAME;
    END SP_GETEMPLOYEES;
  2. Now we will execute “SP_GETEMPLOYEES” stored procedure to verify that our stored procedure is working fine or not. Following is the syntax to execute the stored procedure. Syntax to execute SP:
    var c refcursor;
    execute SP_GETEMPLOYEES('DBA',:c);
    print c;
    Below is the screenshot of stored procedure execution and we are able to see filtered data:

    Execute SP with Parameter

  3. Now the steps for adding DataSource and Dataset is the same as we did in Part 1 (within section "Creating Report" > Steps # 1 to 4 ). But this time after adding Dataset, you would be able to see EMPLOYEEDATASET dataset is added in Datasets folder and a new parameter called DEPARTMENTNAME is added inside "Parameters" folder as shown in the below screenshot. Here, DEPARTMENTNAME is an input parameter and having the same name as we have declared in stored procedure.

    Dataset with Parameter

  4. Now the steps for adding Reports and table are the same as given in Part 1 (Within section "Creating Report" > Steps # 6 and 7). Once you complete those, click on Run button in tab or press F5 to run the report. Report would be created for you. While running the report, you would see a text box at the upper side where you can provide the value of "DEPARTMENTNAME" which will be passed to SP and the report would be working fine and filtering the data as expected.

    Provide Value for Parameter

  5. In the above screenshot, there is no space between "DEPARTMENT NAME" for the label of filter. And what if user want a dropdown to show all department names. Let us implement that by right clicking on DEPARTMENTNAME parameter and click on "Parameter Properties". Here, you have the facility to show user a nice UI as a dropdown if there are only a set of values user needed to choose or many other formats depending on requirements.

     

    Parameter Properties

  6. Further, we will do formatting to show departments as a dropdown with few selected value. In Prompt text box, you can give space or any other text also what you want to show as label for dropdown.

    Parameter Dispay Name Change

  7. Here, if user wants to show data in Capital latter in dropdown but data is saved in Oracle table in other format. To handle such scenario, we have the facility to provide a different display than the value of a parameter. Follow the below screenshot and click OK.

    Parameter Dropdown Values

  8. Run the report, hope you would be able to see department names in dropdown.

    Parameter Dropdown Values

Formatting Report

Report builder provides you options to format the report which are similar to basic operation available for MS Excel / Word. Like formatting of Text in a Cell, Adding rows/ columns, Adding Header/Footers, etc. It provides you various Built-In-Fields also which you can use in your report by simply dragging and dropping.

Let's say you want to add Execution time and a Name to the report. Following is what you need to do.

Insert two rows just above the table header. In the first row, give report name as “EMPLOYEE DETAILS REPORT” as Name of Report. And in the second row, write “Rerun On” which would be label then drag and drop “Execution Time” from Built-in Fields in left pane. It will show report execution date and time. Execution Time is a function which is available as Built-in Fields in left pane. There are many more such Built-in Fields, you can use them as per the need.

Add Execution Time

Formatting Using Code

Many a times, we need to put colors and other kind of highlighting based on the same condition. In such cases, we need to do the formatting using code.

  1. Let us say, user wants to see records in different color base on its Address. To achieve it, go to report builder, navigate Properties window. (In case you are not able to see Properties window, click on View tab in upper Ribbon, there you will get properties checkbox.) Select a row in table and in Properties window, look for "BackgroundColors", click on its value textbox and then "Expression" hyperlink. Now write SWITCH case in Expression window as the code given below and click OK.
       =SWITCH(
       Fields!ADDRESS.Value = "Bangalore", "LightBlue",
       Fields!ADDRESS.Value = "Hyderabad", "Moccasin"
    ) 
    The above steps are highlighted in the below screenshot:

    Use Code for Background Color

    Now run the report by clicking on Run button as shown in the above screenshot and you will get the desired result.

  2. As you can see in the below screenshot, using Export option, you can save your report in given formats.

    Final Report with Export Options

More Options

  • Custom Functions: You can write custom code in VB.NET and use those functions in Report for various purposes like Visibility or formatting. For more, have a look at this MSDN page and this article.
  • Tablix: It provides you the facility to provide multiple tables (data regions) in a single Report. For more, please read on this MSDN page.
  • Interactive Sort: It enables a user to toggle between ascending and descending order for rows in a table or for rows and columns in a matrix. For more, have a look at this MSDN page.
  • Explore More Possibilities: There is a complete guide available on MSDN describing various features of Report Builder here.

Conclusion

In the first part of this article series, we learned how to create a simple report using Report Builder while fetching data from Oracle DB using Stored Procedure. In this part, we learned how to add parameter to Stored Procedure to filter data, do some formatting in Report and look into advanced options available. Your comments and suggestions are most welcome to make this article more useful. Thanks!

References

  1. Report Builder Help for SQL Server 2014
  2. Report Builder 3.0
  3. Oracle Database 11g
  4. Oracle SQL Developer

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

 
QuestionMy vote of 5 Pin
Yogesh Kumar Tyagi11-Jan-15 17:43
professionalYogesh Kumar Tyagi11-Jan-15 17:43 
AnswerRe: My vote of 5 Pin
Snesh Prajapati11-Jan-15 17:48
professionalSnesh Prajapati11-Jan-15 17:48 
GeneralRe: My vote of 5 Pin
Yogesh Kumar Tyagi11-Jan-15 18:20
professionalYogesh Kumar Tyagi11-Jan-15 18:20 

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.

Permalink | Advertise | Privacy | Cookies | Terms of Use | Mobile
Web03 | 2.8.190425.1 | Last Updated 28 Dec 2014
Article Copyright 2014 by Snesh Prajapati
Everything else Copyright © CodeProject, 1999-2019
Layout: fixed | fluid