Oracle Reports Material Part 5

CHANGES IN RUNTIME VIEW OF A REPORT

Setting a Format Mask

Ø  The most commonly used numeric format symbols are available on the style bar (In runtime) or format menu-ànumber-à see four options,

1)    Currency-----$

2)    Percentage-----%

3)    Thousand separator----0

4)    Decimal places—add and remove. ------.0

Note: Oracle Developer applies the format mask only to fields whose datatype is NUMBER.

How to Change Field and Boilerplate Font

1)    Select one or more objects.

2)    Use the font pop-up lists in the stylebar to change font face and size.

How to Modify Color Fills and Lines

1)    Select the object that you want to modify.

2)    Use the color palettes in the toolbar to change the fill, line, or text color.

Conditional Formatting (Imp)

You can highlight or suppress objects using conditional formats.

How to Create Format Exceptions

ü  Select an object.

ü  Display the pop-up menu (by right clicking of the object) and choose Conditional Formatting, or choose Format menu—>Conditional Formatting.

ü  Create a new format exception.

ü  Specify the conditions as required.

ü  Set the format attributes as required.

ü  Create further format exceptions as required.

ü  Choose OK.

Inserting Page Numbers, Current Date, and Time

Insert Page Number

ü  Use this button to add a page number to the margin of a report. Manually also.

Insert Date/Time

ü  Use this button to add the current date and time to the margin of a report.

ü  You can define your own date and time format masks.

Manually : - In Layout, Take one field along with Date label, go to properties

Name—field name, Source---Current date, Format Mask---DD-MON-RRRR. ||y Time.

How to see the Measurement of the report page

§  Goto obj.nav press  F4 on module name---àReport node-à unit of measurement.

§  It provides three options are Centimeter, Inch (Default), Point.

Report Templates

The template page of the wizard has three options for choosing a template:

v  Predefined Shows a static descriptive list of available templates

v  User-defined A user-defined template file that you specify by entering a filename or

browsing the file system

v  No Template Uses the report default settings instead of a template file.

Creating a User-Defined Template

ü  You can create your own templates from scratch, but this is not necessary, because

many prebuilt templates exist in the installation.

ü  Create your own templates by modifying one or more installed templates with your

own standards, logos, and so on, as described on the following pages.

Several templates are included in the installation. They are in

<oracle_home>\report60\admin\template\us>. The file extension for

templates is .tdf.

Note:-  Move .bmp files(200 x 150 pixels) to template directory.

ü  You can copy, create, or import various objects into the margin of the template.

• Images: File—>Import—>Image

• Text: Text tool in vertical toolbar

• Page Numbers: Toolbar button

• Date: Toolbar button.

Customizing Template Body

ü  In the template body you can modify fonts, colors, and fills of the field, label,

summary, and frame objects.

ü  You make changes directly in the Template editor or

by using the property palette and pop-up lists corresponding to the object selected.

Commenting Your Queries

ü  Use the Comment property to enter meaningful text that describes the query.

• --comment for a single line

• /*comment*/ for multiple lines.

                      Enhancing Reports Using the Data Model: Creating Queries and Groups

Ø  Query: - You can create a report with one or more queries. The Report Wizard creates a single-query report. Use the query tool or the Data Wizard to create additional queries.

Ø  Group: - Each group is owned by a query. By default, Reports creates one group for each query.

Ø  Column: - Columns are owned by the group to which they belong. By default, the group contains one column for each select expression in the query.

Ø  Link: - Links join a parent group to a child group. You can create links to form a relationship between groups from different queries. Links are never created by default.

Ø  Parameter: - Parameters are owned by the report. You can create parameters that allow users to enter value restrictions at run time. Reports also provides a number of system parameters by default.

ü  Way-1:  Manually dragging from the group in the Data Model.

Way-2:- while creating report move the group fields 

How to Create a New Query: (Another query)

1)    In the Data Model editor, select Data Wizard from the toolbar.

2)    Name the query as required.

3)    Create the query statement in one of three ways:

§  Enter the syntax of a valid select statement.

§  Choose Query Builder and build the query.

§  Choose Import SQL Query and select a .sql file.

4)    Create additional groups as required.

5)    Create totals as required.

Filtering Data in a Group

A group filter allows you to control the processing of records in each group.

Reports provides two packaged filters:

• First------Retrieves the first <n> records for the group

• Last------Retrieves the last <n> records for the group.

ü  Change Filter Type to First or Last and Enter the number of records you require (not zero). See in below Diagram.

DATABASE COLUMNS

1 Database columns.

2 Summary column at group level. (Default Name----- CS_1)

3 Formula column (Default Name----- CF_1)

4 Placeholder column (Default Name----- CP_1)

5 Summary column at reportlevel.

v  Summary column: Summarizes another column and can recalculate for each record

in a specified group.

v  Formula column: Uses a formula you have written in PL/SQL to calculate a value

from one or more other columns.

v  Placeholder column: Has its value set from another object at run time.

ü  A placeholder is a column for which you set the datatype and value in PL/SQL that you define.  You can set the value of a placeholder column in the following places:

ü  The Before Report Trigger, if the placeholder is a report-level column

a report-level formula column, if the placeholder is a report-level column

a formula in the placeholder's group or a group below it (the value is set once for each record of the group)

Formula Column

• Performs a user-defined computation

• Executes a PL/SQL function

• Must return a value

• Can be Character, Number, or Date

• Returned value must match datatype.

Editing Database Column Properties

• Name-----sal

• Column Type----summary/formula

• Datatype

• Width

Deleting a Database Column

ü  You cannot delete a database column object directly from the group.

ü  To delete a column, you must remove the corresponding expression from the SELECT

statement in the query.

Creating Layout Objects

The tool palette contains:

• Standard GUI drawing tools

• Frame, repeating frame, field objects

• Other layout objects

IMP NOTE: - within the brackets are default names.

v  And all drawing objects are boiler plates (static values).

v  Outer frame is also a frame and frames will not be visible in the runtime only borders are visible in runtime.(border is also imp in real-time ,use drawing objects for borders).

Modifying Common Layout Properties

v  Most layout objects share a number of common properties. This section explains how

to use these common properties.

The Four Layout Objects That Have Common Properties

• Frames

• Repeating Frames

• Fields

• Boilerplate Objects

Some Common Properties

ü  Sizing: Horizontal and Vertical Elasticity

ü  Pagination: Page Break Before, Page Break After, Page Protect, and Keep with

Anchoring Object

ü  Frequency of Display: Print Object On and Base Printing On

Additional Default Layout

ü  The Additional Default Layout tool invokes the wizard to enable you to create

additional layouts in your report without deleting existing objects.

How to Create an Additional Layout

1)    Choose the section.

2)    Choose the Additional Default Layout tool from the tool palette.

3)    Drag an area in the Layout Model editor defining where to place the new

objects. The Report wizard opens.

4)    Choose the Group tab. Choose the group you want and the direction to print the

records.

5)    Complete the wizard tabs as before.

Confine Mode

ü  Work with Confine Mode enabled (closed padlock) to avoid accidently

moving one object outside or below its correct enclosing object.

ü  Work with Confine Mode disabled (open padlock) when you want to move

one object outside its enclosing object.

Flex Mode

ü  Work with Flex Mode enabled to adjust all affected objects when you move

or resize one object; the whole layout flexes to accommodate your changes.

ü  Work with Flex Mode disabled when you want to move an individual object

without moving other objects.

Sizing Objects

ü  The elasticity properties allow you to specify whether the object can change size at run time.

ü  The Four Types of Object Elasticity. (IMP NOTE : - Set only for frames not for fields)

v  Fixed:- Identical to layout object size.

v  Expand:- Can be larger than layout, but not smaller.

v  Contract:- Can be smaller than layout, but not larger..

v  Variable:- Can be larger or smaller than layout.

Ø  How Wizard Report Styles Affect Elasticity

Layout Style------- Horizontal Elasticity---- Vertical Elasticity

§  Tabular---  --- Fixed ----Expand

§  Group Above-- Fixed----- Expand

§  Form----- Fixed------ Expand

§  Form Letter------ n/a------ n/a

§  Mailing Label----- n/a----- n/a

What Is a File Column?

ü  A file column is a database column that contains the name of an operating system

file or a URL.

ü  The file can be text, image, or multimedia.

How to Create a File Column

ü  Open the column property palette.

ü  Set Read from File to Yes.

ü  Select the appropriate file format from the list of valid types.

Anchors

Definition :- Anchors are used to determine the vertical and horizontal positions of the child object related to its parent.

ü  A layout object used to fix a spot on one object to a spot on another object, ensuring the position of the first object in relation to the second object.

Contrasting/differencing Implicit and Explicit Anchors

Ø  Implicit

§  Not visible in the Layout Model .

§  Created and maintained automatically byReport Builder

Ø  Explicit

§  Visible in the Layout Model.

§  Created by the developer using the anchor tool in the tool palette.

How to Compare Properties of Two Objects

ü  Select one or multiple objects and open the property palette.

Note: Selecting multiple objects results in an intersection of properties.

ü  Choose the Pin tool to “freeze” the property palette.

ü  Select the second object and open the property palette.

ü  Reposition the property palettes to view them side by side.

Controlling the Number of Records per Page

ü  You can specify the maximum number of records that you want to display on each

logical page.

ü  In the repeating frame property palette, choose the Maximum Records per Page

property. The value you enter for this property can be any positive whole number, or

blank.

ü  If this setting is 0 (zero), Report Builder formats as many records as possible on the

logical page.