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):
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
To keep the size of article reasonable, this article is divided into two parts. Contents of both articles are arranged as per following outlines:
- Overview of Demo
- Test Connection using SQL Developer
- Setting up Oracle Client
- Create new Table and SP
- Creating Report
- Creating Report with parameters
- Formatting Report
- Formatting using Code
- More Options
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.
- 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
OPEN e_recordset FOR
SELECT * FROM EMPLOYEE where DEPARTMENT = DEPARTMENTNAME;
- 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;
Below is the screenshot of stored procedure execution and we are able to see filtered data:
- 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.
- 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.
- 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.
- 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.
- 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.
- Run the report, hope you would be able to see department names in dropdown.
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.
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.
- 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.
Fields!ADDRESS.Value = "Bangalore", "LightBlue",
Fields!ADDRESS.Value = "Hyderabad", "Moccasin"
Above steps are highlighted in below screenshot:
Now run the report by clicking on Run button as shown in above screenshot and you will get desired result.
- As you can see in below screenshot, using Export option you can save your report in given formats.
- 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.
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.
1. Report Builder Help for SQL Server 2014
2. Report Builder 3.0
3. Oracle Database 11g
4. Oracle SQL Developer