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;
0 comments:
Post a Comment