Creating
Matrix Reports
When you choose the Matrix report
style, the wizard displays three new tab/wizard pages.
Ø Rows :-The field to be displayed vertically down the left side of the matrix;
you can
choose multiple levels of rows to create a vertically nested matrix
Ø Columns :-The field values to be displayed
horizontally across the top of the matrix;
you can choose multiple levels of columns to create a horizontally nested
matrix.
Ø Cell:-The field value that becomes the cell, or cross-product, of the matrix.
Creating Matrix Summaries
ü When you choose a summary in the Summary page, the wizard creates three
summaries in the matrix.
Creating a Matrix for Each Group Record
ü Choose the Matrix with Group report style. This provides a similar group
structure to
the Group Above report.
ü In your Regional report, you can select REG_NAME in the Group page so
that the
wizard structures the Department/Title matrix for each record.
The differences from a nested
matrix are:
§ Only the relevant Departments and Titles are displayed for each region.
§ If you create summaries for a Matrix with Group style, the wizard
calculates and displays summaries for each group as well as the report total.
The report total is displayed at the end of the report, in the bottom left
corner.
Enhancing
Matrix Reports
The Matrix Data Model
1) Cross product
group.
2) Row and
Column groups
3) Cell group.
4) Cell column
is the source of a cross product summary that becomes the cell content.
5) Row and
Column summaries.
ü To maintain
and enhance the structure of a matrix report, you must understand the
Data Model objects
and their interdependency.
Simple
Matrix Style: The wizard creates three data groups:
• Department records (the rows)
• Job Title records (the columns)
• Salary records (the values used to calculate the matrix
cells)
ü There is an
extra group that surrounds the row and column groups. It is called the cross product
group and contains the summary column that forms each cell value,
SumSalary,
as well as the summaries to total the row and column values.
ü To create a
cross product group, use the Cross Product tool in the vertical toolbar.
Matrix with
Group:
ü The data
model is identical, except for the addition of a break group, similar to a
master/detail hierarchy.
ü The break
group exists one level above, and outside, the matrix group.
ü The entire
cross product group, with its contents, repeats for each record in the break
group.
The Matrix Layout:
1 Repeating frame for rows (Down
Direction).
2 Repeating frame for columns. (Across)
3 Matrix object, the intersection of
the two repeating frames.
4 Matrix with Group style: Outer repeating
frame for break group.
The layout for a Matrix report (The
number of objects and their interdependency) is more complex than other styles
because of the special relationship between the row and column groups and the
cell values.
Simple
Matrix Style : The wizard creates the following objects:
• One repeating frame with Print
Direction of Down to hold the row values
• One repeating frame with Print
Direction of Across to hold the column values
• A field for the summary of the cell
values; the source is SumSalary, which
resides in the cross product group
• Fields for the row and column values,
as well as for the row, column, and report
summaries if applicable
• A matrix object that corresponds to
the cross product group; this object
intersects the row and column repeating
frames, and contains the cell field.
Matrix with
Group: The Matrix with Group style is identical except for the
addition
of a repeating frame that surrounds the
entire matrix layout and corresponds to the
break group in the Data Model.
Matrix Structure:
The Data Model and Layout Model of a
matrix report contain special features, and
relationships between objects, to
reflect the cross product structure of the report.
Matrix-Specific Objects
and Properties
Matrix reports include special objects,
and properties:
• Data Model: Cross Product group;
Product Order property
• Layout Model: Matrix object.
Creating Matrix
Summaries
1 Product Order = G_DEPT
2 Product Order = G_TITLE
3 Product Order =G_DEPT G_TITLE
When creating row or column summaries,
or the summary for the cell values, use the
summary tool in the vertical toolbar,
but ensure that you create the summaries inside
the cross product group (but outside
the row and column groups).
PL/SQL TRIGGERS IN REPORTS
The
Three Trigger Types
v Report
level:
ü A set of
five report-level triggers. Each trigger fires at a different stage of the
report execution.
ü Access
report triggers from the Report Triggers node in the Object Navigator.
v Data Model level:
ü Formula
Column fires each time the column is processed.
ü Group
filter fires for each record in the group.
ü Parameter
validation fires when the run-time parameter form is displayed and when the
user leaves the parameter field.
ü Access data
model triggers in the property palette of a data model object—column, group, or
parameter—or the corresponding layout object—field, repeating frame, or
parameter field.
v Layout level: (format and action triggers are layout
triggers).
ü Format
triggers on most layout objects (excluding anchors). Each trigger fires as the
layout object is processed.
ü Access
format triggers in the property palette of a layout object.
Note: Report
Builder does not allow data manipulation language (DML)
Commands—INSERT,
DELETE, UPDATE—in layout format triggers.
Ø Different Triggers in Report Builder : -
1)
Formula Trigger
2)
Format Trigger
3)
Validation Trigger
4)
Action Trigger
5)
Group Filter
6)
Report Trigger
VALIDATION TRIGGER:
- (validating a parameter value)
ü Validation triggers are PL/SQL functions used
to validate the value of the parameter in
a report.
ü The function that you specify as a validation
trigger must return a Boolean value (T/F) based on whether the validation is
successful or not.
ü If the function returns TRUE, the report is run. If the
function returns FALSE,
an error message is displayed and the report is not run.
In case of FALSE, the cursor remains in the
parameter and the user has
The following two options.
1.
Enter a
different value.
2.
Cancel the
report.
Note: - you can test
the value of a parameter, but you can’t change the value.
ACTION
TRIGGER: -
ü These are
pl/sql procedures executed when a button is select into the runtime previewer. These
triggers can be used to call another report dynamically.
Types of
Report Triggers in Report Builder and the sequence is ,
1)
Before Parameter Form Trigger
2)
After Parameter Form Trigger
3)
Before Report Trigger
4)
Between Pages Trigger
5)
After Report Trigger
Write
the code in following ways:
Ø Write validation trigger code for parameter (user or system)
values validation under data model in obj.nav. --------> Return type is
Boolean.
(Dbl click on parameter
under user parameter in obj nav, go to properties click on validation trigger
and write the code.)
Ø Write Group
Filter Trigger code for Group under
data model in obj.nav. --------> Return type is Boolean.
ü Dbl click
on Group (Ex:- G_VOUCHER) in data model (or) in obj.nav , goto properties, in group
node,
§ if filter
type is PL/SQL, then click on PL/SQL Filter and write code.
§ If filter
type is None, it displays all the records.
§ If filter
type is First/Last, you must provide No.
of records in property palette.
Note:- Filter
types are ------>None, First, Last, PL/SQL.
ü Except
PL/SQL type, in other three cases, PL/SQL Filter is not enabled.
Ø Write format trigger in formula cols.or database ----->
Return type is Boolean .
Ø Write formula trigger for formula cols -----------> Return
type is any.
Note :- For
database cols, dbl click on col in paper layout model, in properties,
Advanced Layout node-------> click on Format Trigger.
For Formula cols, dbl click on col in paper layout model, in
properties,
Placeholder/Formula node------> click on PL/SQL formula.
Ø Write in report triggers in obj.nav. -----> Return type is
Boolean.
Note :- Don’t
write the code in summary cols and place holder cols.
FORMULA TRIGGER CODE FOR NAME COLUMN IN FORMULA COLUMNS
Function NAMEFormula
return Char is
A VARCHAR2(200);
B VARCHAR2(200);
begin
B:=:CNAME;
IF B='NEPL' THEN
A:='NARNE ESTATES PRIVATE LIMITED';
ELSIF B='NCPL' THEN
A:='NARNE CONSTRUCTIONS PRIVATE
LIMITED';
ELSIF B='NAME' THEN
A:='NARNE AQUA MARINE ESTATES
LIMITED';
ELSIF B='MSPL' THEN
A:='MARKET SPACE SYSTEMS PRIVATE
LIMITED';
END
IF;
RETURN(A);
end;
FORMULA TRIGGER CODE FOR TITLE COLUMN
Function
TITLEFormula return Char is
A VARCHAR2(20);
B VARCHAR2(20);
C DATE;
D DATE;
F VARCHAR2(20);
E VARCHAR2(200);
begin
A:=:CNAME;
C:=:STDT;
D:=:EDDT;
F:=:BNAME;
E:=A||'
'||F||' BANK BOOK'||' PAYMENT DETAILS FROM '||TO_CHAR(C,'DD/MM/YYYY')||' TO
'||TO_CHAR(D,'DD/MM/YYYY');
RETURN(E);
end;
FORMULA TRIGGER CODE FOR CF_3 COLUMN
function CF_3Formula
return Number is
x
number(20);
begin
if :debit>:credit then
x:=:debit-:credit;
else
x:=null;
end if;
return
x;
end;
Formula
Trigger for Formula column BAL
function
BALFormula return Char is
A DATE;
B
VARCHAR2(50);
begin
A:=:TDT;
B:='Balances as on'||'
'||TO_CHAR(A,'DD/MM/YYYY');
RETURN(B);
end;
Example of
Foramt trigger to ENAME(database) column.
Function
F_4 Format Trigger return Boolean is
Begin
If
length(:ename)>4 then
Return(TRUE);
Else
Return(FALSE);
End if;
End;
Example of
Format Trigger on Ename col ( hide the 3 emps based on condition).
Steps : -
ü In obj,nav
create 3 user parameters (hide 3 enames).
ü Goto
properties of each parameter.
ü Name---Pname1,
Datatype---char, list of values----select ename from emp;
ü In layout
editor, dbl click on ename col write format trigger code,
Begin
If :ename in(:pname1,:pname2,:pname3)
then
Return (false);
Else
Return (true);
End if;
End;
Example of
validation trigger to p_dno parameter.
SQL QUERY : - Select empno, ename, sal,
deptno from emp where deptno=:p_dno;
function
DNOValidTrigger return boolean is
begin
If
:p_dno in(10,20,30) then
Return(TRUE);
Else
Srw.message(10,’please enter valid number’);
Return(FALSE);
End if;
End;
IMP NOTE :- SRW.message
is accepts the error number and error message.
VALIDATION
TRIGGER EXAMPLE
Isbegin IF UPPER(:DESTYPE)
= 'PRINTER' AND
:LAST_NAME_PARAM = 'SMITH' THEN
RETURN(TRUE); ELSE
RETURN(FALSE); END
IF;end;
Group
Filter Trigger for group G_VOUCHER
If :comm.
Is null then
Return (TRUE);
Else
Return (FALSE);
End if;
End;
Setting the
border colors to the cols based on the condition.(psal is a parameter)
Begin
If
:sal=:psal then
Srw.set_foregroun_border_color(‘red’);
Srw.set_border_pattern(‘solid’);
Srw.set_foreground_fill_color(‘magenta’);
Srw.set_fill_pattern(‘solid’);
End if;
Return(false);
End;
Good article
ReplyDeletePlease disable the ( type the text to verify) option . Otherwise nobody will give feedback
ReplyDeleteThanks for ur suggestion
DeleteNice Blog
ReplyDelete