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):
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:
- 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.
- 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;
- 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:
- 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.
- 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.
- 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.
- 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 the facility to provide a different display than the 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.
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.
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.
- 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:
Now run the report by clicking on Run button as shown in the above screenshot and you will get the desired result.
- As you can see in the below screenshot, using Export option, you can save your report in given formats.
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
- Report Builder Help for SQL Server 2014
- Report Builder 3.0
- Oracle Database 11g
- Oracle SQL Developer
