Click here to Skip to main content
12,698,179 members (22,128 online)
Click here to Skip to main content
Add your own
alternative version

Stats

7.1K views
99 downloads
4 bookmarked
Posted

Creating Report using Report Builder and Oracle DB - Part 2

, 28 Dec 2014 CPOL
Rate this:
Please Sign up or sign in to vote.
This is Part 2 of the article series (<a href="http://www.codeproject.com/Articles/857489/Creating-Report-using-Report-Builder-and-Oracle-DB">Part 1 here</a>). 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 given 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.

This is second part of the article series which is having two parts. In 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 demo application will be as shown in following figure (Click on the image to get fill size image):

Final Report with Export Options

This article is for beginner to Report Builder 3.0 and I suggest that before reading this part please have a look on Part 1

Outlines

To keep the size of article reasonable, this article is divided into two parts. 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

Creating Report with parameters

In part 1, we just created simple report which does not provide any filter. Let's say if user want 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 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 below screenshot. Here DEPARTMENTNAME is a input parameter and having same name as we have declared in stored procedure.

    Dataset with Parameter
     
  4. Now the steps for adding Reports and table are 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 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 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 DEPARTMETNNAME parameter and click on "Parameter Properties". Here you have facility to show user a nice UI as a dropdown if there are only a set of value user need to choose or many other formats depends 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 facility to provide different display than 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 above just above the table header. In first row give report name as “EMPLOYEE DETAILS REPORT” as Name of Report. And in 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 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 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"
    ) 
    
    Above steps are highlighted in below screenshot:

    Use Code for Background Color

    Now run the report by clicking on Run button as shown in above screenshot and you will get desired result.
  2. As you can see in 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 purpose like Visibility or formatting. For more, have a look this MSDN page and this article.
  • Tablix: It provides you facility to provide multiple tables (data regions) in a single Report. For more please read on this MSDN page.
  • Interactive Sort: It enable 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 on this MSDN page.
  • Explore More Possibilities: There is a complete guide available on MSDN describing various features of Report Builder here.

Conclusion

In 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 advance 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 18:43
professionalYogesh Kumar Tyagi11-Jan-15 18:43 
AnswerRe: My vote of 5 Pin
Snesh Prajapati11-Jan-15 18:48
mvpSnesh Prajapati11-Jan-15 18:48 
GeneralRe: My vote of 5 Pin
Yogesh Kumar Tyagi11-Jan-15 19:20
professionalYogesh Kumar Tyagi11-Jan-15 19: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.

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