Oracle Unleashed- P15

Chia sẻ: Thanh Cong | Ngày: | Loại File: PDF | Số trang:50

0
52
lượt xem
3
download

Oracle Unleashed- P15

Mô tả tài liệu
  Download Vui lòng tải xuống để xem tài liệu đầy đủ

Oracle Unleashed- P15: When I first started using Oracle many years ago, it was possible to know the database and the tools available. With the rash of recent releases of different options for the database and the spate of new tools, only people who wear their underpants over their trousers will be able to know everything there is to know about the Oracle products.

Chủ đề:
Lưu

Nội dung Text: Oracle Unleashed- P15

  1. Summary This chapter explored several techniques for developing Oracle Reports. Due to the limitations of space available, some of the simpler features might have been glossed over. Also, as with any powerful application development tool, there are many ways to produce the same result. Hopefully enough material has been presented to give you a fairly extensive understanding of this product. It has been my experience that the only way to truly learn a tool such as this is through repetitive practice and experimentation. I encourage you to expand on this material to gain the expertise necessary to use this tool knowledgeably and efficiently. Previous Next TOC Home Page Page Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  2. Previous Next TOC Home Page Page q 34 r Oracle Graphics 2.5 s The Oracle Graphics Environment s The Layout Editor s Initializing the Graphics Environment s Creating a New Display s Defining Displays s Creating a Pie Chart s Creating a Columnar Chart s Other Chart Types s Defining Parameters for Charts s Multiple Chart Applications s Creating a Drill-Down Chart s Creating a Multilayer Chart s Formatting Charts with Text and Breaks s Adding Text Items to Charts s Customizing the Chart Labels s Creating a Break Chart s Advanced Formatting Techniques s Using Format Triggers s Creating Data Filters s 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 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  3. 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 Select Select object(s) from canvas. Zoom in/out in Layout Editor. Click on the canvas to zoom in at the mouse location. Click while Magnify holding the Shift key to zoom out. Rectangle Draw a rectangle. Ellipse Draw an ellipse or circle. Polygon Draw a multi-sided object. Rounded Rectangle Draw a rectangle with rounded corners. Chart Add a new chart to the layout. Symbol Insert a chart symbol. Rotate Rotate the object. Reshape Reshape the object. Line Draw a line. Arc Draw an arc segment. Polyline Draw a series of connected lines. Freehand Draw in freehand mode. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  4. Text Insert text. Text Field Insert a text field. 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 copyright 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. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  5. 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. In addition to creating an Oracle Graphics chart using an embedded query, you can use data from external programs such as Microsoft Excel (SYLK file format) or Lotus 1-2-3 (WKS file format), as well as from a .PRN output file. Also, an Oracle select statement stored in an operating system external SQL file can be used to retrieve data for your Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  6. chart. 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. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  7. 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 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  8. 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 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  9. 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", Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  10. 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 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  11. 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 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  12. 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 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  13. 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. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  14. 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'); Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  15. 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 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  16. 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. Previous Next TOC Home Page Page Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  17. Previous Next TOC Home Page Page q 35 r Developer 2000 Integration s Displaying Oracle Graphics in Oracle Forms s Executing Oracle Reports from Oracle Forms s Displaying Oracle Graphics in Oracle Reports s Interfacing to Non-Oracle Applications s Interfacing with the Windows API and other DLLs s Summary 35 Developer 2000 Integration The previous three chapters discussed the Developer 2000 components as independent, standalone tools. Based on Oracle's concept of the open-enterprise connectivity, each of the Oracle tools have been designed to be combined into a multi-faceted, integrated application. Oracle Forms can display graphics on the presentation canvas, and reports can be spawned from within the tool. Additionally, Oracle Reports can display an Oracle Graphic's chart directly in the report itself. In addition to the ability of the Developer 2000 tools to work with each other, the Oracle tools have the ability to work in an integrated environment with other compatible non-Oracle applications. This chapter will describe the steps necessary to produce these integrated applications quickly. Displaying Oracle Graphics in Oracle Forms Oracle Graphics charts can be integrated into an Oracle Forms module using specific forms objects and built-in procedures and functions. An example of a module that integrates these tools is shown in Figure 35.1. In this form, the annual sales for all warehouses is shown in standard form text fields, and a pie chart that indicates the relative contribution of each warehouse is shown next to the text items. Below these text items, the monthly shipments for the current warehouse is shown in a bar chart. The user may click on any of the pie segments to select a record in the form. Additionally, if the user selects any record in the form, the bar chart will change to show data for the new warehouse. Figure 35.1. Integrated Oracle Forms and Oracle Graphics module. To build this module, first build the two graphics modules using the Oracle Graphics Designer. The first chart should be based on the following query: select hist_wh_code, sum (hist_ord_shipped) Shipments, from warehouse_history where hist_year = 1994 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  18. group by hist_wh_code; Next, define a pie chart for this query using the pie with depth attribute. After the pie chart is displayed in the layout editor, double-click on one of the pie segments to present the trigger-definition form for the pie slices. Select the drill- down tab, and set this item to set the P_wh_code parameter equal to hist_wh_code on a mouse down trigger. (Note, do not select a query for the drill-down trigger.) The next chart will be a bar-chart with shadow and will be based on the following query: select to_char (to_date (to_char (hist_month_no), ' MM' ), ' MON' ) Month, hist_ord_shipped Shipments from warehouse_history where hist_year = 1994 and hist_wh_code = :P_wh_code; The P_wh_code parameter will hold the value for the warehouse code for the chart. Now, create the form for the WH_ANNUAL_SHIPS view displaying the warehouse name and total annual shipments for 1994. Next, create a control block to hold the chart items and using the chart item tool in the layout editor, create the two areas for the Graphics charts on the canvas. These items will be linked to the Graphics modules using PL/SQL. To use the Oracle Graphics built-in program units, the OG.PLL library must be attached to the form. The first trigger that should be created is the WHEN-NEW-FORM-INSTANCE trigger that will set up the charts as soon as the form is started. The bar chart requires parameters to define the warehouse that should be displayed in the chart. This trigger should be defined as follows: declare plist ParamList; begin execute_query; OG.Open (' g35oun01.ogd' , ' CONTROL.PIE_CHART' , FALSE); plist := Create_parameter_list (' chart_parms' ); Add_parameter (plist, ' P_wh_code' , TEXT_PARAMETER,:wh_annual_ships. wh_code); OG.Open (' g35oun02.ogd' , ' CONTROL.BAR_CHART' , FALSE, TRUE, plist); Destroy_parameter_list (plist); end; The parameters for the OG.Open procedure are the chart module name, form display object, clip indicator, refresh indicator, and parameter list id. By defining the clip indicator in this procedure call as FALSE, the chart will be included on the form by scaling it to fit within the defined area. The default value of TRUE will cause the chart to be clipped to fit within the display. Now, the mouse down trigger interface must be built in the form. For the PIE_CHART item, create a Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  19. WHEN_MOUSE_CLICK procedure as follows: declare wh VARCHAR2(3); plist ParamList; begin OG.mouse_down (' g35oun01.ogd' , ' CONTROL.PIE_CHART' ); wh := OG.GETCHARPARM (' g35oun01.ogd' , ' CONTROL.PIE_CHART' , ' P_wh_code' ); go_block (' WH_ANNUAL_SHIPS' ); loop if wh = :WH_ANNUAL_SHIPS.WH_CODE then exit; end if; next_record; end loop; plist := create_parameter_list (' chart_parms' ); add_parameter (plist, ' P_wh_code' , TEXT_PARAMETER, wh); OG.REFRESH (' g35oun02.ogd' , ' CONTROL.BAR_CHART' , plist); destroy_parameter_list (plist); end; The GETCHARPARAM function will extract the parameter from the first chart, and the OG.REFRESH procedure will pass the new parameter value to the second chart. One thing to note when working with Graphics in Oracle Forms is that the displays may look different when displayed in Forms, especially when the chart is scaled to fit within the chart area. The primary problem relates to the text labels on a chart due to the fact that Oracle Forms tries to use the closest fitting font to the defined display, often resulting in overlapping labels. The best advice is to define the chart with the smallest possible fonts, and hope for the best. Honestly, building forms where everything fits properly can be done, but it will require a significant effort. Also, when building the chart, define the chart area as approximately the same size as it will be in the integrated module. Executing Oracle Reports from Oracle Forms Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  20. Printing an Oracle Reports 2.5 report from Oracle Forms requires the use of a single built-in procedure, RUN_PRODUCT. To demonstrate this concept, open the form that was created in Chapter 32 in the section on multi- block relationships. This form was used to enter new orders into the system. The boilerplate graphic's report that was built in Chapter 33 was a Shipping Document for an order. This two modules will be integrated using a form button with a WHEN-BUTTON-PRESSED trigger. First create a new button on the form canvas labeled Print Document, then create the trigger as: declare plist ParamList; begin plist := create_parameter_list (' print_parms' ); add_parameter (plist, ' P-order_no' , TEXT_PARAMETER, to_char (:orders. order_no)); add_parameter (plist, ' PARAMFORM, TEXT_PARAMETER, ' NO' ); add_parameter (plist, ' DESTYPE' , TEXT_PARAMETER, ' PRINTER' ); add_parameter (plist, ' BATCH' , TEXT_PARAMETER, ' YES' ); RUN_PRODUCT (REPORTS, ' r35oun01.rdf' , ASYNCHRONOUS, BATCH, FILESYSTEM, plist); destroy_parameter_list (plist); end; As indicated by this module, the command line parameters for the R25RUN command are passed to the product as part of the parameter list. This is all that is needed to run a report directly to the printer from Oracle Forms. Also, note that the report will be run asynchronously in background, while the operator is free to execute other tasks as the report is printing. The RUN_PRODUCT built-in may also be used to invoke Oracle Graphics from Oracle Forms; however, the Graphics chart will not have the same interactive functionality as shown in the prior section. Displaying Oracle Graphics in Oracle Reports Oracle Graphics can be added to a Report by using the Oracle Graphics tool in the Reports Layout Editor. To demonstrate this concept, open the first report created in Chapter 33. Select the Oracle Graphics tool from the tool palette, and drag an area for the chart below the main report layout. The new object will display a chart in the designated area. Double-click in this area to display the chart definition dialog box as shown in Figure 35.2. Name this object D_performance, and define the Graphics File as g35oun01.ogd. Run the report and save the new report in a file. Figure 35.2. Oracle Graphics Chart Definition dialog box. Interfacing to Non-Oracle Applications Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Đồng bộ tài khoản