Docstoc

Templates for Paper Masks

Document Sample
Templates for Paper Masks Powered By Docstoc
					  Enhanced Guide to Oracle8i

Chapter 9:
Using Report Builder




                               1
Reports
  Snapshot summary of database
   data
    Viewed on screen
    Printed on paper
    Printed to a file and distributed
     electronically



                                         2
Report Uses
  A report can:
    Display SQL query data
    Display summary columns
    Display calculated values based on
     formulas applied to database data
    Be formatted to look like an invoice
     or other business document


                                            3
Report Styles
    Tabular (table-format)
    Form-like (looks like a form)
    Mailing labels
    Form letters
    Group left and Group above
      Displays master-detail relationships
  Matrix and Matrix with group
      Data value appears at the intersection of a
       column and row heading

                                                     4
Creating a Report Using the
Report Wizard
 1.   Enter the SQL query to retrieve
      all of the report data
 2.   Choose the display fields
 3.   Specify the report master-detail
      groups
 4.   Specify the field prompts
 5.   Select a predefined template
                                         5
Creating a Master-Detail
Report
   Specify the master and detail records on the
    Report Wizard Groups page
       Detail records remain in Available Fields list



                                                  Master
                                                  records
                                                  Detail
                                                  records


                                                            6
    Using Format Masks in
    Reports
   Format masks can be applied to:
       NUMBER fields
       DATE fields


   Format masks cannot be applied to
    text fields


                                        7
    Report Templates
   Specify report appearance
    characteristics
       Font sizes, types and colors
       Graphic images
       Background shading
       Page numbering
       Date report is created
   Stored in a file with a .tdf extension
                                             8
Applying Templates to Reports
   Use the Report Wizard Templates page
    to specify:
       The name of a predefined template
       The file path specification to a custom
        template
   To register a template file as a custom
    template, modify the Developer global
    preferences file (cagprefs.ora)
                                                  9
The Report Wizard
Templates Page




                    10
Report Builder Windows
   Live Previewer
       Shows how report will look when viewed or
        printed
   Layout Model
       Shows report components symbolically
   Data Model
       Shows underlying report data structure


                                                 11
Live Previewer




                 12
    Data Model
   Shows report data components


        Query
        Data
        link

        Record
        group

                                   13
    Data Model Components
       Record group: set of
        records with the same     Column
        column headings
         Master-detail reports
          have multiple record
          groups
         Default record group
          name:
          G_first_column_name
       Individual fields are
        called columns
                                     14
Report Column Types
   Database – Scalar
       Discrete data value retrieved from database
   Summary
       Data value calculated by applying a summary
        function to a report column
   Formula
       Data value calculated by applying a user-defined
        PL/SQL function to a report column


                                                      15
Break Groups
   You can create a user-defined break
    group to change the order of report
    data

                             Break
                             group




                                          16
Using a Break Group
to Change Report Data Order
   Drag the break
    group out of its
    existing record group
    to create a new
    break group
   Open the new break
    column Property
    Palette, and change
    the Break Order
    property value to the
    desired sort order


                            17
Group Filters
   Allow you to limit the number of records
    retrieved by a report query
   Group filter criteria:
       Last: retrieves a specific number of records from
        the end of the record set
       First: retrieves a specific number of records from
        the beginning of the record set
       PL/SQL: retrieves a specific number of records
        based on a user-defined PL/SQL function


                                                       18
Creating a Group Filter
   Open the
    Property
    Palette of the
    record group
    on which you
    wish to apply
    the filter
   Change the
    Filter Type
    property to the
    desired value
                          19
    Layout Model
   Shows reports components
    symbolically




                               20
Report Sections
  Clearwater Traders
                       Header: optional title page
     Sales Report




Main Section:
report data
and computations                           *** End of Report ***


                        Trailer:
                        optional final
                        page                                       21
    Main Report Section
       Main Section has margins
        for values that are
        displayed on each report
        page
         Titles
         Dates
         Page numbers
                         Margins



                                   Page 1 of 5


                                                 22
Section Navigation Buttons



Header                  Main Section
                        Margins
Main                  Trailer
Section




                                23
    Frames
   Containers for grouping related report
    items
       Header frame: contains the column
        headings for a record group
       Repeating frame: contains a record
        group's data fields
           Fields repeat multiple times depending on
            number of records retrieved
       Group frame: encloses a record group's
        header frame and repeating frame
                                                        24
Report Record Group and
Corresponding Frames




                                                   Header
ID         Building      Room     Capacity
                                                   frame

F_LOC_ID   F_BLDG_CODE   F_ROOM   F_CAPACITY       Repeating
                                                   frame


                                     Group frame
                                                       25
    Default Frame Names
   Group frame:
    M_record_group_name_GRPFR

   Header frame:
    M_ record_group_name _HDR

   Repeating frame:
    R_ record_group_name
                                26
    Selecting Report Frames
   Different report frames might not be visible
    because they are on top of each other




       report frames
       are on top of each other

                                                   27
    Selecting Parent Frames
       Parent frame: frame that directly encloses
        an object
       To select a specific frame: click an object
        directly inside the frame, then click the
        Select Parent Frame button
         Sometimes an inner frame and its parent frame
          are directly on top of each other, so there might
          not be a visible change when you click


                                                              28
    Components of Master-Detail
    Reports
   In a master-detail report, a separate record group is
    created for each level of records


                                              Master-detail
                                              relationships




                                                        29
    Master-Detail Report Frames
   In a master-detail report with many
    record groups, the highest-level (most
    detailed) record group frames are
    nested inside the frames for the less-
    detailed record groups
   Header frames are only created when
    data is displayed in columns (group
    above style)
                                             30
               Master-Detail Report
               Repeating Frames

R_G_term_desc    Term:        F_term_desc


                    Course:      F_call_id

R_G_call_id
                         Section. F_sec_num     Instructor:   F_f_first        F_f_last

                                 F_day        F_time                  F_bldg_code    F_room
R_G_sec_num              Day:                           Location:



    R_G_s_id




                                                                                              31
Printing Report Records on
Separate Pages
   Open the Property
    Palette for the
    repeating frame of
    the record group
    for the records that
    will appear on
    separate pages
   Change the
    Maximum Records
    per Page property
    value to 1
                             32
    Repositioning Report Objects
       Confine mode
         When on, you cannot drag an object outside of
          its confining frames
       To toggle confine mode on and off, click the
        Confine Mode button




                                                          33
    Repositioning Report Objects
       Flex mode
         When on, enclosing frames are automatically
          resized when you move an enclosed object
         Works well when moving objects lower on the
          report page
         Doesn't work well when moving objects to the
          right edge of the report page, because resized
          frames might extend beyond report page
          boundaries
       To toggle flex mode on and off, click the
        Flex Mode button

                                                           34
Adjusting Report Column
Spacing
   Elasticity determines whether a
    column’s width and height is fixed or
    can change depending on the data
    value it contains
   Elasticity dimensions
       Horizontal: determines how wide the
        column will be
       Vertical: determines how tall the column
        will be
                                               35
Elasticity Values
   Fixed
       Columns have exact size as defined on layout
   Contracting
       Column will shrink if retrieved data value is small
        than column size
   Expanding
       Column will expand if retrieved data value is larger
        than column size
   Variable
       Column will contract or expand depending on
        retrieved data value size
                                                         36
Layout Model Elasticity
Indicators
                 Fixed

                 Contracting

                 Expanding

                 Variable



                               37
Separating Tabular Data With
Variable-Length Lines
   You can place data in a grid that
    automatically contracts or expands
    depending on the number of records
    retrieved




                                         38
Steps For Creating Gridlines
   Draw a single horizontal line under all fields
   Draw vertical lines on the edges of all fields
    that are the same height as the repeating
    frame
   Select all of the vertical lines, and open an
    intersection Property Palette
   Change the Line Stretch with Frame property
    to the name of the repeating frame

                                                39
Changing the Vertical Gridline
Property
                        Select all
                        vertical lines
                        and open
                        intersection
                        Property
                        Palette

                     Change property
                     to name of
                     repeating frame


                                  40
Report Parameters
   System parameters
       Specify how report appears in the user display,
        and how the Reports Runtime application behaves
   User parameters
       List of possible values from which the user can
        choose to specify a report query search condition
        and filter report output
   User can select parameter values on report
    Parameter Form

                                                       41
  Report Parameter Form


                      Title
                      Hint line
System                Status line
parameter
                        Parameter
User                    lists
parameter


                                42
System Parameters
   Listed in Data
    Model Main
    Section in
    Object Navigator
   Open parameter
    Property Palette
    and modify
    values
                       43
User Parameters
   Parameter list appears on report
    Parameter Form
   User selects parameter value before
    running the report
   Data based on parameter search
    condition appears in report


                                          44
Creating a User Parameter
1.   Create parameter in the Object Navigator
2.   Specify the SQL query to display the list of
     possible input values
3.   Modify the data model query to use the
     input parameter value as a search
     condition




                                                45
Creating a User Parameter

          User parameter in
          Object Navigator




                      User parameter in
                      query search
                      condition
                                          46
Parameter Form Builder
   Utility for customizing the parameter form




                                      Hint line
                                      Status line




                                                    47
Parameter Form Builder
Interface

                         Specify
                         text


                          Select
                          system
                          parameter




                                   48
Multipanel Reports
   Sometimes a report is very wide and
    spans multiple horizontal pages




       Page 1                 Page 2

                                          49
Physical and Logical
Report Pages
   Physical page: corresponds to printer
    output
       Example: 8.5 x 11 inches
   Logical page: value that is a multiple of
    a logical page
       Can create reports that span multiple
        horizontal or vertical physical pages


                                                50
Creating a Multipanel Report
   Open the
    report Layout
    Model Main
    Section
    Property
    Palette, and
    change the
    Panels per
    Page values
                               51
Displaying Image Data
   You can display image data that is
    stored in a BLOB database field



                                    Image
                                    retrieved
                                    from
                                    database

                                          52
Creating a Report That
Displays Retrieved Images
   Create the report
    using the Report
    Wizard, and specify
    that the SQL query
    retrieves the image
    column
   Open the image field
    Property Palette, and
    change the File
    Format property
    value to Image
                            53
Explicit Anchors
   Used to link report objects so they always
    appear on the same page when viewed or
    printed
   Links master objects to detail objects




               Anchor   Detail
      Master                                     54
Creating an Explicit Anchor
   Select the Anchor tool     on the tool
    palette
   Click the mouse pointer on the bottom
    right corner of the master (parent)
    object
   Double-click the mouse pointer on the
    bottom left corner of the detail (child)
    object

                                           55
Report File Types
   Report definition files
       Specify the report content and appearance
       Created and modified by report developers
   Report output files
       Generated by users to retrieve and display
        report data
       Cannot be modified


                                                56
Report Definition File Types
   .rdf
       Binary file generated, displayed, and modified in
        the Report Builder utility
   .rex
       Text file containing report definitions
       Can be displayed and modified in a text editor
   .rtf
       Rich text file containing report definitions
       Can be displayed and modified in a word
        processor that supports .rtf files

                                                         57
Report Output File Types
   .rep
       Report executable file
       Can be viewed in the Reports Runtime
        application
       Reports Runtime can be used to save files
        to alternate formats that can be viewed in
        word processors and Web browsers


                                                58
Displaying Calculated Values
   Formula columns
       Display values defined by user-defined PL/SQL
        functions applied to report columns
   Summary columns
       Display values defined by summary functions
        applied to report columns
   Placeholder columns
       Display values defined by either formulas or
        summary functions

                                                        59
Steps for Creating and
Displaying a Formula Column
1.   Create the formula column in the report
     Data Model
2.   Write the function to perform the
     calculation
3.   Draw a display field on the form layout
     to display the function result




                                               60
Creating the Formula Column
in the Data Model
   Select the Formula column tool       on
    the tool palette
   Click the record group that will contain
    the formula column
       Formula column must be in record group
        that contains report columns that are
        referenced in the formula


                                             61
Configuring the Formula
Column
   Open the formula column
    Property Palette
   Rename the formula column
    to a descriptive name
       Formula column name
        format: CF_formula_result
   Specify the PL/SQL function



                                    62
    Writing the Formula Column
    PL/SQL Function
   To reference a report column in a function,
    preface the column name with a colon (:)




                                        Report
                                        column
                                        reference


                                                    63
    Drawing the Formula Column
    Display Field
   Draw the formula column display field in
    the same repeating frame as the report
    columns used in the function calculations


                                                 repeating
                                                 frame


             report columns     formula column
             used in function   display field
             calculation                            64
Creating a Summary Column
   Approaches:
       Create summary column and display field
        using Report Wizard Totals page
       Create summary column and display field
        manually




                                              65
Report Wizard Totals Page




                            66
Creating a Summary Column
and Display Field Manually
   Create the summary column in the data
    model
       Column must be in record group above summary
        column data source
   Specify the summary function and report
    column in the summary column Property
    Palette
   Manually draw the display field on the Layout
    Model
       Display field must be in repeating frame
        corresponding the record group that contains
        summary column                                 67
Creating Queries and Data
Links Manually
   To create a master-detail report, you
    can create separate queries to retrieve
    the master and detail records, and then
    link the queries manually




                                         68
Creating a Query Manually
   Select the SQL Query tool   on the
    Data Model tool palette
   Draw the query on the data model
   Open the query Property Palette, and
    specify its properties




                                           69
Types of Data Links
   Query to Query
       Primary key of the parent record group is a
        foreign key in the child record group
   Group to Group
       One of the columns in the parent record group
        has a foreign key relationship with one of the
        fields in the child record group
   Column to Column
       Links two identical columns that do not necessarily
        have a foreign key relationship

                                                         70
Creating a Data Link Manually
   Select the Data Link tool        in the
    Data Model tool palette
       Query to Query: draw a link from the
        parent query to the child query
       Group to Group: draw a link from the
        parent record group to the child record
        group
       Column to Column: draw a link from the
        parent column to the child column

                                              71
    Running a Report From a
    Form
       Reports are often run from Form Builder
        applications in integrated database
        applications
       Steps for running a report from a form:
         Create a bind parameter(s) in the report to
          accept an input parameter(s) from the form
         Create a parameter list in the form to pass the
          input parameter(s) from the form to the report
         Run the report from the form using the
          RUN_PRODUCT procedure
         Destroy the parameter list
                                                            72
    Creating a Report Bind
    Parameter
   Substitute one or more search conditions in
    the report SQL query with a parameter
    name prefaced with a colon (:)




                                      bind parameters


                                                   73
Creating a Form Parameter
List
   Declare the parameter list variable in the
    DECLARE section
   Create the list

                          declaring the list
                           bind parameters



                                               creating
                                               the list


                                                          74
    Adding Data Parameters to
    Parameter Lists
       Parameter lists can only pass character or record
        group data
       To add parameters to the parameter list using the
        ADD_PARAMETER procedure:

ADD_PARAMETER(list, key, paramtype, value);

         List: 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
                                                              75
Example ADD_PARAMETER
Procedure Call




List   Key    Paramtype   Value




                                  76
   Running the Report Using the
   RUN_PRODUCT Procedure

RUN_PRODUCT(product, document,
    communication_mode, execution_mode, location,
    parameter_list_ID, display);

     Product: application to run
         Value can be REPORTS or GRAPHICS
     Document: complete path and filename of the report .rdf file
     Communication_mode
         SYNCHRONOUS: control returns to the form only after report is
          closed
         ASYNCHRONOUS: user can toggle between report and form


                                                                     77
   Running the Report Using the
   RUN_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: report file stored in the filesystem
         DATABASE: report file stored in the database
     Parameter_list_ID: ID of the parameter list to be passed
     Display: only used when running a GRAPHICS product
         Value is always NULL when running a report
                                                                     78
Example RUN_PRODUCT
Procedure


            Product    Document            Communication
                                           Mode




Execution   Location   Parameter Display
Mode                   List ID



                                                 79
    Destroying the Parameter List
       List must be destroyed after report is called
         If list is not destroyed, an error will occur the next
          time you try to create the list again

DESTROY_PARAMETER_LIST(parameter_list_id);




                                                              80

				
DOCUMENT INFO
Description: Templates for Paper Masks document sample