Applying Conditional Formatting in a Paper Report

Purpose

In this tutorial, you learn how to apply formatting attributes to data in a report that satisfies a particular business requirement or rule.

Time to Complete

Approximately 1 hour

Topics

This tutorial covers the following topics:

	Overview
	Scenario
	Prerequisites
	Creating a Master-Detail Report
	Highlighting Data by Applying Conditional Formatting
	Comparing Data by Applying Conditional Formatting
	Generating Report Output to a PDF File
	Summary
	Related information

Viewing Screenshots

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

It is often desirable to draw attention to portions of a report when certain business rules or criteria are met. Using the Conditional Formatting dialog box in Reports Builder, you can create a format trigger to change the appearance of retrieved data in your report based on the factors, or conditions, you define.

Back to Topic List

Scenario

In this tutorial, you will create a sales report using the Report Wizard. You will apply conditional formatting to highlight sales promotions that generated high revenues for the company. You will also use conditional formatting to compare the performance of Internet and telesales channels.

Back to Topic List

Prerequisites

Before starting this tutorial, you should:

Back to Topic List

Creating a Master-Detail Report

You can use the Report Wizard to build eight styles of reports. In this part of the tutorial, you create a master-detail report.

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). Click OK. If Reports Builder is already open, create a new report by selecting File > New > Report from the menu. Select the option Use the Report Wizard. Click OK.
2.	Click Next on the Report Wizard welcome page.
3.	The default option is Create both Web and Paper Layout. Click Next to accept this option.
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 Sales Report as the title. Click Next.
5.	For the data source, accept SQL Query as the data source type by clicking Next.
6.	On the data source definition page, click Connect to connect to the database. In the Connect dialog box, enter SH for the User Name, SH for the Password, and the name of your database for the Database field. Click Connect. Note: If you were previously working in this Reports Builder session using a different schema, you will have to connect to the SH schema in order to complete this tutorial.
7.	You can now type in a SQL statement, define a query visually using Query Builder, or import an existing file. For your convenience, a SQL query is provided for you. Click Import Query. In the file import dialog box, click Conditional_Formatting.sql located in your working directory. Click Open. The query fetches sales history data from the database. Now that the query has been defined, click Next to continue.
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 sales data by product category. Select PROD_CATEGORY in the Available Fields list and click the [>] button. The field is now displayed on the right as the first level Group Field. Click Next.
9.	You now need to decide on the fields you want to display in the report. PROD_CATEGORY is already selected. From the Available Fields list, select PROD_NAME and click the [>] button. Repeat this process for PROMO_NAME, CHANNEL_DESC, CUST_LAST_NAME, QUANTITY_SOLD, and AMOUNT_SOLD fields. Ensure that the displayed fields are in the order shown below. You can drag and drop a field to modify the order. Click Next.
10.	You won't calculate totals for this report, so click Next to advance to the label and width panel of the wizard. Modify the width settings as follows: Field Width QUANTITY_SOLD 2 AMOUNT_SOLD 5 CUST_LAST_NAME 8 PROMO_NAME 12 CHANNEL_DESC 8 PROD_NAME 14 PROD_CATEGORY 14 Click Next.
11.	Keep the default setting for the template. Click Finish to complete the definition of your report. You now view the paper layout for your report in the Paper Design view of the Report Editor. Note: Some of the data in the image above was formatted using the WYSIWYG features of the Paper Design view of the Report Editor. To learn more about this, see the tutorial Creating a Report Using the Report Wizard.
12.	Save your report as SalesReport_< your_initials >.jsp. To save a report definition, 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. Click Save.

Back to Topic List

Highlighting Data by Applying Conditional Formatting

You may need to emphasize, or draw attention to, data in a report that satisfies a particular business requirement or rule. You can highlight or suppress objects in a report by applying conditional formatting.

Back to Topic List

1.	In the Paper Design view, select the PROD_NAME column by clicking the data in the column. Now click the Select Parent Frame icon on the toolbar  to select the repeating frame that contains the PROD_NAME field.
2.	Now you will define an exception, or rule, to highlight the data that formats within the repeating frame. Select Format > Conditional Formatting from the menu to invoke the Conditional Formatting dialog box.
3.	The purpose of this report is to analyze the sales promotions. The first format exception you will define will hide the rows where no promotion was involved. Click New to define a new exception. In the Format Exception dialog box, click on the drop-down list of columns and select PROMO_NAME . In the condition drop-down list, select Null. In the Format section of the dialog box, select the Hide the Object check box. Click OK.
4.	Now you will define another exception on the same repeating frame that will highlight the promotions that generated revenue greater than $3000. Click New to define a new exception. Ensure that the column AMOUNT_SOLD is selected in the column drop-down list. Since the columns are displayed alphabetically, this should be the first column in the list. Select Greater Than from the condition drop-down list. Enter 3000 in the next field to complete the definition of the exception.
5.	Next, choose the display attributes for rows meeting this exception. Let's highlight these rows by displaying them with a green background, or fill color. In the Format section of the dialog box, click the Fill Color icon to display the color palette. Choose a fill color of green. Click OK . Click OK again to apply the exceptions and formatting selections and exit the Conditional Formatting dialog box.
6.	The Sales Report is refreshed in the Paper Design view of the Report Editor. Navigate through the pages of your report. Hint: The first highlighting appears on page 16 of the report. Notice that the sales promotions that generated revenues greater than $3000 are highlighted in green. Save the modifications you made to your report. Click the Save icon in the toolbar, or select File > Save from the menu.

Back to Topic List

Comparing Data by Applying Conditional Formatting

In this part of the tutorial, you will apply conditional formatting to the Sales Report to compare the performance of Internet sales and telesales channels.

1.	Ensure that your report is displayed in the Paper Design view of the Report Editor. Select the CHANNEL_DESC column by clicking the data in the column. Keeping the CHANNEL_DESC column selected, right-mouse-click and select Conditional Formatting from the pop-up menu. You want define a format exception that highlights those Internet channel promotions what generated less than $100 in revenue. In order to do this, you will need to define two exceptions. Click New.  The Format Exception dialog box displays.
2.	Click on the drop-down list of columns and select CHANNEL_DESC . Select Equal from the condition drop-down list. Enter ‘Internet' in the next field to complete the definition of the exception. Be sure to include the single quotation marks.
3.	Now define the second exception. Select the check box at the beginning of the second row of the Format Exception dialog box. Click on the drop-down list of columns and select AMOUNT_SOLD . Select Less Than from the condition drop-down list. Enter 100 in the text field to complete the definition of the exception.
4.	Choose the display attributes for rows meeting this exception. Highlight these rows by displaying the data in the Channel_Desc column with a text color of red. In the Format section of the dialog box, click the Text Color icon to display the color palette. Choose a text color of red. Click OK to return to the Conditional Formatting dialog box.
5.	Next, you will define the same format exception for the Telesales channel, but will specify a text color of blue. Click New. The Format Exception dialog box displays. Click on the drop-down list of columns and select CHANNEL_DESC. Select Equal from the condition drop-down list. Enter ‘Tele Sales' in the next field to complete the definition of the exception. Be sure to include the single quotation marks.
6.	Now define the second exception. Select the check box at the beginning of the second row of the Format Exception dialog box. Click on the drop-down list of columns and select AMOUNT_SOLD . Select Less Than from the condition drop-down list. Enter 100 in the text field to complete the definition of the exception.
7.	Choose the display attributes for rows meeting this exception. Highlight these rows by displaying the data in the Channel_Desc column with a text color of blue. In the Format section of the dialog box, click the Text Color icon to display the color palette. Choose a text color of blue.
8.	Click OK. Click OK again to apply the exceptions and formatting selections and exit the Conditional Formatting dialog box. The Sales Report is refreshed in the Paper Design view of the Report Editor. Navigate through the pages of your report. Notice that the Internet promotions that generated revenues less than $100 are highlighted in red, while the telesales promotions that generated similar revenues are highlighted in blue. Save the modifications you've made to your report. Click Save in the toolbar, or select File > Save from the menu.
9.	So that the corresponding revenues for Internet and telesales promotions display in red and blue, respectively, define the same format exceptions on the AMOUNT_SOLD column. Ensure that your report is displayed in the Paper Design view of the Report Editor. Select the AMOUNT_SOLD column by clicking on data in the column. Keeping the AMOUNT_SOLD column selected, right-mouse-click and select Conditional Formatting from the pop-up menu. Click New.  The Format Exception dialog box displays.
10.	Click on the drop-down list of columns and select CHANNEL_DESC . Select Equal from the condition drop-down list. Enter ‘Internet' in the next field to complete the definition of the exception. Be sure to include the single quotation marks.
11.	Now define the second exception. Select the check box at the beginning of the second row of the Format Exception dialog box. Click on the drop-down list of columns and select AMOUNT_SOLD. Select Less Than from the condition drop-down list. Enter 100 in the text field to complete the definition of the exception.
12.	Choose the display attributes for rows meeting this exception. Highlight these rows by displaying the data in the Amount_Sold column with a text color of red. In the Format section of the dialog box, click the Text Color icon to display the color palette. Choose a text color of red. Click OK to return to the Conditional Formatting dialog box.
13.	Define the same format exception for the telesales channel, but specify a text color of blue. Click New. The Format Exception dialog box displays. Click on the drop-down list of columns and select CHANNEL_DESC. Select Equal from the condition drop-down list. Enter ‘Tele Sales' in the next field to complete the definition of the exception. Be sure to include the single quotation marks.
14.	Now define the second exception. Select the check box at the beginning of the second row of the Format Exception dialog box. Click on the drop-down list of columns and select AMOUNT_SOLD. Select Less Than from the condition drop-down list. Enter 100 in the text field to complete the definition of the exception.
15.	Choose the display attributes for rows meeting this exception. Highlight these rows by displaying the data in the AMOUNT_SOLD column with a text color of blue. In the Format section of the dialog box, click the Text Color icon to display the color palette. Choose a text color of blue.
16.	Click OK. Click OK again to apply the exceptions and formatting selections and exit the Conditional Formatting dialog box. The Sales Report is refreshed in the Paper Design view of the Report Editor. Hint: Navigate back to the beginning of your report. As a result of conditional formatting, you can easily compare the Internet and telesales channel promotions that generated less than $100. Close the Report Editor window. Save your report by clicking Save in the toolbar.

Back to Topic List

Generating Report Output to a PDF File

Reports Builder can generate report output to a PDF file, containing the formatted data and all objects. When you generate your report output to a PDF file, you can distribute the output to any PDF destination, including e-mail, printer, OracleAS Portal, and Web browser.

1.	Select the report in the Object Navigator.
2.	Select File > Generate to File > PDF from the menu. Save your report output as SalesReport_< your initials >.pdf , such as SalesReport_jlt.pdf.

Back to Topic List

Summary

In this lesson, you've learned how to:

	Create a master-detail report
	Apply conditional formatting to highlight data in a report
	Apply conditional formatting to compare data in a report
	Generate report output to a PDF file

Back to Topic List

Related Information

	To learn more about Oracle Reports 10g, refer to the additional Reports OBEs.
	Visit the OTN Web site for technical papers, presentations, frequently asked questions, demonstrations, and more.

Back to Topic List

Place the cursor over this icon to hide all screenshots.