2
Building a Report with Conditional Formatting

The report described in this chapter will help you learn more about Report Builder features for conditional formatting. You will build a single-query report that records company stock prices, and then displays values using conditional formatting when the price of a stock hits minimum and maximum values.

To build this report, you will use the Report Wizard to create the initial data model and report layout. You will make refinements to the report layout in the Live Previewer. You will also write PL/SQL, which is provided for you.

About conditional formatting

It is often useful to highlight certain parts of your report when particular conditions are met. For example, you can change the color of a value to red when it is greater than 5000. Report Builder provides two ways of adding conditional formatting to your report:

• The Conditional Formatting and Format Exception dialog boxes lets you specify multiple conditions and formatting attributes (font, text color, border, and fill color) for a selected layout object. You don't write any code with this method.

• The Format Trigger property allows you to code your own PL/SQL functions to perform conditional formatting. Because you write the code yourself, you have more flexibility and complete control when compared to using the Conditional Formatting and Format Exception dialog boxes alone.

For many conditions, you often can do what you need using the Conditional Formatting and Format Exception dialog boxes. For other conditions, you may want to use the dialog boxes to get started, and then modify the generated code by editing the Format Triggers.

The following figure illustrates the condition formatting features that you will apply to your report. Table 2-1, "Features demonstrated in this Conditional Formatting sample report", describes the steps you will take to create this report.

The cond.rdf file contains the report you will create after finishing the tasks in this chapter. You may want to refer to this file while you are working. This file is located in your ORACLE_HOME\TOOLS\DOC60\US\RBBR60 directory.

Table 2-1 Features demonstrated in this Conditional Formatting sample report

Feature	Location
Use the Report Wizard to define the SQL and create a first draft of the report.	Section 2.1, "Creating the initial report with the Report Wizard"
Modify the report layout for readability using the Live Previewer.	Section 2.2, "Making simple formatting modifications to the report"
Add a format exception to a field using the Conditional Formatting dialog box so that the appearance of the field changes when a specified condition is met.	Section 2.3, "Adding a formatting exception to a field"
Modifying the code generated by the Format Exception dialog box in order to do additional processing when a specified condition is met.	Section 2.4, "Adding more functionality to the generated format trigger code"
Add a formatting exception to a repeating frame in order to make a change that affects a number of report objects at once.	Section 2.5, "Adding a formatting exception to a repeating frame"
Modify code generated by the Format Exception dialog boxes to further customize the report.	Section 2.6, "Modifying the generated code"

To get started, open Report Builder. If the Welcome dialog box appears, click Use the Report Wizard and click OK. If not, choose File->New->Report. Click Use the Report Wizard and click OK.

At some point before you generate the report, you will need to log into the database. Choose File->Connect to connect to the database. Enter the appropriate log on information. See Section 1.3, "Obtaining database access before you start" for details.

2.1 Creating the initial report with the Report Wizard

The Report Wizard is a great way to start building a report. The Report Wizard alone may give you a report that satisfies your requirements. If it doesn't, you can use the Data Model view, the Live Previewer, and the Layout Model view to further refine your report. For this report, you will start with the Report Wizard, and then use the Live Previewer and Layout Model to modify the layout and add conditional formatting to the report. The steps in this section will help you to create the initial report.

For online help on this task, choose Help->Report Builder Help Topics On the Index page, type... report wizard, about Then click Display to view help topic... Report Wizard: Welcome page

1. If the Welcome page of the Report Wizard appears, click Next.

2. On the Style page, enter Stocks as the Title, and click Group Above as the report style.

   Tip: If you are unsure about what to do on any page of the wizard, click Help.

3. Click Next.

4. If the Type page appears, click SQL statement, and click Next. The Type page will appear only if you have configured Report Builder to run with Oracle Express.

5. On the Data page, ignore the buttons on the page and type in the following SELECT statement:

   SELECT symbol,
   
       company,
   
       current_price,
   
       trade_date,
   
       number_traded_today,
   
       todays_high,
   
       todays_low
   
   FROM stocks
   

6. Click Next.

7. On the Groups page in the Available Fields list, click the item symbol.
   

8. Click .

9. Repeat steps 7 and 8 for the company field.

10. For this report, you want one break level, but you currently have two - Level 1 and Level 2. To make one break level, click company in the Group Fields list, and drag it into Level 1.

    Tip: Groups are created to organize the columns in your report. When you create a query, Report Builder automatically creates a group that contains the columns selected by the query. You create additional groups to produce break levels in the report in order to create a group above or group left report.

11. Click Next.

12. On the Fields page, click . Doing this makes all fields display in the report.

13. Click Next.

14. On the Totals page, click current_price, and click Maximum.

15. Click Minimum. The minimum and maximum current_price fields display in the Totals list.

16. Click number_traded_today, and click Maximum.

17. Click Minimum. The minimum and maximum number_traded_today fields display in the Totals list.

18. Click Next.

19. On the Labels page, change the labels and widths as shown in the following table:

    Table 2-2 Labels and widths

    Column	Label	Width
    Todays High	Today's High	9
    Todays Low	Today's Low	9

20. Click Next.

21. On the Template page, click Predefined template if it is not already selected, and click Corporate 2 in the list box.

22. Click Finish. The report output automatically displays in the Live Previewer and should look similar to the following figure:
    

23. Choose File->Save As. Save the report in the directory of your choice, and name the report cond_21.rdf.

    Tip: It is good practice when you are designing your report to save it frequently under a different file name. If you generate an error or if you don't like some of the changes you made, you easily can go back to the previously saved file and make revisions from that point.

2.2 Making simple formatting modifications to the report

At this point, you could return to the Report Wizard and update any of its settings (for example, labels or widths) and regenerate the report. However, in this section, you will make a few edits in the Live Previewer to improve the appearance of the report.

For online help on this task, choose Help->Report Builder Help Topics On the Index page, type... format mask, applying to numeric object Then click Display to view help topic... Applying a format mask to a numeric object

1. In the Live Previewer, click the column of data below the Current Price heading. Report Builder selects every instance of the field. This means that any change you make at this point will be applied to all instances of the field.

2. Press and hold SHIFT. Then click the data under the Today's High and Today's Low headings. (You can select multiple columns by holding the SHIFT key while you click.)

3. Find the bold values at the end of each column. (You will need to scroll to the bottom of the first page). Shift-click on them. Find any other bold monetary values at the end of the report, and shift-click on them. Now all of the monetary values in the report are simultaneously selected, and you can easily format them all at once.

   Tip: If you accidentally select something and want to deselect it, shift-click on it again to deselect.

4. Click to add a dollar sign ($) to the selected values.

5. Click twice to add two decimal places.

6. Click in an empty area of the Live Previewer to deselect everything.

7. Click the data under the Number Traded Today heading to select it.

8. Click to add a comma to values that have more than three digits to the left of the decimal.

9. Click in an empty area of the Live Previewer to deselect everything. The report output should look similar to the following figure:
   

10. Save the report as cond_22.rdf.

Optional Exercise:

Explore the Live Previewer to see what other changes you can make.

2.3 Adding a formatting exception to a field

The Conditional Formatting dialog box is a great way to start applying formatting exceptions to your layout objects. (For example, you can make a field's value bold when it exceeds a specified maximum value). The Conditional Formatting dialog box alone may satisfy your requirements for creating a formatting exception.

Tip: When you use the Conditional Formatting dialog box, it generates a Format Trigger for the selected object. Consequently, once you directly edit a Format Trigger for an object, you should not go back and use the Conditional Formatting dialog box to make more modifications. If you do, you may lose some of the customized modifications that you made directly to the Format Trigger.

In this section, you will set conditional formatting for two summary fields, :Maxcurrent_pricePerSymbol and :Mincurrent_pricePerSymbol. Doing this will flag values if they are outside of the specified maximum and minimum points.

For online help on this task, choose Help->Report Builder Help Topics On the Index page, type... conditional formatting, applying to an object Then click Display to view help topic... Applying conditional formatting to an object

1. In the Live Previewer, click the data under the Current Price heading to select it.

   Tip: When you select data in the Live Previewer, the corresponding object is highlighted in the Object Navigator, and vice versa.

2. Choose Tools->Property Palette.

3. Under the General Layout node, find the Conditional Formatting property, and double-click the button next to it.

4. In the Conditional Formatting dialog box, click New to create a new format exception for the Current Price field object.

5. In the Format Exception dialog box, check the first check box if it is not already checked. Choose current_price from the first drop-down list of values if it is not already chosen.

6. Choose Equal from the second drop-down list of values that is next to current_price.

7. Type :Maxcurrent_pricePerSymbol in the third field next to Equal.

8. Check the check box in the second row. Note that the last drop-down list in the first row is activated and can be edited.

9. In the last drop-down list in the first row, choose OR.

10. Repeat steps 5 through 7 adding current_price, Equal, and :Mincurrent_pricePerSymbol to the second row.

11. Click Font, and choose Bold in the Font dialog box. Click OK to close the Font dialog box.

12. Click and choose red. The palette closes automatically.

13. Click and choose black. The palette closes automatically.

14. Click OK to close the Format Exception dialog box.

15. Click Apply. You will see the effect of your format exception in the Live Previewer. You should now see some values in bold with a red fill color and a black border. Looking at these values allows you to see when the price of a particular stock was at its minimum and maximum value.

16. In the Conditional Formatting dialog box, click OK. Or, click Edit to further modify your format exception.

17. Close the Property Palette. The report output should look similar to the following figure:
    

18. Save the report as cond_23.rdf.

Optional Exercises:

• Choose File->Generate to File->PDF. After you have created the PDF file, locate the file in Explorer and open it in the Acrobat Reader to see the report output.

• To see the format exception code that Report Builder has generated for you, go to the Object Navigator and expand the Program Units node for the report. Double-click the icon next to the Format Trigger to open it in the PL/SQL Editor, and view the code.

• If you want more practice, try implementing a format exception for the values under Number Traded Today that is similar to the one you just implemented for the current price. The report already contains all of the values you need to do this.

2.4 Adding more functionality to the generated format trigger code

In many cases, simply changing formatting attributes as done in Section 2.3, "Adding a formatting exception to a field" may suffice. However, you can also include more sophisticated processing.

The steps in this section will help you to design the report for PDF output and add bookmarks that indicate when stock prices reach a maximum point.

Because you have already generated some code with the Conditional Formatting dialog box, some of the logic you need is already in place.

For online help on this task, choose Help->Report Builder Help Topics On the Index page, type... format trigger, creating Then click Display to view help topic... Creating or editing a format trigger

1. In the Live Previewer, click the data under the Current Price heading.

2. Choose Tools->Property Palette.

3. Under the Advanced Layout node, find the Format Trigger property, and double-click the button next to it.

4. Modify the code so that it looks as follows. The new code that you need to add is in bold:

   function F_current_priceFormatTrigger return boolean is
   
   begin
   
     -- Automatically Generated from Report Builder.
   
     if ((:current_price = :Maxcurrent_pricePerSymbol) or
   
         (:current_price = :Mincurrent_pricePerSymbol)) 
   
     then
   
       srw.set_foreground_border_color('black');
   
       srw.set_border_pattern('solid');
   
       srw.set_foreground_fill_color('red');
   
       srw.set_fill_pattern('solid');
   
       srw.set_font_face('Arial');
   
       srw.set_font_size(10);
   
       srw.set_font_weight(srw.bold_weight);
   
       srw.set_font_style(srw.plain_style);
   
         if (:current_price = :Maxcurrent_pricePerSymbol)
   
         then
   
           srw.set_bookmark(:symbol || ' at maximum ');
   
         end if;	
   
     end if;
   
     return (TRUE);
   
   end;
   

5. Click Compile.

6. If any compilation errors occur, check the code for syntax errors, and recompile as needed.

7. Click Close.

8. Close the Property Palette.

   Tip: The next four steps require that the Live Previewer be the active window.

9. Choose View->Web Preview->Use PDF.

10. Choose View->Web Preview->Show All Pages.

11. Choose View->Web Preview->Generate to Web Browser. The PDF output for your report is now displayed in your Web browser. Test the bookmarks.

    Tip: You may be requested to locate the executable that launches your browser. Use the Browse button. Your browser must be configured to use the Acrobat Reader plug-in in order to view the PDF that Report Builder generated.

    If you do not have the Acrobat Reader plug-in configured for your browser, you can choose File->Generate to File->PDF, and open the PDF in the stand alone Acrobat Reader.

12. Go back into Report Builder and choose View->Web Preview->Generate to Web Browser. This turns off the Web Previewer.

    Tip: When you're making a lot of little changes to your report, it's best to turn off the Web preview feature. Otherwise, the Web preview is constantly updating each small change that you make.

13. Repeat steps 1 through 8. But this time, update the Format Trigger to look as follows. The new code that you need to add is in bold:

    function F_current_priceFormatTrigger return boolean is
    
    begin
    
      -- Automatically Generated from Report Builder.
    
      if ((:current_price = :Maxcurrent_pricePerSymbol) or
    
          (:current_price = :Mincurrent_pricePerSymbol)) 
    
      then
    
        srw.set_foreground_border_color('black');
    
        srw.set_border_pattern('solid');
    
        srw.set_foreground_fill_color('red');
    
        srw.set_fill_pattern('solid');
    
        srw.set_font_face('Arial');
    
        srw.set_font_size(10);
    
        srw.set_font_weight(srw.bold_weight);
    
        srw.set_font_style(srw.plain_style);
    
          if (:current_price = :Maxcurrent_pricePerSymbol)
    
          then
    
            srw.set_bookmark(:symbol || ' at maximum ');
    
          elsif (:current_price = :Mincurrent_pricePerSymbol)
    
            then 
    
              srw.set_bookmark(:symbol || ' at minimum ');
    
          end if;	
    
      end if;
    
      return (TRUE);
    

end;

14. Save the report as cond_24.rdf.

15. Click to view the results in the Live Previewer.

16. Repeat steps 9 through 12 to regenerate the PDF.

2.5 Adding a formatting exception to a repeating frame

Creating a formatting exception for a single field can be useful, but you can also create a formatting exception that affects a number of objects at once. Report Builder typically groups objects inside of frames or repeating frames. If the objects inside of a frame are transparent, changing the fill color of a repeating frame will effectively change the fill color of the objects inside of it.

In this section, you will change the fill color of an object in a repeating frame.

1. Go to the Data Model view.

2. Click .

3. Click the title bar of the group named G_symbol.

4. Double-click the newly created column named CS_1 to display the Property Palette. You may have to scroll down. Update the properties as follows:

   Table 2-3

   Name	Function	Source	Reset at
   CS_COUNT	Count	symbol	Report

5. Press ENTER or RETURN, or click any other field in the Property Palette to accept the changes.

6. Close the Property Palette.

7. In the Object Navigator, place your cursor in the Find field and type
   R_G_SYMBOL. Note that the search occurs as you type, so you will most likely be taken to the object before you finish typing the entire name.

8. Choose Tools->Property Palette.

9. In the Property Palette, under the General Layout node, locate the Conditional Formatting property, and double-click the button next to it.

10. Click New to create a new format exception for the field object.

11. Choose CS_COUNT from the first list of values.

12. Choose Equal from the list of values next to CS_COUNT.

13. Type 1 in the field next to Equal.

14. Click and choose a darker grey.

15. Click OK.

16. Repeat steps 10 through 14 using CS_COUNT, Equal, 0, and a light blue for the Fill Color.

17. Click OK in the Format Exception dialog box.

18. Click Apply and OK in the Conditional Formatting dialog box.

19. Check your output in the Live Previewer. Because there are non-transparent objects on top of the repeating frame, you only see the dark grey color at the very top of the first instance of the repeating frame. The light blue color never appears, but you will change that in Section 2.6, "Modifying the generated code"
    .

20. Save the report as cond_25.rdf.

2.6 Modifying the generated code

By making a simple modification to the code that was generated by the Conditional Formatting and Format Exception dialog boxes, you can make the fill color alternate for even and odd instances of the repeating frame.

1. In the Object Navigator, click the R_G_SYMBOL object.

2. Choose Tools->Property Palette.

3. In the Property Palette, under the Advanced Layout node, locate the Format Trigger property, and double-click the button next to it.

4. Modify the code so that it looks as follows. The new code that you need to add is in bold.

   Tip: Note that the color specifications in the following code may differ slightly from the colors specified in your code:

   function R_G_symbolFormatTrigger return boolean is
   
   begin
   
     -- Automatically Generated from Report Builder.
   
     if (:CS_count mod 2 = '1')
   
     then
   
       srw.set_foreground_fill_color('gray32');
   
       srw.set_fill_pattern('solid');
   
     end if;
   
   
   
     -- Automatically Generated from Report Builder.
   
     if (:CS_count mod 2 = '0')
   
     then
   
       srw.set_foreground_fill_color('r50g50b100');
   
       srw.set_fill_pattern('solid');
   
     end if;
   
   
   
     return (TRUE);
   
   end;
   

5. Click Compile.

6. If any compilation errors occur, check the code for syntax errors, and recompile as needed.

7. Click Close.
   

8. Click to view the results in the Live Previewer. Note as you scroll through the report that the headings alternate between dark grey on the odd pages and light blue on the even pages.

9. Save the report as cond_26.rdf.

Optional Exercise:

Make the opaque objects on top of R_G_SYMBOL transparent so that you can see its fill color through the other objects.

Tip: Use the Object Navigator to choose the frames contained in the R_G_SYMBOL object.

2.7 Summary

Congratulations! You have finished the Conditional Formatting sample report. You now know how to:

• Use the Report Wizard to define a data model and layout.

• Use the Live Previewer to modify a report layout.

• Use the Conditional Formatting dialog box to add a format exception to a field.

• Modify code generated by the Conditional Formatting dialog box.

• Add formatting exceptions to a repeating frame.

• Modify code generated by the Format Exception dialog box.

For more information about conditional formatting, see the online help:

For online help on this topic, choose Help->Report Builder Help Topics On the Index page, type... conditional formatting, about Then click Display to view help topic... About conditional formatting