Oracle Reports FAQ's
Oracle Reports FAQ's
1.What is SRW
Package?
Ans: The Report builder
Built in package know as SRW Package (Sql Report Writer) This package extends
reports, Control report execution, output message at runtime, Initialize layout
fields, Perform DDL statements used to create or
Drop temporary table, Call User Exit, to format width of
the columns, to page break the column, to set the colors
Ex: SRW.DO_SQL, It’s like
DDL command, we can create table, views , etc.,
SRW.SET_FIELD_NUM
SRW. SET_FIELD_CHAR
SRW. SET FIELD _DATE
2.What are Lexical
Parameters and bind parameters?
Lexical Parameter is a
Simple text string that to replace any part of a SELECT
statement. Column names, the from clause, where clause or the order by clause.
To create a lexical reference in a query we prefix the parameter name with an
ampersand (ex. &.dname,)
A parameter, which is
created by user. For to restrict values with where clause in select statement.
Data type, width, input
mask, initial value, validation trigger, list of values
We can use Lovs in use in
user parameter with static and Dynamic Select Statement.
BACKGROUND: Is whether the
report should run in the foreground or the background.
COPIES Is
the number of report copies that should be made when the report is printed.
CURRENCY Is
the symbol for the currency indicator (e.g., "$").
DECIMAL Is
the symbol for the decimal indicator (e.g., ".").
DESFORMAT Is the definition
of the output device's format (e.g., landscape mode for a
printer). This
parameter is used when
running a report in a character-mode environment, and when
sending a bitmap
report to a file (e.g. to create PDF or HTML output).
DESNAME Is
the name of the output device (e.g., the file name, printer's name, mail
userid).
DESTYPE Is
the type of device to which to send the report output (screen, file, mail,
printer, or
screen using PostScript
format).
MODE Is
whether the report should run in character mode or bitmap.
ORIENTATION Is the print
direction for the report (landscape, portrait, default).
PRINTJOB Is
whether the Print Job dialog box should appear before the report is run.
THOUSANDS Is the symbol for
the thousand's indicator (e.g., ",").
Tabular form-like form
– letter Group
left
Group
above matrix Matrix
with group Mailing label
Simple Matrix Report
required 4 groups
1.Cross Product Group
2. Row and Column Group
3. Cell Group
4. Cell column is the source
of a cross product summary that becomes the cell content.
Frames: 1.Repeating frame
for rows (down direction)
2.Repeating frame for
columns (Across)
3.Matrix object the
intersection of the two repeating frames
6.What Types of
Triggers are Available in
Reports.
§ Report level Triggers
§ Data Model Triggers
§ Layout Model TriggersBefore parameter form: If u want take
parameters passed to the report and manipulate them so that they appear
differently in the parameter form., this is where modification can be done for
ex: when u want pass a deptno but show the dname selected , use a before
parameter form trigger.Between
pages: This
Trigger fires before all pages except first page one. It will not
fire after the last page of a report. If a report only
has one page it will not fire at all. You can use this trigger to send specific
control to the change the paper orientation or to do double sided printingData
Model Triggers Layout
Model Triggers Format triggers
enable you to modify the display of objects dynamically at run time or to
suppress display altogetherTo format a column based on certain
criteria for
exampleii) To
format the Sal column with a Dollar($) prefix.8. What is Data
Model? 9. What is
Layout model? 10 What is
Livepreviewer? To activate buttons in the
Live Previewer, you must display the report output in the Runtime
Previewer. In order to edit your report, such as changing column
size ,move columns, align columns insert page numbers, edit text, change
colors, change fonts set format masks, insert field the Live
Previewer must be in Flex Mode.TitleRulersToolbarTool palette11. What is Parameter Form The Parameter Form view is
the work area in which you define the format of the report's Runtime Parameter
Form. To do this, you define and modify parameter form objects
(fields and boilerplate).12. What is Query? 13. What is
Group? 14
What is Repeating
Frame? Repeating
frames correspond to groups in the data model. Each repeating frame
must to be associated with a group of data model The repeating frame
prints (is fired) once for each record of the group.A ref cursor query uses
PL/SQL to fetch data. Each ref cursor query is associated with a
PL/SQL function that returns a strongly typed ref cursor. The
function must ensure that the ref cursor is opened and associated with a SELECT
statement that has a SELECT list that matches the type of the ref
cursor.n more easily administer
SQLn share datasources with
other applications, such as Form
Buildern encapsulate logic
within a subprogram16. What
is Template? Creation
of Template: In Report editor , open a existing Template or Create a new
Template and save it concerned directory. Then Edit CAGPREFS.ORA
File , and Specify which type of Template are u going to develop.Develop Report
with Newly developed Template.
§ Ex. Tabular, form,
matrix Then give your developed
template *.tdf file name.
§ Ans: Templates define
common characteristics and objects that you want to apply to multiple
reports. For example, you can define a template that includes the
company logo and sets fonts and colors for selected areas of a report. And
properties of the objects also
§ Furthermore, if you use a
stored program unit to implement ref cursors, you receive the added benefits
that go along with storing your program units in the Oracle database.
§ n increase
control and security
§ n avoid
the use of lexical parameters in your reports
§ You base a query on a ref
cursor when you want to:
§ Ans: Repeating frames
surround all of the fields that are created for a group’s columns.
§ Ans: Groups are created to
organize the columns in your report. When you create a query, Report
Builder automatically creates a group that contains the columns selected by the
query. You create additional groups to produce break levels in the
report, either manually or by using the Report Wizard to create a group above
or group left report.
§ The first thing in data
model is the query. Through query we access database objects with
sql query. Compiled query creates groups. We can create query through query
builder, sql query and import query from o/s file or database.
§ When you run a report,
Report Builder uses the Parameter Form view as a template for the Runtime
Parameter Form. Fields and boilerplate appear in the Runtime
Parameter Form exactly as they appear in the Parameter Form
view. If you do not define a Runtime Parameter Form in the
Parameter Form view, Report Builder displays a default Parameter Form for you
at runtime.
§ Ans: Parameters are
variables for report that users can change at runtime immediately prior to the
execution of the report. You can use system parameters to specify aspects of
report execution, such as the output format, printer name , mailed or number of
copies. We can also create own parameters through sql or Pl/sql at runtime.
§ Status bar
§ Style bar
§ Grid
§ Viewing region
§ Access
§ Ans: The Live Previewer is
a work area in which you can preview your report and manipulate the actual, or
live data at the same time. In the Live Previewer you can customize
reports interactively, meaning that you can see the results immediately as you
make each change.
§ Layout Model is to
Physically arrange Data model group objects on the Report. The Report Editor's
Layout Model view enables you to define and modify the layout model objects for
a report. In this view, objects and their property settings are
represented symbolically to highlight their types and relationships.
§ Data Model is logically
group of the Report Objects through query and Data model tools . Once query is
compiled report automatically generates group. The queries build the groups ant
then Groups are used to populate the report. The only function of
queries in report is to create the groups. The Report Editor's Data
Model view enables you to define and modify the data model objects for a
report. In this view, objects and their property settings are
represented symbolically to highlight their types and
relationships. To create the query objects for your data model, you
can use the Report Wizard, Data Wizard, or the Query tools in the tool palette.
§ iii) To
format Date formats….etc
§ i) To format
the max(Sal) for particular department.
§ For Headings, for repeating
frames, for field, for boilerplate object
§ 7. What is Format triggers.
§ Formula Column, Group
Filter, Parameter values
§ After report: This trigger fires
the report has printed or in the case of a screen report, after the report is
closed following viewing. This trigger can be used to update a global variable
if u r returning the number of pages in a report. It is also used to delete
temporary table used to print the report
§ After parameter form & Before Report: These two triggers
are fired one after the other. No event occurs in between them. However the way
the way that the reports product behaves when the triggers fail is quite
different. If the After Parameter trigger fails the report will be put back
into the parameter form. It’s useful to place code here to check whether values
in your parameter form are valid. Even though the Before Report trigger is
executed before the query runs, if this trigger fails it won’t fail until
reports tries to display the first page of the report. This means that even if
something goes wrong in the before report trigger (meaning that you may not
want to run the query at all) It will run anyway
Confine mode
On: child
objects cannot be moved outside their enclosing parent objects.
Off: child
objects can be moved outside their enclosing parent objects.
Flex mode:
On: parent
borders "stretch" when child objects are moved against them.
Off: parent
borders remain fixed when child objects are moved against them.
Ans: To limit the records
per page.
Ans: The Page Protect
property indicates whether to try to keep the entire object and its contents on
the same logical page. Setting Page Protect to Yes means that if the
contents of the object cannot fit on the current logical page, the object and
all of its contents will be moved to the next logical page. Ex: if you set yes,
the object information print another page.
The print condition type
First, All, All but first, Last, All but last refer to the frequency with which
you want to appear based upon the setting of the print condition object. A
print condition object of Enclosing Object is whichever object encloses the
current object (could be the parent or a frame within the parent), while
Anchoring Object is the parent object (unless you have explicitly anchored the
object in which case it is the object to which it is anchored). The key here is
that this is about the pages on which the Print Condition Object appears, not
the current object. Oracle views First as the first page on which any part of
the Print Condition Object is printed, likewise Last is the last page on which
any part of the Print Condition Object is printed. For objects inside a
repeating frame, this condition is re-evaluated for each instance of the frame.
Ans: The Print Direction
property is the direction in which successive instances of the repeating frame
appear.
21 What is Vertical and
Horizental Elacity
Ans: The Horizontal
Elasticity property is how the horizontal size of the object will change at
runtime to accommodate the objects or data within it:
Ans: A placeholder is a
column is an empty container at design time. The placeholder can hold a value
at run time has been calculated and placed in to It by pl/sql code from anther
object. You can set the value of a placeholder column
is in a Before Report trigger , A report level formula
column(if the place holder column is at report level) A formula column
in the place holder group or a group below it
Uses of place holder columns
enables u to populate multiple columns from one piece of code. U can calculate
several values in one block of pl/sql code in a formula column and assign each
value into a different placeholder column. U therefore create and maintain only
program unit instead of many.
Store a Temporary value for
future reference. EX. Store the current max salary as
records are retrieved.
23 What is Formula Column?
Ans: A formula column
performs a user-defined computation on another column(s) data, including
placeholder columns.
Ans: A summary column
performs a computation on another column's data. Using the Report Wizard
or Data Wizard, you can create the following summaries: sum,
average, count, minimum, maximum, % total. You can also create a
summary column manually in the Data Model view, and use the Property Palette to
create the following additional summaries: first, last, standard
deviation, variance.
Ans: Boilerplate is any
text or graphics that appear in a report every time it is
run. Report Builder will create one boilerplate object for each
label selected in the Report Wizard (it is named B_
Column
name). Also, one boilerplate object is sometimes created for each
report summary. A boilerplate object is owned by the object
surrounding it, unless otherwise noted.
26 What is Data Link
When we join
multiple quires in a report the join condition is stored in the data
link section
Data links relate the
results of multiple queries. A data link (or parent-child
relationship) causes the child query to be executed once for each instance of
its parent group. When you create a data link in the Data Model view
of your report, Report Builder constructs a clause (as specified in the link's
Property Palette) that will be added to the child query's SELECT statement at
runtime. You can view the SELECT statements for the individual parent and
child queries in the Builder, but can not view the SELECT statement that
includes the clause created by the data link you define.
27 What is filter and Group
Filter
28.What is Query
Builder
Ans: it’s a gui tool to
build a query in Report Wizard, Data Wizard or Data model.
Ans: We can break a column
through data model , it Display once for a
group
Ans: RUN_PRODUCT and RUN_REPORT_OBJECT
40. HOW CAN U CREATE TWO FORMATS
USING DISTRIBUTION WE CAN
CREATE DIFFERENT FORMATS
45 HOW TO DISPLY ONE RECORD PER PAGE (
WHICH PROPERTY WE SHOULD SET)
Set Repeating Frame
Properties : Maximum records per page=1 And it will override group filter
property.
In Data model
Layout , Group Property Through Filter Type & No of
records to display
Property, Values are First,
last, pl/sql
Header: The header consist of
on e or more pages that are printed before report proper. The type of
Information you might want
to print title of the page ,company logo and address or chart the
Summarizes the report.
Trailer: The trailer consists
of one or more pages that print after the report itself, usually used for
nothing more than an end of report blank page, but also used for a report
summary or chart.
Body: The body is where all
the main report objects are placed
Margin: the report layout
only governs the part of the pages designated for the main data portion of the
report. The margins are can be used to specify page headers and page footers.
49. what are Executable file definitions in
Reports
Report Builder
(RWBLD60.EXE)
n Reports
Runtime (RWRUN60.EXE)
n Reports
Convert (RWCON60.EXE)
n Reports
Background Engine (RWRBE60.EXE)
n Reports
Server (RWMTS60.EXE)
n Reports
Web Cartridge (RWOWS60.DLL)
n Reports
CGI (RWCGI60.EXE)
n Reports
Queue Manager (RWRQM60.EXE)
n Reports
Launcher (RWSXC60.EXE)
n Reports
ActiveX Control (RWSXA60.OCX)
Aggregated Information,
Calculated information, A string Function
Can I highlight and change all the format
masks and print conditions of a bunch of fields all at once?
You can. If you highlight a
bunch of objects and then right click and select "properties..",
Oracle gives you a stacked set of the individual properties forms for each of
the selected objects. While this may be useful for some things, it requires
changing values individually for each object. However, instead you can select
the group of fields and then select "Common properties" from the
"Tools" menu which will allow you to set the format mask , print
conditions etc. for the whole set of objects at once.
How do I change the printed value of a
field at runtime?
Triggers are intended to
simply provide a true or false return value to determine whether an object
should be printed. It is generally not allowed to change any values held in the
cursor, make changes to the database, or change the value of it's objects value.
That being said, there is a
highly unpublicized method of doing just that using the SRW.Set_Field_Char
procedure.
The syntax is
SRW.Set_Field_char (0,) and the output of the object that the current trigger
is attached to will be replaced by .
There are also
SRW.set_fileld_num and SRW.set_field_date for numeric or date fields.
While these options do work, they should only be used if a suitable NVL or
DECODE statement in the original query is not possible as they are much, much
slower to run. Also, note that this change of value only applies to the
formatted output. It does not change the value held in the cursor and so can
not be used for evaluating summary totals
The capability of producing
multiple copies of a given report or portion of it in different output formats
is referred to as report bursting.
Additional layout created
for to different format using same query and groups
without modifying default layout created by report wizard., we can use
both layouts according to user requirement.
Ans: Current date, Page
Number, Panel number, Physical Page Number, Total Pages,
Total Panels, Total
Physical Pages.
Link
File : Is a special type of boilerplate, that doesn’t have to
remain constant for each report run
The type of file
contents, can be Text, Image, CGM, Oracle drawing format, or image
URL
Source filename :the
name of the file the u want link to the report Through import Image
from
