• 34
• Oracle Graphics 2.5
• The Oracle Graphics Environment
• The Layout Editor
• Initializing the Graphics Environment
• Creating a New Display
• Defining Displays
• Creating a Pie Chart
• Creating a Columnar Chart
• Other Chart Types
• Defining Parameters for Charts
• Multiple Chart Applications
• Creating a Drill-Down Chart
• Creating a Multilayer Chart
• Formatting Charts with Text and Breaks
• Adding Text Items to Charts
• Customizing the Chart Labels
• Creating a Break Chart
• Advanced Formatting Techniques
• Using Format Triggers
• Creating Data Filters
• Summary

34

Oracle Graphics 2.5

An old adage states that "A picture paints a thousand words." This adage is certainly evident in many executive information systems. By being able to view database trends graphically, an Oracle database user is better able to make decisions based on this data. Oracle Graphics 2.5 is used to graphically display database information.

You can run Oracle Graphics 2.5 to display data charts from the Oracle database; however, this tool is used primarily as a graphical server that can present Oracle data within other compatible applications such as Oracle Forms or Oracle Reports. This chapter explains how to create graphs and charts of Oracle data. These charts will encompass many of the available chart types and techniques that can be used to create highly useful displays of graphical data.

The Oracle Graphics Environment

Like Oracle Forms and Oracle Reports, Oracle Graphics is designed to operate in a graphical user interface (GUI) environment such as Microsoft Windows. You can perform functions either by selecting them from a menu or by clicking an iconic button. The Oracle Graphics Designer consists of two primary components: The Object Navigator (described in Chapter 31) and the Layout Editor.

The Layout Editor

The Layout Editor (shown in Figure 34.1) presents a canvas that will contain the charts as they are created. You operate this tool by using iconic buttons and menu picks. You use the buttons along the left side of the Layout Editor window to create and manipulate the chart layout objects. These buttons (top to bottom, left to right) are described in Table 34.1.

Figure 34.1. Oracle Graphics Layout Editor.

Table 34.1. Layout Editor buttons.

Button

Usage

Below these iconic buttons there is a square that displays the current visual attribute for the selected item (or the default, if no object is selected). This display indicates the fill color, line color, and text color for the object. Below the display is a series of three palette controls that you use to adjust the visual attribute. The fourth button in this area defines the current symbol to be used for charts that display a symbol.

At the top of the Layout Editor window is another series of iconic buttons that are used for module control. The first two buttons open an existing chart and save the current chart. The next button is used to start up the runtime simulator to test the operation of the graphics module as it would operate in production. The last three buttons are used to cut, copy, and paste layout objects between the display and the Windows clipboard.

Initializing the Graphics Environment

To utilize the features of Oracle Graphics and to ensure compatibility with other tools, Oracle Graphics enables you to initialize the designer environment based on a set of personal (and possibly departmental) preferences. To configure Oracle Graphics, select the Tools Options dialog box by selecting Tools | Tools Options from the menu.

The Oracle Graphics 2.5 options are defined in two tabs. The first tab, Defaults, defines the parameters for charts that will be created in the Graphics Designer; the first field defines the message that will appear on the charts. This dialog box is shown in Figure 34.2.

Figure 34.2. The Oracle Graphics default options dialog box.

In the next field in the Defaults tab, you set the color palette file to be used for the chart. (The example shown uses Oracle's sixteen-color palette, which is the recommended setting for most applications.) You use Color Palette Mode to define how color palettes should be handled in Oracle Graphics. The options for the list box are Editable, Read Only - Shared, and Read Only - Private. Editable means that the color palette of the active chart will replace the system color palette. This shows the active chart accurately while the appearance of any inactive charts might not be accurate. Read Only - Shared means that each chart's color palette will be appended to the system palette until the space reserved for the palette becomes full. If any charts that use a different color palette are then opened, they might not appear accurately. Finally, Read Only - Private operates the same as shared mode except that Graphics ensures that the palette used is always valid for the active chart; any inactive charts might not appear correctly because their color palettes have been cleared to make room for the new chart.

The Date and Number buttons are used to establish format masks for dates and numeric fields in Oracle Graphics. You can then use these masks throughout the designer to display data.

The last set of fields on the Defaults tab define the default page width and height and the unit of measure for the chart.

The second tab in the Tools Options dialog box is used to define the user preferences in the designer (see Figure 34.3). You can set the Startup options to automatically create a new blank display, prompt for an existing display, or do nothing whenever the Graphics Designer is started. If the checkbox in this block is selected, Oracle Graphics Designer will prompt you to log in to the database whenever the Designer is started.

Figure 34.3. The Oracle Graphics Preferences dialog box.

Under the General Options box, the Sound Output option is based on the sound drivers that have been installed in the operating environment. (These drivers are installed in the Control Panel for Microsoft Windows 3.1.) Image Compression determines how graphical objects will be stored in the graphics file. If you select No Compression, the resulting file will be larger; however, when you select Compression, the application will run slower as the image is decompressed.

The last six options in the Preferences tab define the way Oracle Graphics will operate in the Designer and during runtime operation. Checking Automatic Character Set Conversion causes Oracle Graphics to automatically convert the character set used by Oracle Graphics to the default set based on the National Language Support (NLS) setting for the user. You would need to select this option when you develop an application for international use. Smooth Redraw at Runtime instructs Graphics to redraw any "damaged" screen areas in an internal memory buffer before the screen is updated. This option can reduce flickering and should be used unless there are memory problems.

The Keep Backup File option makes Oracle Graphics copy the Graphics file to a backup (extension .bak) before saving the new version. The last two options, Apply Properties on Navigation and Apply Query Properties on Execution, instruct Oracle Graphics to apply any changes to the display whenever the developer navigates out of the object. Otherwise, the developer will be prompted to apply the changes before navigation is allowed.

Creating a New Display

As stated in the previous section, you can configure Oracle Graphics to automatically open a new blank display upon startup. However, if you need to create a new display after startup, you can do so by selecting File | New | Display from the Designer menu or using the Ctrl+N hot key. A new blank display will be created in the Object Navigator according to the preferences defined in the Tools Options dialog box.

Defining Displays

A display is the basic modular unit for Oracle Graphics. Within a display, a chart or set of charts comprises the functional module that makes up an Oracle Graphics application unit.

Creating a Pie Chart

A pie chart presents data from several sources to indicate the portion that each contributes to the total for all sources. As shown in Figure 34.4, the Warehouse Shipments Chart shows the relative percentage of shipments for a single warehouse for a single year.

Figure 34.4. The warehouse shipments pie chart.

You can create a new chart in Oracle Graphics using either a one-step or two-step method. To use the one-step method, first click the mouse within the Layout Editor canvas to activate the editor and then select the Chart | Create Chart from the menu. A query dialog box will appear that will enable you to name and create a new query to select data for the pie chart. Using this box, create the query, Q_performance, as shown in Figure 34.5.

Figure 34.5. Creating a new chart query.

After you create the query, the developer can test it by clicking the Execute button at the bottom of the dialog box. (Note: You can use the Connect button to connect to the database if you have not yet done so.) The results of the query are shown in the second tab of the New Query dialog box. When you are satisfied with the query, click OK to view the Chart Properties dialog box.

The Chart Properties dialog box (shown in Figure 34.6) is used to define how the data from the query will be laid out on the presentation canvas. To create the pie chart, enter the chart name and title as shown. Define the format for the chart by selecting the type and subtype icon for the chart you want. For this chart, first select the pie chart type (second row, left), and then select the pie chart with depth subtype (far right). The format you select will be stored as a template that you can name within the display for any additional charts that might be needed (the default is template#).

Figure 34.6. The Chart properties sheet.

After the chart layout is completed, click OK to display the chart on the Layout Editor canvas. (You might have to drag the chart from the edge of the canvas to view the entire chart.) At this point, you can save the chart to the file system (or database) by selecting File | Save As from the menu. Save this chart as g34oun01.odg.

Creating a Columnar Chart

A columnar chart displays related data in a series of vertical filled bars. As shown in Figure 34.7, the Monthly Shipments chart displays the number of orders shipped each month for a warehouse.

Figure 34.7. The Monthly Shipments columnar chart.

To create this chart using the two-step method, click the mouse within the Object Navigator and select the Queries category. Using the iconic buttons at the left of the Object Navigator, click the Add Object icon to create a new query. In the first tab of the Query Properties dialog box, define the new query with the name Q_mly_ships as:

select to_char ( to_date (to_char (hist_month_no), 'MM'), 'MON'),

          hist_ord_shipped

     from warehouse_history

     where hist_year = 1994

     and hist_wh_code = 'WNJ'

   order by hist_month_no

To build the columnar chart, activate the Layout Editor and select the Chart tool from the Graphics tool palette. Using this tool, drag a region for the chart on the layout canvas. This action will cause the Chart Genie dialog box to appear, enabling you to define the data source for the new chart, as shown in Figure 34.8. For this example, select the Existing Query radio button and choose the query, Q_mly_ships, that was previously defined.

Figure 34.8. Assigning a query for a new chart.

Define the layout for the chart using the Chart Properties dialog box as shown by selecting the Columnar Chart type (top row, left) and the Columnar Chart with Shadow subtype (top row, right). When you are satisfied with the chart, save it as g34oun02.ogd.

Other Chart Types

Oracle Graphics 2.5 provides the functionality to create ten types of charts, with further subtype delineation, to define a total of 56 different chart formats. The first chart type is the column chart that was shown in the previous section. In its most basic form, a simple column chart consists of a series of filled rectangles indicating a value based on the height of the bar. Oracle Graphics has variations of the columnar chart for charts that present data that can be grouped in subcategories. (An example would be shipments for all warehouses for each month.) These variances include columns with stacked bars (the individual bars are stacked on top of each other to indicate the totals for the category.), columns with overlapping bars (the individual bars overlap within the group.), and columns with percent scaling (each segment shows the percentage that each bar contributes to the total 100 percent).

The column with baseline at zero is a chart subtype for negative data values. A column chart with shadows displays the bars in a three-dimensional background—the bar shadow shows behind the bars. A column chart with depth presents each bar as a 3-D object in a multidimensional background. The last column chart subtype, column with connecting lines, displays the bars with a solid line connecting the tops of each bar.

The next chart type is the bar chart. This chart displays the data in a series of horizontal bars. The same variations are available for the bar chart as in the case of the column chart.

The next set of chart formats is the line chart and its variations. These chart subtypes can be classified in three major subcategories.

1. Line—straight lines connecting the individual data points
   
   
2. Step—a horizontal line is drawn at the data point and vertical lines connect the individual point values
   
   
3. Curve—a smooth curve is drawn through the data points
   
   

Within each of these subcategories, you can choose to show symbols at each data point. You also can show multiple data entities as additive values on the same chart as individual stacked lines or as stacked lines with a solid fill between the line and the horizontal axis or prior line.

The next chart type enables you to create a chart that shows two different data values as a mixture of columns and lines. Typically, this report will indicate discreet data values on each column and the line would be used to indicate summary or trend data. The standard mixed chart presents a simple column chart with a straight line chart on the same grid. You can vary the way this chart type appears by using either a straight or curved line with or without fill.

The last chart type on the top line is called the Double-Y chart. This chart provides a y axis on both sides of the chart. Column chart variations are either a simple column or a column with overlapping bars. Line chart variances enable you to use a simple line or a line chart with symbols.

The next chart type is the pie chart, which was presented as the first chart type created. You can create either a pie chart with a shadow below it or one with depth, which appears as a 3-D object. The table chart presents the data in a tabular format with the same optional effects that exist for the pie chart.

The next type is the scatter chart, which displays data on the chart as individual points in a two-dimensional coordinate system. Typically, you can use this chart type in engineering applications to display scientific data points. Options enable you to use a regression line that approximates a straight line formula for the data. You can include a grid in the chart to facilitate reading the data values. The chart can also be displayed using a logarithmic y-axis or with both axes using a logarithmic scale. Finally, you can draw the chart with lines connecting the individual data points in the order that they are retrieved.

The next chart type, the high-low chart, is typically used to analyze data values related to values that can fluctuate within a short interval, such as stock prices over time. For each horizontal data point, there is a high value and a low value as well as (in some cases) a closing (or ending) value. The simple high-low chart indicates the high value and low value with a horizontal tick mark at each position and a vertical line connecting the two points. Variations show a closing value symbol. You can indicate the high and low value using spikes (vertical line only) rather than separate tick marks. The next subtype connects the closing values with a line. The last subtype connects consecutive data points with a fill line between the high and low values.

The final chart type is a Gantt chart. A Gantt chart is a special chart type that is used to indicate data over a specified time interval. This chart is typically used in project management to present the time schedule needed to accomplish individual tasks for the project. You can vary how this chart looks by using a background shadow or a 3-D bar to show depth.

Defining Parameters for Charts

The charts created earlier in this chapter depend on the query conditions that are defined in the query definition dialog box. In practice, however, many graphical applications need the user to execute the query based on a variable parameter that is defined at runtime. Depending on the value entered, the chart will be drawn dynamically by changing the data in the chart at runtime, based on the selection criteria entered by the user.

Using the columnar chart created earlier in module g34oun02.ogd, modify the chart to execute the query based on a user-specified parameter for the warehouse code and the chart year. To create a parameter, select Parameters within the Object Navigator to display the Parameters dialog box as shown in Figure 34.9. Define this parameter as a character field named P_WH_CODE with an initial value of WNJ. (Give the initial value as a valid value to help you develop the graphical display.) Additionally, create a numeric parameter called P_YEAR for the chart year.

Figure 34.9. Creating graphics parameters.

Next, modify the chart query to include the parameters as follows:

     select to_char ( to_date (to_char (hist_month_no), 'MM'), 'MON'),

          hist_ord_shipped

     from warehouse_history

     where hist_year = :P_YEAR

     and hist_wh_code = :P_WH_CODE

The chart will now use the value of the parameter at runtime to display the appropriate chart. To test the runtime capabilities of Oracle Graphics, you must first create a runtime module. (The module must be saved prior to creating the runtime module. Save this module as g34oun03.ogr.) To create the runtime module, select File | Administration | Generate, and then create the module as g34oun03.ogr. To test the interactive capability of the module, use the following command:

g25run module=g34oun03 userid=uid/pwd P_WH_CODE='ACA' P_YEAR=1994

Multiple Chart Applications

Oracle Graphics provides the capability to create individual displays that are based on multiple charts that are linked through parameters. One example of a multiple chart application is a drill-down chart, which displays a secondary chart based on data passed from one chart to the next. In other cases, a second chart can be included within the same application to display additional related data on separate chart layers.

Creating a Drill-Down Chart

A drill-down chart is a multi-chart application in which an individual chart is dependent on another linked chart. One example of this chart is shown in Figure 34.10; the two charts that were created previously are combined in a single display. By clicking on an individual pie segment, the user will make the monthly shipments chart indicate the orders shipped for the warehouse associated with the segment.

Figure 34.10. A sample drill-down chart.

To create this chart, open the Warehouse Shipments pie chart that was created earlier in module g34oun01.ogd. Create parameters for the warehouse code and year as in the previous section and create the monthly shipments query within the Object Navigator. Modify the pie chart query to include the year parameter and to select the warehouse code from the initial query. Navigate to the Layout Editor and use the chart drag points for the pie chart to resize the chart to provide room for the monthly shipments column chart.

Now, using the Chart tool from the Graphics tool palette, drag an area for the new column chart in a blank area of the Layout Editor. Define the column chart the same as in g34oun02, when you created the column chart.

To link the two charts in a drill-down relationship, click within the pie chart area to select the pie chart and then click one of the pie segments. This will present the object definition property sheet. Select the Drill-Down tab as shown in Figure 34.11. This tab is used to define the drill-down relationship for the charts. You define the relationship by selecting the parameter that will hold the value for the query. For this chart, select the P_WH_CODE parameter and set it equal to the value of the WH_CODE. Finally, set the drill-down relationship to execute the query for the monthly shipments chart.

Figure 34.11. Creating a drill-down relationship.

You can now test this chart by clicking the Run icon in the Layout Editor. Notice how the monthly chart changes based on the pie segment selected. You can now save this module as g34oun04.

Creating a Multilayer Chart

Another type of multichart application is operated by presenting a single chart at a time based on user interaction. To do this, you can create charts on separate layers and coordinate them through the use of buttons or other interface elements. Typically, multilayer charts are used to display different aspects of related data on separate chart layers. The example that you will create in this section displays the total shipments, average shipping delay, and maximum shipping delay for a given warehouse by month.

To create this display, define the parameters and query to retrieve the data for all three charts in a single query as follows:

     select to_char (to_date (to_char (hist_month_no), 'MM', 'MON') "Month",

          hist_ord_shipped "Shipments",

          hist_ship_days / hist_ord_shipped "Average Delay",

          hist_max_days "Maximum Delay"

     from warehouse_history

     where hist_year = :P_YEAR

     and hist_wh_code = :P_WH_CODE

     order by hist_month_no

In the Layout Editor, create a chart for the total shipments by month. Define this chart as a column chart with shadow as in module g34oun02.ogd. This time, however, before you finish the chart select the Values tab from the Chart Properties dialog box, as shown in Figure 34.12. As shown in this figure, the available data columns are shown in the left box on the property sheet and the box on the right displays the columns that will be used for the chart. Use the Insert and Delete buttons to set up the selection as shown in the figure. Now the chart will display only the monthly shipments.

Figure 34.12. Selecting the value column for a chart.

After you create the first chart, display the layers property sheet for the display by selecting the Tools | Layers menu pick. The Layer Settings dialog box (shown in Figure 34.13) manages the layers for a display. Using this dialog box, you can rename, activate, hide, and display the layer. You can also create new layers and delete existing layers.

Figure 34.13. The Layer Settings dialog box.

Using this dialog box, rename the first layer to Ship_layer and create a new layer named Avg_layer. The new layer will automatically be activated. Click OK to return to the Layout Editor. At this point, try to select an item from the Monthly Shipments chart on the first layer. Note that nothing from the first layer can be selected because it is not the active layer. Select the Layer Settings dialog box again and click Ship_layer in the Existing Layers box, click the Hide/Show button, and then return again to the Layout Editor. The existing chart is now hidden.

Now, create a new chart that displays the average shipping delay by month. Create this chart on the second layer. Repeat this process to create a third layer named Max_layer and a chart to display the maximum monthly shipping delay. The last step necessary to facilitate navigation between the layers of a chart is to create program units that will execute the navigation.

The program units must be assigned to an object, such as a button, in the Graphics display. The first button will navigate from the Shipments layer to the Average Delay layer. Using the Layer Settings dialog box, activate the Shipments layer and hide all other layers. To build a button, first select the text tool and click in the Layout Editor where the button should appear. In the text box, type Average Delay on two lines. To complete the text entry, click outside the text box. Next, select the Rounded Rectangle tool and draw a rectangle around the text box. Using the visual attribute definition buttons at the lower left of the layout editor, modify the rectangle to define the color and border that you want. Using Format | Bevel from the menu, select a raised bevel effect for the rectangle. Using the Select tool, select the rectangle and the text item and link them as a group object by typing Ctrl+G. This combined item is called a graphics button.

You should next create a procedure for the button by selecting Tools | Properties. The Object Properties dialog box that appears (shown in Figure 34.14) enables you to name the object and to assign a button procedure to the object.

Figure 34.14. The button object property sheet.

After you name the button, click the New button (displayed as Edit... in Figure 34.14) next to the button procedure field to create the button procedure. In the PL/SQL editor box, enter the text for the procedure as shown here:

PROCEDURE OGBUTTONPROC4 (buttonobj IN og_object,

                              hitobj IN og_object,

                              win IN og_window,

                              eventinfo IN og_event) IS

       ship_layer    OG_LAYER;

       avg_layer     OG_LAYER;

     BEGIN

       ship_layer := OG_GET_LAYER ('Ship_layer');

       avg_layer := OG_GET_LAYER ('Avg_layer');

       OG_ACTIVATE_LAYER (avg_layer);

       OG_HIDE_LAYER (ship_layer);

     END;

Repeat this process by creating buttons on all layers to navigate between the chart layers. When you are finished, save the display as g34oun05.ogd. To test the chart module, click the run iconic button. When the display appears, click the appropriate buttons to navigate between the separate layers and view the appropriate chart.

Formatting Charts with Text and Breaks

Oracle Graphics provides several optional functions that you can use to customize a chart. These functions include text objects and mechanisms for customizing the labels on a chart. Additionally, by grouping data in subcategories, the displays can show data from multiple data sets on the same chart.

Adding Text Items to Charts

You can add boilerplate text to a chart to provide user instructions or additional information on a chart. To demonstrate this function, open the drill-down chart that was created earlier in module g34oun04.ogd. Add a text object to this chart that will instruct the user on how to use the drill-down function of the chart.

Select the Text tool from the tool palette and click in a blank area of the Layout Editor. A text box will appear where you can enter the user instructions. Type Click on any pie segment to view the Monthly Shipments for the selected warehouse. After the text has been entered, click outside the text box to complete the entry.

You can further customize this text box using the palette controls to define the background fill, outside line color, and color of the text. You can also use the Format menu to define other effects such as font selection and style, text justification, border line thickness, and other attributes. By modifying these attributes, you can customize the text object to provide the effects you want.

Customizing the Chart Labels

Take a look at the shipments chart that you created as the second chart in this chapter (refer to Figure 34.7). The axis labels are based on the columns selected in the query associated with the chart. Under most circumstances, the column names are undesirable for a chart because they do not mean anything to the end user.

Oracle Graphics 2.5 provides two methods for customizing the axis labels for a chart. One way to define the labels is to use the SQL syntax method for creating a column alias directly in the select statement. (This syntax was demonstrated earlier in the multilayer chart example.)

The other way you can modify the chart labels is to explicitly define the axis labels. To do this, load the Monthly Shipments chart that was created in the "Defining Parameters for Charts" section of this chapter. Activate the chart in the Layout Editor and select Chart | Axes. You use the Axis Properties sheet (shown in Figure 34.15) to define the properties of the chart axes. In the list box at the top you select the axis to be defined, and you can define the custom label for the axis as well as other properties associated with the axis. Change the label for the X axis to be Month and the Y1 axis to be Shipments.

Figure 34.15. The axis definition property sheet.

Additionally, because there is only one data value set displayed in the chart, you can remove the legend box by selecting Chart | Frame. The Frame tab of the Frame Properties dialog box that appears (shown in Figure 34.16) defines the visual effects used for the chart. To remove the legend box, make sure that the Show Legend check box is not selected.

Figure 34.16. The Frame Properties dialog box.

The last modification for this chart is to dynamically modify the chart title at runtime. Instead of displaying a generic title, the title should include the name of the warehouse being displayed. Using the PL/SQL feature of Oracle Graphics, you can change the title in a trigger which will fire when the display is first opened. To create this trigger, select Tools | Display. Click the Edit button at the right of the Open Procedure field and create the procedure as listed following:

     PROCEDURE OGTRIGGERPROC1 IS

       chart   OG_OBJECT;

       wh      VARCHAR2(3);

       year    number;

       whname  VARCHAR2(50);

       title   VARCHAR2(100);

     BEGIN

       chart := og_get_object ('wh_monthly');

       wh := og_get_char_param ('P_WH_CODE');

       year := og_get_num_param ('P_YEAR');

       select wh_name

         into whname

         from warehouses

        where wh_code = wh;

       title := to_char (year)||' Monthly Shipments for '||whname;

       og_set_title (chart, title);

     exception

       when NO_DATA_FOUND then

         og_set_title (chart, 'Unknown Warehouse');

     END;

In this procedure, the chart handle must be retrieved as a parameter to the og_set_title built-in procedure. The values of the parameters are then retrieved into PL/SQL variables to be used to define the new title. The exception code is used in case the display receives an invalid warehouse code.

Creating a Break Chart

A break is a special type of chart that displays data for multiple similar entities on a single chart. An example of this chart is the monthly shipments shown in Figure 34.17. This chart shows the monthly shipments for all five warehouses plotted as separate curves on the same chart.

Figure 34.17. An example of a break chart.

To create this chart, first define a parameter for the chart year and build the following query:

     select to_char (to_date (to_char (h.hist_month_no), 'MM'), 'MON') Month,

          w.wh_name Warehouse,

          h.hist_ord_shipped Shipments

     from warehouses w,

          warehouse_history h

     where h.hist_wh_code = w.wh_code

     and h.hist_year = :P_YEAR

     order by h.hist_month_no, w.wh_name

In the Layout Editor, create a chart using the curved line chart subtype. To define the multiple lines, select the Categories tab in the Chart Properties dialog box as shown in Figure 34.18. To create a multiline chart, first define the chart category as Month and also set the Subcategory (lower right) equal to Warehouse. This will cause the X-axis to vary by month and the data within each category value will be grouped by warehouse.

Figure 34.18. Defining a break chart subcategory.

Press OK to complete the chart design. Note that the five lines are presented in separate colors with a legend box at the right of the chart that can be used to translate the chart. Save the chart as g34oun08.

Advanced Formatting Techniques

As in all of the Developer/2000 products, Oracle Graphics provides facilities to enhance the application module by using PL/SQL program units. These program units can be used to modify the default processing for a chart as well as to define how data can be displayed or to determine if the data should be included in the chart at all.

Using Format Triggers

A format trigger is used to modify how a chart element can be displayed. In the chart that was created in the section "Creating a Multilayer Chart," the chart on the third layer displayed the maximum shipping delay for each month. Suppose that management has set a standard that all orders must be shipped within one week of order placement. In order to emphasize substandard performance, the chart should indicate any month in which the maximum delay is greater than seven.

To demonstrate this technique, load the chart created in the "Creating a Multilayer Chart" section. In the Layout Editor, use the Layers Management tools to activate the Maximum Delay layer and chart. (Hide all other charts.) Within this chart, click any one of the tick mark labels along the X-axis. This will select all the labels as the active objects. To build the format trigger, select Tools | Properties. Next to the format trigger field, click the New button to activate the PL/SQL editor behind the property definition sheet. Click OK to close the property sheet.

In the PL/SQL editor, complete the format trigger procedure as shown following:

     -- Chart Element Format trigger. Called for each member of a

     -- specified chart element group (e.g., each bar in a group of

     -- bars for a bar chart).

     -- ARGUMENTS:

     --   ELEM   The current chart element.

     --   QUERY  The query associated with this chart. The current

     --          row of the query is the one associated with ELEM.

     --          Use OG_GET_xxxCELL to get at column values for the

     --          current row.

     PROCEDURE OGFORMATTRIG0(elem IN og_object,

                             query IN og_query) IS

       maxdel     NUMBER;

     BEGIN

       maxdel := OG_GET_NUMCELL (query, OG_NEWDATA, 'Maximum Delay');

       if maxdel > 7 then

         og_set_gcolor (elem, 'red');

       end if;

     END;

This procedure retrieves the value from the query into a PL/SQL variable, and if this value is greater than seven, the month label will be displayed in red.

Creating Data Filters

A data filter is used to eliminate data retrieved by a query that should not be included in the chart. Sometimes for better performance a query will retrieve all data from a table without restricting the retrieval in the where clause.

Another use of the data filter is to retrieve all data necessary for all charts in a display and to restrict the results to the individual chart for which it is needed. This data can then be eliminated from the query using a data filter. A data filter is a PL/SQL procedure that is attached to a chart. To create a data filter, select the Data tab in the Chart Properties dialog box. Click the New button next to the filter field to create the filter function in the same way you create a format trigger. This filter is a boolean (TRUE or FALSE) function and will be used to determine if a row should be plotted on the chart. Suppose, for example, that in the break chart created earlier only the Wharton, NJ and Boise, ID warehouses should be plotted.

This function should be created as

     FUNCTION OGQUERYFILTER0 (chartobj IN og_object,

                              query IN og_query) RETURN BOOLEAN IS

whname    VARCHAR2(50);

     BEGIN

       whname := OG_GET_CHARCELLL (query, OG_NEWDATA, 'Warehouse');

       if whname in ('Wharton, NJ', 'Boise, ID') then

         RETURN TRUE;

       else

         RETURN FALSE;

       end if;

     END;

This filter will return a value of TRUE for all valid rows that will then be plotted on the chart.

Summary

This chapter presented many of the capabilities of Oracle Graphics 2.5 that will enable most Oracle developers to use this tool constructively. By practicing and working with the tool set you can quickly become an expert user of this powerful tool.

You can use Oracle Graphics to present graphical representations of Oracle data. The extensive options of this tool gives the Oracle developer the capability to create more than 50 different chart types that can be customized to provide the functionality needed for today's high-level executive information systems. Graphical views of data deliver the impact that cannot be achieved with mere textual views of numerical data.