Applying Conditional Formatting in the JSP Web Source

The goal of this tutorial is to show you how to modify the JSP code for a Web report to conditionally format data in the report output, and to highlight alternating rows.

Approximately 1 hour

Topics

This tutorial covers the following topics:

Place the cursor over this icon to load and view all the screenshots for this tutorial. (Caution: Because this action loads all screenshots simultaneously, response time may be slow depending on your Internet connection.)

Note: Alternatively, you can place the cursor over each individual icon in the following steps to load and view only the screenshot associated with that step.

 

Overview

Oracle Reports is designed for the Internet. You can develop Web reports simply and quickly in Reports Builder. Oracle Reports supports JavaServer Pages (JSPs) as the underlying technology to enable you to enhance Web pages with information retrieved using Reports Builder. JSP technology is an extension to the Java servlet technology from Sun Microsystems that provides a simple programming vehicle for displaying dynamic content on a Web page. A JSP is an HTML page with embedded Java source code that is executed on the Web server or application server. The HTML provides the page layout that is returned to the Web browser, and the Java provides the business logic.

For additional background information about JSP technology, see the JavaSoft web site at http://www.javasoft.com/products/jsp/.

Back to Topic List

 

In this tutorial, you will create a Web report that displays employee information for each manager. You will enhance the report by applying conditional formatting. You'll code a format trigger that will highlight employee salaries that fall within specified ranges, and then reference that trigger in the Web source code for your report. You will also add Java code to the Web source such that alternating rows in the output display with background color.

Back to Topic List

Prerequisites

Before starting this tutorial, you should:

1.

Have access to or have installed Oracle Reports Developer.

 

2.

Have access to or installed the sample schema. This tutorial uses the HR schema included in the Oracle10g database.

 

3. Download reportsOBE.zip and unzip into your working directory.
4.

Perform the following steps:

a.

Copy the file rwgray.css from your working directory to ORACLE_HOME\reports\docroot\css, replacing the existing rwgray.css file.

b.

Delete the existing rwgray.css from REPORTS_TMP\docroot\css.
Note: REPORTS_TMP is an environment variable found in the registry entries.

c.

Restart Reports Builder.

 

Back to Topic List

Creating a Web Report

Using Oracle Reports, you can easily publish data from any source, in any format, with high fidelity to the Web. You have two options for creating a Web report.

You can design a Web page using your favorite HTML authoring tool, open the HTML page in Reports Builder, and add dynamic content to the page.

You can use one of the predefined HTML templates provided with Oracle Reports and use the Report Wizard to define the data model and Web layout.

In this tutorial, you will use a predefined HTML template and the Report Wizard to build a Web report and save it in JSP format.

 

Back to Topic List

1.

Start Oracle Reports by double-clicking the Reports Builder icon on your desktop. The welcome screen displays.

You have several choices for designing your report. For this tutorial, you will use the Report Wizard (the default).

Move your mouse over this icon to see the image

Click OK.

If Reports Builder is already open, create a new report by selecting File > New > Report from the menu.

Move your mouse over this icon to see the image

Select the option Use the Report Wizard. Click OK.

Move your mouse over this icon to see the image


2.

Click Next on the Report Wizard welcome page.

Move your mouse over this icon to see the image

 

3.

For this report, you will create a Web layout only. Select Create Web Layout only.

Move your mouse over this icon to see the image

Click Next.

 

4.

On the next page of the Report Wizard, you specify the style of the report that you want to build and can include a title for your report.

Select Group Above as the style and enter Managers Report as the title.

Move your mouse over this icon to see the image

Click Next.

 

5.

For the data source, accept SQL Query as the data source type by clicking Next.

Move your mouse over this icon to see the image

 

6.

On the data source definition page, click Connect to connect to the database.

In the Connect dialog box, enter HR for the User Name, HR for the Password, and the name of your database for the Database field.

Move your mouse over this icon to see the image

Click Connect.

 

7.

You can now type in a SQL statement, define a query visually using Query Builder, or import an existing file.

Enter the following SQL statement in the Data Source definition field: select * from employees

Move your mouse over this icon to see the image

Click Next.

 

8.

You specified that you wanted to create a Group Above report. The next page of the Report Wizard asks you to choose the field(s) by which you want to group the returned data. For this report, you will group employees by manager ID.

Select MANAGER_ID in the Available Fields list and click the [>] button. The field is now displayed on the right as the first level Group Field.

Move your mouse over this icon to see the image

Click Next.

 

9.

You now need to decide on the fields you want to display in the report. MANAGER_ID is already selected. From the Available Fields list, select EMPLOYEE_ID and click the [>] button. Repeat this process for FIRST_NAME , LAST_NAME , HIRE_DATE ,and SALARY fields.

Ensure that the displayed fields are in the order shown below. You can drag and drop a field to modify the order.

Move your mouse over this icon to see the image

Click Next.

You won't calculate totals for this report, so click Next to advance to the label and width panel of the wizard.

 

10.

Keep the default settings for the labels and widths.

Move your mouse over this icon to see the image

Click Next.

 

11.

As the final step in the Report Wizard, you can include a template in the report definition. Templates allow you to create a standard look and feel, and also promote reusability. In this tutorial, you will use one of the predefined templates for your report.

Select Gray from the Predefined template list and click Next.

Move your mouse over this icon to see the image

 

12.

Click Finish.

The source code for your Web report now displays in the Web Source view of the Report Editor. The source code includes HTML tags and JSP tags. You can edit the source code directly to add a report block, add a graph, and to customize your report.

Move your mouse over this icon to see the image

 

13.

Run the Web layout for your report. Click the Run Web Layout icon in the toolbar, or select Program > Run Web Layout from the menu.

The Web layout for your Managers Report displays in the default Web browser.

Move your mouse over this icon to see the image

 

14.

Save your report definition as ManagersReport_ <your_initials>. jsp .

To save a report definition, select the report module in the Object Navigator window and click the Save icon in the toolbar, or select File > Save from the menu. The first time you save your report definition, the Save dialog box displays, giving you the opportunity to enter a meaningful name for your report.

Move your mouse over this icon to see the image

Click Save.

 

Back to Topic List

Applying Conditional Formatting in a Web Report

In the next part of this tutorial, you will add conditional formatting logic to the JSP to highlight high salaries in the Managers Report. To do this, you will code a format trigger and reference the format trigger in the Web source.

Format triggers are PL/SQL functions that enable you to modify the display of objects dynamically at run time or to suppress their display altogether. Format triggers must return a Boolean value (TRUE or FALSE). Depending on whether the function returns TRUE or FALSE, the current instance of the object is included or excluded from the report output.

Back to Topic List

1.

Define a format trigger for this report.

In the Object Navigator, select the Program Units node.

Move your mouse over this icon to see the image

Click the Create icon  in the toolbar on the left side of the Object Navigator. The New Program Unit dialog box displays.

Move your mouse over this icon to see the image


2.

In the Name field, enter F_SALARYformatTrigger.

Select Function from the Types list.

Move your mouse over this icon to see the image

Click OK.

The PL/SQL Editor displays. The PL/SQL Editor supplies a template for the function block, providing you with the basic syntax that is required.

Move your mouse over this icon to see the image

 

3.

In this report, you want to display all the salaries greater than or equal to $10,000 in red, and those salaries between $4,999 and $10,000 in blue. For your convenience, the format trigger code to do this is provided for you in the file Format_Trigger.txt.

Open this file from your working directory using a text editor, such as Notepad or WordPad.

Move your mouse over this icon to see the image


Select all the code in this file by using Ctrl+A, and copy it by using Ctrl+C, or by selecting Edit > Copy from the menu. Return your focus to the PL/SQL Editor, and paste the code into the source pane using Ctrl+V, replacing the default template code.

Move your mouse over this icon to see the image

 

4.

Next, click the Compile button to compile the code. Be sure there are no errors. Successfully Compiled should be displayed in the lower right corner of the window.

Move your mouse over this icon to see the image

Close the PL/SQL Editor by clicking the Close button.

 

5.

Now that you have defined the format trigger, you have to reference it in the Web source for your report.

If you have closed the Web Source view of the Report Editor, you can reopen it by double-clicking the icon immediately to the left of the Web Source node in the Object Navigator.

Select Edit > Find and Replace from the menu.

In the Find what field, enter src=”SALARY”.

Move your mouse over this icon to see the image

Click Search.

 

6.

Now that you have located the SALARY field, you want to reference your format trigger.

Add the following code immediately after src=”SALARY”: formatTrigger="F_SALARYformatTrigger"

Move your mouse over this icon to see the image

 

7.

Run the Web layout again to test your format trigger.

Click the Run Web Layout icon , or select Program > Run Web Layout from the menu.

Move your mouse over this icon to see the image

Notice that the salaries are highlighted in the output.

Save the modifications you made to your report. Click the Save , or select File > Save from the menu.

 

Back to Topic List

 

Highlighting Alternate Rows in a Web Report

In this part of the tutorial, you will add Java code to your Web source to highlight alternate rows in the output.

 

1.

Return to the Web Source view of the Report Editor. If you have closed the Web Source view of the Report Editor, you can reopen it by double-clicking the icon immediately to the left of the Web Source node in the Object Navigator.

Place your cursor at the very beginning of the Web source code.

Move your mouse over this icon to see the image

Select Edit > Find and Replace from the menu.

In the Find what field, enter <rw:foreach.

Move your mouse over this icon to see the image

Click Search.

Move your mouse over this icon to see the image

The rw:foreach tag loops through the specified data source group. The tag creates a cursor for the data source group and moves the cursor to the next instance for each iteration.

 

2.

Right after this first occurrence of the <rw:foreach…> tag, you will add Java code to initialize a counter that you will use later to identify alternating rows. For your convenience, the code for this is provided for you in the file Alternate_Row_Color.txt

Open this file from your working directory using a text editor, such as Notepad or WordPad.

Note: This file contains three sections of code. Be sure you are working with code snippet 1.

<%
 int i = 0;
 String textClassName = null;
 String numberClassName = null;
%>
         

Select the code in this section of the file and copy it by using Ctrl+C, or by selecting Edit > Copy from the menu. Return your focus to the Web source code. Position your cursor at the end of the <rw:foreach…> tag, that is, after the closing bracket (>). Paste the code into the source pane by using Ctrl+V.

Move your mouse over this icon to see the image

 

3.

Now search for the next occurrence of the <rw:foreach…> tag.

Select Edit > Find and Replace from the menu.

The Find what field should still contain <rw:foreach. If it does not, enter <rw:foreach in the field.

Move your mouse over this icon to see the image

Click Search.

 

4.

Locate the <tr> tag that immediately follows this <rw:foreach…> tag.

Move your mouse over this icon to see the image

The <tr> tag defines a row in a table.

You will now add Java code to change the Cascading Style Sheet (CSS) class names for even and odd numbered rows. For even numbered rows, you will use new CSS class names, OraCellText1 and OraCellNumber1 .

Code snippet 2, in the file Alternate_Row_Color.txt, contains this code.

<%
 if (i % 2 == 0) {
   textClassName = "OraCellText1";
   numberClassName = "OraCellNumber1";
 } else {
   textClassName = "OraCellText";
   numberClassName = "OraCellNumber";
 }
 i++;
%>
         

Select the code in this section of the file and copy it by using Ctrl+C, or by selecting Edit > Copy from the menu. Return your focus to the PL/SQL Editor. Position your cursor after the <tr> tag and paste the code into the source pane by using Ctrl+V.

Move your mouse over this icon to see the image

 

5.

To complete the code modifications to achieve alternate color rows in your Web report, you have one more task. You will replace the static class names in the Web source with the variable class names you defined in the previous step. The class names will vary for even and odd numbered rows.

Search for the string src=”EMPLOYEE_ID”.

Select Edit > Find and Replace from the menu.

In the Find what field, enter src=”EMPLOYEE_ID”

Move your mouse over this icon to see the image

Click Search.

Move your mouse over this icon to see the image

 

6.

Staying in the same line of code as src=”EMPLOYEE_ID”, scroll slightly to the left and locate the text string class=”…” code, as well as in the four lines of code that follow.

Move your mouse over this icon to see the image

Make the following changes in these five lines of code:

Change from:

To:

class="OraCellNumber"

class="<%=numberClassName%>"

class="OraCellText"

class="<%=textClassName%>"

Move your mouse over this icon to see the image

 

7.

Run the Web layout of your report again to view the changes.

Click the Run Web Layout icon , or select Program > Run Web Layout from the menu.

Move your mouse over this icon to see the image

Notice the alternating row colors.

 

8.

Save the modifications you made to your report. Click the Save , or select File > Save from the menu.

 

Back to Topic List

In this lesson, you've learned how to:

Back to Topic List

Back to Topic List

Place the cursor over this icon to hide all screenshots.