ORACLE 10 Using Graphics Builder

1
ORACLE - 10Using Graphics Builder

• Jason Chen, Ph.D.
• Professor of MIS
• School of Business Administration
• Gonzaga University
• Spokane, WA 99258

2
Term Project

• 1. Follow the format and requirements stated on
  the syllabus and samples.
• 2. SDLC approach
• 3. Database design requirements
• ER-D, Functional dependency, normal forms
• 4. Oracle implementation
• Database creation, report, graphics (chapters 9A,
  10)
• Charts with form (chapter 10 p.904-922)

3
Registering Oracle File Extensions and Installed
Folders (Select View then folder options then
) 1 .BRW Query Builder
oracle\oraDev\bin\QBE60.exe 2 .PLS
Procedure/Program Builder oracle\oraDev
\bin\DE60.exe .PLL Library 3 .FMB
Form Builder oracle\oraDev\bin\ifbld60.exe
.FMX Form Module Executable
oracle\oraDev\bin\ifrun60.exe Form Runtime
oracle\oraDev\bin\ifdbg60.exe Form Compile
oracle\oraDev\bin\ifcmp60.exe 4 .RDF Report
Builder C\oracle\oraDev\bin\RWBLD60.exe
.REP(executable file) C\oracle\oraDev\bin\RWR
UN.exe .TDF (template file) 5 .OGD
Graphics Builder (design file) --
note that the text listed wrong information
oracle\oraDev\bin\GOBLD60.exe .OGR
Graphics Runtime oracle\oraDev\bin\GORUN60.exe
4
Graphics Builder

• Graphics builder is a developer utility used for
  creating graphical displays of database data.
• All labels and values that appear on charts is
  retrieved from the database.
• Oracle graphics builder applications can run as
  stand alone applications, or they can be embedded
  within forms or reports.

5
Chart Types

• Column
• Shows discrete values using vertical columns
• Bar
• Shows discrete values using horizontal bars
• Gantt
• Shows task or project scheduling information
  using horizontal bars

6
More Chart Types

• High-Low
• Shows multiple Y-axis values for each X-axis
  point
• Line
• Shows data values as points connected by lines to
  show trends
• Mixed
• Combines a column and line chart

7
More Chart Types

• Pie
• Shows how individual data values contribute to an
  overall total
• Scatter
• Plots 2 sets of potentially related data to
  identify possible trends
• Table
• Shows text data in a table

8
More Chart Types

• Double-Y
• Shows a column or line chart with multiple values
  for the same X-axis value

9
Creating a Chart

• Steps of using the Chart Genie to create a chart
• Specify the SQL query to retrieve the chart data
  from the database
• Select the chart type
• Define the chart display properties

10
Creating a Pie Chart

• Start SQLPlus
• Copy data files in the folder of chapter10
• Re-fresh three databases
• SQLPlusgt _at_ a\chapter10\clearwater.sql
• SQLPlusgt _at_ a\chapter10\northwoods.sql
• SQLPlusgt _at_ a\chapter10\softwareexp.sql
• Start Graphics Buider
• Click Start then Programs then

11
Specifying Chart Queries

• Chart query must return all data that appears on
  the chart
• Pie chart
• pie slice labels
• numerical values corresponding to each pie slice
• Bar chart
• X- and Y-axis labels
• numerical values corresponding to each bar value
• Line chart
• X- and Y-axis labels
• numerical values corresponding to each line value

12
Specifying Chart Queries

• When using SQL group functions (SUM, AVG, MIN,
  MAX) for chart data, assign a column alias

Group function
Column alias
13
Defining Chart Properties

• Chart Name
• How chart is referenced internally
• Title
• Chart type
• Column, bar, pie, etc.
• Chart subtype
• Formatting and display features for selected
  chart type

14
Chart Properties Dialog Box

• Chart tab

Chart types
Chart subtypes
15
Data Tab

• Enables you to edit the SQL query

Click Edit to edit SQL query
16
Categories Tab

• Defines the labels that appear on the chart

Available label fields (data fields from query)
Selected label fields
17
Values Tab

• Specifies the numerical data field(s) that are
  displayed

Available data fields (numerical data fields
from query)
Selected data field
18
Figure 10-2 on p.886
To save the chart Step 4 (p.887)
19
Steps 1, 2 (p.887)
20
Step 4 (p.889) Figure 10-4
Step 1 (p.890)
21
Steps 1-4 (p.890)
Step 1 (p.891)
22
Step 5 (p.892) Figure 10-6
23
Editing and Formatting the Chart Display
Steps 1-3 (p.893)
24
OLD
New step3 (p.893)
25
Step 1 (p.893)
26
Modifying Pie Chart Labels (p.892)

• Frame Properties dialog box (p.893)
• Used to format the chart frame area
• Show/Hide legend
• Show percentages on pie charts

27
Steps 2,3 (p. 893)
28
Formatting the Chart Display (p.894) Applying
Format Masks to Chart Data Fields

• Select the field on the chart display
• Select or add a format mask
• Format masks can only be applied to NUMBER and
  DATE fields

29
Formatting the Chart Display (p.895)
Step 3 (p.895)
30
Formatting the Chart Display (p.895)
Step 6 (p.895)
31
Chart File Types

• Design (.ogd) files
• Can be viewed and modified in Graphics Builder
• Displayed in Graphics Debugger window
• Executable (.ogr) files
• Finished application viewed by users
• Displayed in Graphics Runtime window

32
Step 5 (p.900)
33
Formatting the Column Chart Layout (p.900)
Step 1 (p.901)
34
Steps 2-5 (p.901)
35
Steps 2-8 (p.901-902)
36
Result of p.902 Note that if the result shown as
left, you may click update chart on the Chart
menu
Right result
37
Steps 1-4 (p.903) Change Colors
38
Integrating Charts and Forms

• Approaches
• Create the form, then call the chart as a
  separate application
• Display the chart directly on the form

39
Creating a Form That Calls a Chart
Value selected on form determines values
displayed on chart
40
Calling a Chart From a Form

• Create parameter(s) in the chart query to accept
  input parameter(s) from the form
• Create a parameter list in the form to pass the
  input parameter(s) from the form to the chart
• Run the chart from the form using the RUN_PRODUCT
  procedure

41
Creating a Parameter in theChart Query

• Substitute one or more search conditions in the
  report SQL query with a parameter name prefaced
  with a colon ()

parameter
42
Creating a Parameter List

• Declare the parameter list variable in the
  DECLARE section of the trigger that calls the
  chart
• Create the list

declaring the list
bind parameters
creating the list
43
Adding Parameters to the Parameter List

• Parameter lists can only pass character or record
  group data
• Add parameters to the parameter list using the
  ADD_PARAMETER procedure
• ADD_PARAMETER(list_id, key, paramtype, value)
• List_id ID of the parameter list
• Key name of the parameter in the report
• Paramtype values can be TEXT_PARAMETER (for
  character values) or DATA_PARAMETER (for record
  groups)
• Value data value to be passed in parameter list

44
Example of the ADD_PARAMETER Procedure
List
Key
Paramtype
Value
45
Running the Chart Using theRUN_PRODUCT Procedure

• RUN_PRODUCT(product, document,
• communication_mode, execution_mode,
• location, parameter_list_id, display)
• Product application to run
• REPORTS or GRAPHICS
• Document complete path and filename to chart
  .ogd file
• Communication mode
• SYNCHRONOUS control returns to the form only
  after chart is closed
• ASYNCHRONOUS user can toggle between chart and
  form

46
Running the Chart Using theRUN_PRODUCT Procedure

• RUN_PRODUCT(product, document,
• communication_mode, execution_mode,
• location, parameter_list_id, display)
• Execution_mode
• RUNTIME called product's runtime environment is
  started
• BATCH called product is displayed directly on
  the form
• Location
• FILESYSTEM chart file stored in the filesystem
• DATABASE chart file stored in the database
• Parameter_list_id ID of the parameter list to
  be passed
• Display name of the graphic image object on the
  form only used when displaying a chart directly
  on a form canvas
• Value is NULL when displaying a chart in the
  Graphics Runtime window

47
Example RUN_PRODUCT Procedure
Product
Document
Communication Mode
Location
Execution Mode
Display
Parameter List ID
48
Form With anEmbedded Chart
49
Creating a FormWith an Embedded Chart

• Create a data block form that contains the data
  that will appear in the chart
• Use the Chart Wizard to create the embedded chart
• Edit the chart formatting as necessary in
  Graphics Builder

50
Use the Chart Wizard to Specify

• Chart title, type, and subtype
• Form data block that is the source of the chart
  data
• Data fields that specify the data for the chart
  Category and Value data
• The name of the file that stores the chart .ogd
  file

51
Formatting the Chart Created by the Chart Wizard

• Open the chart .ogd file in Graphics Builder
• Edit the chart properties
• Size
• Font types
• Legend display
• Colors
• Label format masks

52
Creating a Report with an Embedded Chart
53
Creating a Report With an Embedded Chart

• Create a report that contains the data that will
  appear in the chart
• Use the Chart Wizard in Report Builder to create
  the chart
• Modify and resize the chart in Report Builder
• Chart must be in same repeating frame as its
  source data columns
• Format the chart in Graphics Builder if necessary