23
Building a Report with Conditional Highlighting

Figure 23-1 Conditional highlighting report output

Text description of the illustration condhigh_fin.gif

The report you will build in this chapter demonstrates how to highlight data in your report. In this report that shows employee salaries, salaries that are greater than or equal to 10,000 are displayed in bold and in red color, and values that are between 4,999 and 10,000 are displayed in bold. Using the Conditional Formatting dialog box in Reports Builder, you can create a format trigger that will change the appearance of retrieved data depending on factors you define.

You can use the Conditional Formatting dialog box to create this format trigger, or you can manually create them using the PL/SQL Editor. The steps in this example will show you how to use the dialog box, then display the code in the PL/SQL Editor to see how the format trigger was automatically generated by Reports Builder.

Concepts

• With conditional highlighting, you can format specified portions of a report's output when certain criteria are met.

Data Relationships

• This report uses one query to fetch all data.

Layout

• This report uses a tabular layout style. To add conditional highlighting, you will use the Conditional Formatting dialog box to determine which names and salaries will be highlighted in the report output.

Example Scenario

In this example, suppose you want to create a report for managers that shows a complete list of employees in the company, but also highlights employee salaries that are greater than 10,000. You also need to indicated which employees' salaries are between 4,999 and 10,000. In this example, you will use conditional formatting to highlight these figures in bold and red text.

To see a sample report that uses conditional highlighting, open the examples folder named condhigh, then open the Oracle Reports example report named condhigh.rdf. For details on how to open it, see "Accessing the example reports" in the Preface.

Table 23-1 Features demonstrated in this example

Feature	Location
Use the Report Wizard to create a simple tabular report based on a single query.	Section 23.2, "Create a basic tabular report"
Use the Conditional Formatting dialog box to highlight specific data in your report.	Section 23.3, "Add conditional formatting to the report"
Open the PL/SQL Editor for the format trigger that was generated by Reports Builder.	Section 23.4, "Examine the conditional format trigger code"

23.1 Prerequisites for this example

To build the example in this chapter, you must have access to the Human Resources portion of the sample schema, which is provided by default with the Oracle9i database. See your database administrator for more information.

23.2 Create a basic tabular report

The steps in this section will show you how to use the Report Wizard to build a simple tabular report.

To create a tabular report:

1. Launch Reports Builder (or, if already open, choose File > New > Report)

2. In the Welcome or New Report dialog box, select Use the Report Wizard, then click OK.

3. If the Welcome page displays, click Next.

4. On the Report Type page, select Create Paper Layout Only, then click Next.

5. On the Style page, type a Title for your report, select Tabular, then click Next.

6. On the Data Source page, click SQL Query, then click Next.

7. On the Data page, enter the following SELECT statement in the Data Source definition field:

   SELECT ALL EMPLOYEES.FIRST_NAME, EMPLOYEES.LAST_NAME,
   EMPLOYEES.EMPLOYEE_ID, EMPLOYEES.SALARY
   FROM HR.EMPLOYEES
   

Note: You can enter this query in any of the following ways: Copy and paste the code from the provided text file called condhigh_code.txt into the Data Source definition field. Click Query Builder to build the query without entering any code manually. Type the code in the Data Source definition field.

8. Click Next.

   Note: If you are not already connected to a database, you will be prompted to connect to the database when you click Next. Ensure that you connect to a database that has the appropriate schema for this example. Section 23.1, "Prerequisites for this example" describes the sample schema requirements for this example.

9. On the Fields page, click the right arrow (>) to move the following fields to the Displayed Fields list, then click Next. Make sure you move them in the following order:
   • EMPLOYEE_ID

   • FIRST_NAME

   • LAST_NAME

   • SALARY

10. On the Totals page, click Next.

11. On the Labels page, click Next.

12. On the Template page, select Predefined Template and click Beige, then click Finish to preview your report output in the Paper Design view. It should look something like this:

Figure 23-2 Paper Design view for the initial tabular report

Text description of the illustration condhigh_prev.gif

13. Now, let's format the data to make it more meaningful. In the Paper Design view, select the Salary column by clicking once on the column of data.

14. In the toolbar, click the Currency button, then the Commas button, and finally click the Add Decimal button twice.

    The Salary column of your report should now look like this:

Figure 23-3 Formatted salary column

Text description of the illustration condhigh_sal.gif

15. Change the alignment of your columns by doing the following:
    1. Click the Salary column heading, then click the Align Center button in the toolbar.

    2. Click the Salary column once, then click the Align Right button in the toolbar.

    3. While the Salary column is selected, click and drag one of the right black squares to size the column.

    4. Click the Employee Id column heading, then click the Align Center button in the toolbar.

    5. Click the Employee Id column, then click the Align Center button in the toolbar.

16. The Paper Design view of your report should now look like this:

    Figure 23-4 Paper Design view of the tabular report

    

    Text description of the illustration condhigh_aln.gif

    17. Save your report as condhigh_<your initials>.rdf.

    23.3 Add conditional formatting to the report

    The steps in this section will show you how to add conditional formatting so that salaries higher than 10,000 will be highlighted in bold, red text, and salaries between 4,999 and 10,000 will be highlighted in bold text.

    Since the data retrieved can not be both higher than 10,000 and between 4,999 and 10,000, you will need to create two separate format exceptions. In this section, you will see how to create each distinct format exception.

    To add conditional formatting:

    1. In the Paper Design view, right-click the Salary column of data (not the Salary column heading), then choose Conditional Formatting.

    2. In the Conditional Formatting dialog box, click New to create a new Format Exception.

    3. In the Format Exception dialog box, make sure SALARY is selected.

    4. Next to SALARY, choose Greater Than or Equal from the drop-down list.

    5. In the next box, type 10000.

    6. Under Format, click the icon next to Text Color to display the color palette.

       Note: You can choose as any options as you want in the Format Exception dialog box, such as text color, style, and font.

7. Click Red.

8. Click Font, then choose Bold.

9. Click OK to accept the new font style.

   You should now see the following options selected in the Format Exception dialog box:

Figure 23-5 Format Exception dialog box

Text description of the illustration condhigh_fe1.gif

10. Click OK.

11. In the Conditional Formatting dialog box, click New to create your second format exception.

12. Create a format exception where the values of the SALARY column between 4999 and 10000 are highlighted in bold.

    Note: Make sure you type the values in the order described, so that Reports Builder knows to highlight the data between 4999 and 10000, and not 10000 and 4999.

13. When you're done, click OK. The Conditional Formatting dialog box should now look like the following:

Figure 23-6 Conditional Formatting dialog box

Text description of the illustration condhigh_db.gif

14. Click Apply, then click OK. Your report displays in the Paper Design view, and should now look something like this:

Figure 23-7 FInal Conditional Formatting report

Text description of the illustration condhigh_fin.gif

15. Save your report as condhigh_<your initials>.rdf.

23.4 Examine the conditional format trigger code

The steps in this section will show you the PL/SQL code that was automatically generated by Reports Builder when you used the Conditional Formatting dialog box to set up your format exceptions.

Note: You can also edit the conditional format trigger code in the PL/SQL Editor, but if you attempt to modify the code again in the Conditional Formatting dialog box, your edits will be overwritten by the selections in the dialog box. If you do modify the code in the PL/SQL Editor, you will see a warning note when you try to open the Conditional Formatting dialog box.

To examine the automatically generated code:

1. In the Paper Design view, right-click the Salary column (on which you just applied formatting), then choose PL/SQL Editor.

2. In the PL/SQL Editor, you will see the following code that was automatically generated by Reports Builder.

Figure 23-8 PL/SQL code for the new format triggers

Text description of the illustration condhigh_plsql.gif

Note: In this code, you can see that the two format exceptions you created comprise two parts of a format trigger. You needed to create two separate format exceptions in the Conditional Formatting dialog box to achieve this effect. If you had tried to create both exceptions simultaneously in the same Format Exception dialog box, your data would not have satisfied both exceptions, and thus would not have been highlighted.

23.5 Summary

Congratulations! You have successfully built a report that highlights specified data in the report output. You now know how to:

• use the Report Wizard to create a simple tabular report

• format the appearance of your report using tools in the Paper Design view

• use the Conditional Formatting dialog box and the Format Exception dialog box to create format triggers that highlight certain data in your report output

• examine the code automatically generated by Reports Builder

For more information on any of the wizards, views, or properties used in this example, refer to the Reports Builder online help, which you can access in two ways:

• From the Oracle Technology Network (http://otn.oracle.com/products/reports/), click Documentation and navigate to the Reports Builder Online Help for the most recent, hosted online help.

• From Reports Builder, choose Help > Help Contents.

  Note: You can replace your Reports Builder online help with the most recent update by downloading the latest online help set available on the Oracle Technology Network (http://otn.oracle.com/docs/products/reports/). Instructions for replacing your help file are included in the readme.txt in the download file.