Report Builder

Report is an output in specific layout

Report Builder is used to generates the reports for clients in-order analyze their business performance.

Ex:  Super Market       : Sales Report at end of the Day or Monthly / Quarterly / Yearly report.

       Company              : Employees report.

       Bank                     : Statement of the customer.

The Report Builder provides the following and ever thing is called as an object.

1)  Reports 2) Templates 3) External Libraries

4) PL/SQL libraries 5) Debug Actions

6) Stack 7) Bulilt-in Packages 8) Database Objects

Reports consist of following

1) Data Model

2) Layout Model

3) Parameter form

4) Report Triggers

5) Program Units

6) Attached Libraries

Working with Data Model and Layout Model

Data Model :

The Data Model view is a work area in which you define the data to be used in your report.  You create, define, and modify data model objects (queries, groups, columns, parameters, and links). Report Builder uses the data model to determine what data to retrieve for the report.  The data retrieved from the database may or may not appear in the report output.

Access

There are several ways that you can access the Data Model view:

            à Click the Data Model tool.

            à Choose View Data Model.

            à From the Windows menu, select a window with the Data Model in it.

            à From the Object Navigator, double-click the Data Model node's icon.

Data Model consist of following objects

1) System Parameters 2) User Parameters 3) Queries

4) Groups 5) Formula Column 6) Summary Column

7) Place holders column 8) Data Links

Layout Model

The Layout Model view is a work area, where you can define the format of your report output.  To do this, you work with layout objects, such as frames, repeating frames, fields, boilerplate, anchors, and Graphics objects. When you run a report, Report Builder uses the Layout Model as a default template for the report output.  This template will grow or shrink based on the data fetched for and the PL/SQL logic assigned to the layout objects.

Layout Model consists of following objects

1)      Header Section 2) Main Section 3) Trailer Section.

A report can be developed in two ways

1) Using Report Wizard  and 2) Manually

Steps to develop a simple report by using Report Wizard

1) Start report builder

2) Select the following as you desired

Designing :

      a) Use the Report Wizard --> to develop report automatically by report developer

      b) Build a new report manually --> to develop report manually as you desired.

      c) Open an existing report --> to open the already existing report in your system

Select Use the Report Wizard

3) Select OK Button  à Next

4) Specify the title  ex: Employees Report        --> Select the option as you desired. (  ex : select the table option )

             --->  Click on Next Button

5) Select the Sql Statement in the following

Click on Next Button

6) supply the sql query as shown in Query Statement Text box       ( select empno, ename, sal from emp )

            Click on Connect

7)Supply the User Name and Password and click on connect

Click on next button

8) Select the required fields in the following.

            >    to add fields one by one to displayed section

            > >  to add all fields at time to displayed section

            <   to remove fields one by one from displayed section

            < <  to remove all fields at time from displayed section

Click on Next button

9) Select the fields for which you would like to calculate sum, count, total..... as shown in the following

            Click on Next.

10) Edit the Labels and width in the following as you desired.

            Click on Next.  and   FINISH

For the above Data Model will be as shown below

Layout Model will be as shown below

Report Shows as below

12) Compile the above module

      Programs --> Compile --> All

      click on OK.

13) Run the module

            View -->Runtime Preview.

Steps to develop a simple report by Manually ( it is recommended in the real time projects )

Ex: Report on Employees as shown below.

                        EmpNumber                                        EmpName                                            Salary

                                   

1) Start report builder

2) Select the following as you desired

            Designing :

       a) Use the Report Wizard --> to develop report automatically by report developer

       b) Build a new report manually --> to develop report manually as you desired.

      c) Open an existing report --> to open the already existing report in your system

Select    Build a new report manually

3) Select OK Button

4) Select Sql Query shown below and drop on Data Model area.

5) supply the sql quarry as shown in Quarry Statement Text box

                        ( select empno, ename, sal from emp )

Click on OK

supply the user name and password and then connect.

            Click on OK

6) for the above data model will be as shown below.

Development of Layout Model

It consists of the three sections 1) Header Section 2) Main Section 3) Trailer Section

1) Header Section : This is to show the company title and logo and etc....

2) Main Section : It is an area where we can design the layout of the report.

3) Trailer Section : to show note  ex. Copy submitted to Managing Director

Step1 : Header Section

1) Develop the Header Section as shown below.

                                                                          

2) Select T  ( Text ) on the left side control panel and place on Layout area and then supply the     

    text as you desired as shown below.

Step2 : Main Section

1) Develop the main section as explained below

2) Select the Frame from Tool Palette and place on layout area

            --- Frame to show headers like

                        EmpNumber                           EmpName                                           Salary

3) Select the T (text) from the Tool Palette objects and place on frame of layout area as shown

    below.

4) Select Repeated Frame from Tool Palette and place inside frame ( which is on the layout area)

  

Frame will be displayed only once where as repeated frame will be displayed for each and  every record.

5) Select the Frame and right click, go to property palette and change the following

    properties

            1) Name ( optional ) --  as you desired. ex.. EMP_REPORT

            2) Vertical Elasticity -- Expand

            3) Horizontal Elasticity – Variable

6) ) Select the Repeated Frame and right click, go to property palette and change the

      following properties

            1) Name ( optional ) --  as you desired. ex.. EMP_REPORT

            2) Source -- G_EMPNO

            2) Vertical Elasticity -- Expand

            3) Horizontal Elasticity -- Variable

7) Select the text filed and place on the repeated frame under the headers as shown below

     such as  F1 , F2,   F3 ....

8) Select Text field and right click, go to property palette and set the following

            1) Name ( optional ) --  as you desired

            2) Source  --  empno ( Name of the data base table column )

9) Repeat the same procedure for all text fields

Step3: Develop the Trailer Section

2) Place text on Trailer section of Layout Model as shown below

Step 4:

Compile the module

            Program -->Compile --> All

            Click on OK

Step 5:   Run the module  :      View --> Runtime Preview

Final Report as shown below

Page1:

Page2:

           

Page3:

To Set No.of Records per Page go to repeating frame Properties

Set the Maximum Records per Page : 6  ( as desired )

Additional features

Select the desired one such as text fields, lines, text and select the desired colors ( Fill Color, Line Color, Text Color etc...)

Margin Setting :

Select the Edit Margin as shown below and set the margins on Header Section, Main Section and Trailer Sections as you desired.

when Edit Margins is being used we can not work with layout area.

to work with the layout area we must quit from the edit margins mode

click on edit margins again to quit from the edit margins mode

Default Value

value is null  then go to property palette and set value is null to required value ( default value )

ex: hiredate is null set value is null to some date ( 23-jul-2009 )

update emp set hiredate=NULL where deptno=10

Working with Summary Column :

It provides the following built-in functions

1) sum 2) average 3) minimum 4) maximum 5) count 6) first

7) last 8) % of total 9)Std. Deviation 10) variance

It can be created in two levels 1) Group Level 2) Report Level

1)      Report Level à It will be displayed only once

2)      Group Level à  It will be repeated for every record in the table

Report Level :

Ex: 1) Count of employees, 2) sum of the salaries, 3) Max Salary

Step1:

1)      Start Report Builder

2)      Select manually

3)      Select the query form tool palette of data model ( left side ) and write the required query

Ex: select empno, ename, sal, hiredate, comm from emp ( these are in the group level )

4)      Select  Summary Column form tool palette of data model and place on data model area

5)      Right click and set the properties

1) For Count of employees :

a)      Name : Emp_Count

b)      Function : Count

c)      Source : Sal

    

Repeat the same procedure for

sum of the salaries and  Max Salary as shown below

Step2:

1)      goto layout Model and design the out put layout as shown below

2) Set Properties to text fields 1) Total No.of Employees   2) Sum of the salaries  3) Maximum Salary

     Ex: Total No.of Employees --- go property palette and set the Source  ( Emp_Count )

            Repeat the same procedure for 2) Sum of the salaries  3) Maximum Salary

Step3: Compile and run

Group Above Report

Working with   Formula Column :

It can be created in two levels 1) Group Level 2) Report Level

1)   Report Level à It will be displayed only once

2)      Group Level à It will be repeated for every record in the table

Using this user can define formulas and can also write sql statements by using PL/SQL window.

Ex:  1) Average Salary  and 2) Name of the employee who is getting maximum salary

1) Average Salary

Step1:

a)      go to Data Model and Select Formula Column and place on Data Model area

b)      Set the properties

         Name :  Avg_Sal ( as desired )

         Data Type : Number

         PL/SQL Formula : Double Click and write the following

function Avg_SalFormula return Number is

begin

  return ( :Sal_Sum/:Emp_Count );

end;

        compile above coding in PL/SQL Window by using compile option

Step2:

     Go to Layout model and design the Average Salary field and Map with the Avg_Sal ( formula column )

Step3 :

   Compile and run the module.

2) Name of the employee who is getting maximum salary

 

Step1:

a)      go to Data Model and Select Formula Column and place on the  Data Model area

b)      Set the properties

Name : Max_Emp_Name ( as desired )

Data Type : Char.

PL/SQL Formula : Double Click and write the following

function Max_Emp_NameFormula return Number is

l_name varchar2(100);

begin

             select ename into l_name from emp where sal=:Max_Sal;

              return l_name;

     exception

     when too_many_rows then

     return ('duplicate emp');

     when no_data_found then

     return ('no emp');

     when others then

     return ('Unknown error');

 

end;

        compile above coding in PL/SQL Window by using compile option

Step2:

            Go to layout model and design the Name of Employees field and set Property

            Source : Max_Emp_Name

Step3 :

   Compile and run the module.

Group Level Columns

Defining group level formula column / summary column

Ex: User wish to generate the following report

Empno	EmpName	EmpSalary	EmpComm	EmpGrade
7565	Allen	115000	500	A
1287	Scott	850	150	D
3464	Ramesh	4000	400	C
7245	Smith	9000	200	B

           

If sal < =1000 then grade ‘D’

If sal >1000 and <=5000 then grade ‘C’

If sal > 5000 and <=10000 then grade ‘B’

If sal >10000 then grade ‘A’

   In the above EmpGrade is group level formula column

Step1: Data Model

            1) Query   : select empno, ename, sal, comm from emp

            2) formula column : drop the formula column into group and set properties

                        Name : Emp_Grade

                        Data Type : Char

                        Width        : 1

                        PL/SQL   formula  : write the following code

function Emp_GradeFormula return Char is

begin

  if ( :sal>=10000) then

            return ('A');

  elsif ( :sal>=5000 ) then

            return 'B';

  elsif ( :sal>=3000 ) then

            return 'C';

  else

            return 'D';

            end if;

end;

Step2:  Layout Model

            Design Layout as explained in the previous examples and set the properties of text fileds

            For grade set the source as Emp_Grade.

Parameter From

 It is used to pass the input parameters to the application in-order to generate the dynamic reports.

Parameters are of two types 1) Bind Parameters and 2) Lexical Parameters

1) Bind Parameters : These can be data types of CHAR, NUMBER, DATE. These are used to pass the input values to Query ex: in  clause Deptno= bind parameter ( which is an input ).

2) Lexical Parameters : These must be character data type. These are use to replace the string inside of   the   

                                         query in any clause.

Working with Bind Parameters

Ex : Select * from emp where deptno=(user input value )

            Enter Dept Number : 20.

Step1:

a)      Go to object navigator through Window à Object Navigator

b)      Select User parameters and create.

c)      Set the properties

Name : P_Deptno ( as desired )

Data Type : number.

Width : 2 ( as desired )

Initial value : 10 ( as desired and optional )

List of Values can also be provided:

For list of values : Double click on list of values

It provides list  in two ways 1) Static Values 2) Select Query

1)      Static Values :

Specify the value in the value box and click on add

Repeat the same no of values as desired.

                        To remove the values form the list we can use remove option.

2)      Select Statement:

Select the Select Statement option and supply the query in the Sql Query statement box.  Ex: Select deptno from dept;

Step2: go to Data Model and Double Click on   Q_1 and change the query as follows

select empno, ename, sal, hiredate, comm from emp

where deptno= : P_Deptno ( it is name of the input parameter)

Step3:  Go to Parameter form

1)      by  selecting parameter form on report builder ( 4^th icon  on  tool bar)

( or )

2)      view à select parameter form

3)      Right click and select parameter form builder. It shows as below.

Set the required properties as desired

Title :  DeptNumber ( Report Parameters )

Hint Line :  Dept Number Input   ( Enter values for the parameters)

Go to end and set the P_Deptno : Enter the Dept number ( P Deptno)

Step4:

            Compile and run the module.

          It shows as below

	10

 

            Enter the Dept Number

Two parameters

Ex: to display records of employees between range of values. ( between 10 to 40 )

	10

 

Enter the From Dept Number

	40

 

Enter the To Dept Number  

Step1:

Go to object navigator and create two parameters

Ex: P_From,   P_To

Step2:

Go to Data Model  and change the query as below.

select empno, ename, sal, hiredate, comm from emp where deptno between : P_From and : P_To

Step3:

Go to parameter form and right click and set the labels as explained in the previous example.

Working with Lexical Parameters.

These parameters must be Character  data type only

Ex :  Select * from emp where ( user input value )

        Select * from emp where deptno=20 order by sal desc , the under lined string is an user input.

	Where deptno=20 order by sal desc

 

            Enter the query string  : 

Step1:

a) Go to object navigator Select User parameters and create.

 b) Set the properties

Name : P_Query ( as desired )

Data Type : character ( mandatory ).

Width : 200 ( as desired )

Step2:   

Go to Data Model and Double Click on   Q_1 and change the query as follows

select empno, ename, sal, hiredate, comm from emp  &P_Query

here &P_Query is the name of the parameter for strings we should use & and should not use : before the parameter

Step3:

a)      Go to Parameter form Right click and select parameter form builder.

Set the required labels as explained in the previous example.

Step4:

            Compile and run the module.

          It shows as below

            Ex1:

Where deptno=20 order by sal desc

            Enter the query string  :

Ex2:

Where deptno in ( select deptno from dept ) order by sal desc

            Enter the query string  :

REPORT TRIGGERS

Report Builder provides five types of Triggers. These will be executed in a sequential manner

1)      Before parameter form

2)      After parameter form

3)      Before Report

4)      Between Pages

5)      After Report

1)      Before Parameter Form : It will be executed before invoking of the parameter form.

2)      After Parameter Form : It will be executed after closing the parameter form ( which is on user pressed enter key after supplying the input values )

3)      Before Report : It will be executed before generating the report.

4)      Between Pages : It will be executed when navigating between pages: if no.of pages are “n “ then it will be executed for ( n-1 ) times ex: if no.of pages are 5, then it will be executed for 4 times.

Reverse and second time it will not be applicable.

5)      After Report : It will be executed after quitting form the report.

These triggers will be executed weather the Parameter form developed or not.

Purpose of above five triggers in the real time scenario

Before parameter form :

1)      Used to add some built-in packages to the module.

2)      Used to initialize the form parameters.

This can tested by writing trigger as shown below.

function BeforePForm return boolean is

begin

   srw.Message(160, ‘before parameter form'); // srw == Standard report writer.//

   :P_Dptno=20; // initialization //

  return (TRUE);

end;

             similarly others can also be tested by writing above code.

After Parameter Form :

1)      Used to validate the input parameters .

Ex1:

function AfterPForm return boolean is

begin

                   if( :P_Deptno>80 ) then

srw.Message(190,'Enter the value <80');

return false;

end if ;

      return (TRUE);

end;

Ex2:

function AfterPForm return boolean is

l_Deptno number(5);

begin

select deptno into l_Deptno from dept where deptno= : P_Deptno;

return true;

exception

when others then

srw.Message(200,'invalid dept number');

return false;

      end;

Before Report :

This is similar to the After Parameter Form the difference is that, we can access the Data Model Columns before the report generation.

Between Pages: to get the page numbers dynamically. To Handle some events while moving between pages.

function BetweenPage return boolean is

begin

                                   

                  return (TRUE);

end;

      After Report : Used to log some details into table or text files.

Lexical parameters dynamically

Ex: Where clause dynamically

1) Define P_From , P_To of numeric type as bind parameter

2) Define P_Where of char type as Lexical parameter

Note : In parameter form builder select the required parameters to be displayed on parameter form.

3) Open the After Parameter Form trigger write the following code.

function AfterPForm return boolean is

begin

           

  if ( :P_From is null and :P_To is null ) then

            :P_Where:='';

  return (TRUE);

  end if ;

 

 if ( :P_From is not null and :P_To is null ) then

            :P_Where:='where deptno>=:P_From';

             return (TRUE);

 end if ;

 if ( :P_From is  null and :P_To is not null ) then

            :P_Where:='where deptno<=:P_To';

  return (TRUE);

 end if ;

 if ( :P_From is  not null and :P_To is not null ) then

            :P_Where:='where deptno between :P_From and :P_To';

  return (TRUE);

 end if ;

  return true;

end;

3)      Change the Query( Data Model ) to Select empno, ename, sal, deptno, hiredate from emp & P_Where

4)      Compile and run the module

Order by clause dynamically

Ex : Sort by

            Empno,

            Ename,

            Sal,

            Deptno.

            if Empno is supplied then order by clause will be : order by empno

if Ename is  supplied then order by clause will be : order by ename

1) Go to object navigator add one more bind parameter of char type ( P_Sort ) and supply the list of values (Empno, Ename, Sal, Deptno )

            Add one more lexical parameter to hold the order by clause.  ( P_Order )

2) Go to Parameter form and add set labels for P_Sort  input parameter and P_Order

Note : In parameter form builder select the required parameters to be displayed on parameter form.

3) Change the code in the parameter form trigger as shown below

function AfterPForm return boolean is

begin

           

            :P_Order:='order by ‘||:P_Sort;                    --added to the previous code.

  if ( :P_From is null and :P_To is null ) then

            :P_Where:='';

           

  return (TRUE);

  end if ;

 

 if ( :P_From is not null and :P_To is null ) then

              :P_Where:='where deptno>=:P_From';

             

                return (TRUE);

 end if ;

 if ( :P_From is  null and :P_To is not null ) then

            :P_Where:='where deptno<=:P_To';

           

  return (TRUE);

 end if ;

 if ( :P_From is  not null and :P_To is not null ) then

            :P_Where:='where deptno between :P_From and :P_To';

           

  return (TRUE);

 end if ;

  return true;

end;

4)      Change the Query in Data Model as shown below.

select empno, ename, sal, deptno, hiredate from emp

&P_Where

&P_Order

From table dynamically

1) Create table xx_Emp as select * from emp where deptno in (10, 20);

2) go to object navigator create lexical parameter to hold the name of the table ( P_Table) and

     create input parameter as bind parameter ( P_InTable )

3) When using from clause, the parameter it must be initialized

4) go to parameter form builder and set the lable as desired

5) Change the code in the parameter form trigger as shown below

  function AfterPForm return boolean is

begin

      :P_Table:=':P_InTable'; --added to the previous code for from clause to supply the table name.

      :P_Order:='order by :P_Sort'; -- 1st time added to the previous code for order by clause

  if ( :P_From is null and :P_To is null ) then

      :P_Where:='';

     

  return (TRUE);

  end if ;

 

 if ( :P_From is not null and :P_To is null ) then

     

                    :P_Where:='where deptno>=:P_From';

       

          return (TRUE);

 end if ;

 if ( :P_From is  null and :P_To is not null ) then

      :P_Where:='where deptno<=:P_To';

     

  return (TRUE);

 end if ;

 if ( :P_From is  not null and :P_To is not null ) then

      :P_Where:='where deptno between :P_From and :P_To';

     

  return (TRUE);

 end if ;

 return true;

end;

6)Change Sql statement as shown below.

select empno, ename, sal, deptno, hiredate from

&P_Table

&P_Where

&P_Order

7) compile and run the module

Difference between Bind Parameters and Lexical Parameters

   Bind Parameter                                           Lexical Parameters

System Parameters

1) BACKGROUND : Is whether the report should run in the foreground or the background.

2) COPIES:     Is the number of report copies that should be made when the report is printed.

3) CURRENCY: Is the symbol for the currency indicator (e.g., "$").

4) DECIMAL  : Is the symbol for the decimal indicator (e.g., "."). ( DES – Destination )

5) 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).

6) DESNAME : Is the name of the output device (e.g., the file name, printer's name, mail userid).

7) DESTYPE   : Is the type of device to which to send the report output (screen, file, mail, printer, or screen using  PostScript format).

8) MODE        : Is whether the report should run in character mode or bitmap.

9) ORIENTATION : Is the print direction for the report (landscape, portrait, default).

10) PRINTJOB : Is whether the Print Job dialog box should appear before the report is run.

11) THOUSANDS : Is the symbol for the thousand's indicator (e.g., ",").

The parameters 5 to 9 are related to output. These are very useful in real-time scenario.

Other Triggers

1)      Format Triggers

2)      Validation Triggers

3)      Action Triggers

Format Triggers :

Theses are used to hide/display the layout objects based on some condition. It is a PL/SQL function. It returns Boolean value ( true / false ). true à display,  false à hide.

Eg :

            EmpNo                        EmpName                                EmpSal                        EmpComm

Eg1: If salary is more than 3000 then, it should be displayed.

go to layout model right click on sal field and select PL/SQL Editor, write the following code

function F_3FormatTrigger return boolean is

begin

           

  function F_3FormatTrigger return boolean is

begin

           

  if ( :sal>2000) then

            return true;

  end if;

  return false;

end;

Eg2: If name of the employee more than five then, it should be displayed

Go to layout model right click on ename field and select PL/SQL Editor, write the following code

function F_2FormatTrigger return boolean is

begin

  if( length (:ename)>5) then

  return TRUE;

  end if;

  return false;

end;

Hiding Labels :

 

Ex:  at end of the repot following should be displayed, if no data found

            *********** NO Data Found ******************

1) go to Data Model and create a summary column to get the count of employees. ( Emp_Count )

2) Define the properties

            Name : Emp_Count.

Data Type : Number

Source      : Empno

3) go to Layout Model and create the following label at end

*********** NO Data Found ******************

4) right click and select the PL/SQL Editor and write the following in the code

function B_5FormatTrigger return boolean is

begin

  if ( :Emp_Count > 0)then

             return (FALSE);

  end if;

  return TRUE;

end;

for testing purpose take one input parameter and test

  ex: Enter Dept no : 90

            *********** NO Data Found ******************

a)      go to object navigator and create an user parameter ( P_Deptno )

b)      go to parameter and design as explained in the previous examples.

c)      Go to Data Model and Change query as shown velow

Select empno, ename, sal, comm from emp where deptno=:P_Deptno

5) Compile and run the module.

To hide labels like EmpNo, EmpName, EmpSal …. ( headers )

Right click on labels individually and select PL/SQL editor write the following

function B_1FormatTrigger return boolean is

begin

  if(:Emp_Count>0) then

              return (TRUE);

  end if;

  return false;

 end;

if user wish to hide the all headers then right click on the frame and write the above code

Additional Layout :

This is to display additional report.

Ex: Abstract of the report.

Step1:  Design two layouts 1) main layout 2) Additional Layout

Main layout as explained in the previous examples.

Additional layout:

1)      go to layout model and select Additional default layout, which is at the bottom of the tool palette.

Create text fields for  the summary columns on the additional layout

Step2 : Create input parameter ( P_Layout )  which takes list of values (DETAILED, SUMMARY,  BOTH )

            If selected DETAILED, then o/p should contain  only detailed report

            If selected SUMMARY, then o/p should contain  only abstract report

            If selected BOTH, then o/p  should contain   both detailed and abstract

Step3: go to detailed layout trigger and write the following

            Begin

                        If( :P_layout = ‘DETAILED’ or ‘BOTH’ ) then

                        Return true;

                        End if;

            return false;

            End;

go to abstract layout trigger and write the following

            Begin

                        If( :P_layout = ‘SUMMARY or ‘BOTH’ ) then

                        Return true;

                        End if;

            return false;

End;

Validation Triggers : 

                        To validate the parameter values. Applicable only for parameters.

This is used with individual parameters.

Steps :

            1) go to properties of parameter and select the Validation trigger then write the following code

                        Begin

                        If( :P_Deptno > 50 ) then

                                    srw.Message (155, ‘invalid input enter lessthan 50’);

                        return false;

                                    end if;

                        return true;

                        end;

same validation can be done by using after parameter form trigger. The difference is, validation triggers are used individually for each end every field where as after parameter form trigger is used to validate all fields after submitting the form.

Action triggers :

These are used with buttons . These triggers are used, when user wish to execute some PL/SQL

code on click a button.

Steps :

1)      Select a button from the tool palette and place on the layout area.

2)      Right click on button and define properties.

Name : MY_Button

Lable Text : Test.

Type : PL/SQL

PL/SQL Trigger : double click and write following and the trigger

procedure MY_BUTTONButtonAction is

begin

  srw.Message(180, 'This is is text message');

end;

SRW.RUN_REPORT (command_line CHAR); à to run the exe file

Ex:  SRW.RUN_REPORT (‘C:\EMPTEST.rep’);

.rep is an exe file à report executable program

.rdf is a source file à report definition

SRW.DO_SQL (sql_statement CHAR);

This procedure executes the specified SQL statement from within Report Builder.  The SQL statement can be DDL (statements that define data), or DML (statements that manipulate data).  DML statements can be executed directly in pl/sql coding. The said function is not necessary to execute DML statement.

Since you cannot perform DDL statements in PL/SQL, the SRW.DO_SQL packaged procedure is especially useful for performing them within Report Builder.

Note : Action Triggers are not used in the oracle apps. Only used with oracle developer ( D2k )

applications.

SRW Package functions ( see the help for more details )

SRW.ADD_DEFINITION

SRW.APPLY_DEFINTION

SRW.BREAK

SRW.CONTEXT_FAILURE

SRW.DO_SQL

SRW.DO_SQL_FAILURE

SRW.GETTERR_RUN

SRW.GET_PAGE_NUM

SRW.INTEGER_ERROR

SRW.MAXROW_INERR

SRW.MAXROW_UNSET

SRW.MESSAGE

SRW.NULL_ARGUMENTS

SRW.PROGRAM_ABORT

SRW.REFERENCE

SRW.RUN_REPORT

SRW.RUN_REPORT_BATCHNO

SRW.RUN_REPORT_FAILURE

SRW.SETattributes

SRW.TRACE_ADD_OPTION

SRW.TRACE_END

SRW.TRACE_REM_OPTION

SRW.TRACE_START

SRW.TRACEOPTS.MASK

SRW.TRUNCATED_VALUE

SRW.UNKNOWN_QUERY

SRW.UNKNOWN_USER_EXIT

SRW.USER_EXIT

SRW.USER_EXIT20

SRW.USER_EXIT_FAILURE

       

Parent and Child relation ship (or) Master and Detailed relation ship

It can be established in two ways

1)      Manually and 2) Using Data link object.

Manually:

Requirement :

DeptNo	DeptName	EmpNumber	EmpName	EmpSalary	EmpComm
10	Sales	7565	Allen	7000	500
		7415	Smith	8000	400
		1465	Rakesh	5600	300
20	Finance	6542	Sharma	7500	0
		1267	Suresh	3500
		3865	Ravi	9500	300

Ex: if all the fields are taken on same frame

Step1:

Query : Select D.deptno, D.dname, E.empno, E.ename, E.sal, E.comm from emp E, Dept D where      E.deptno=D.deptno

Step2: Design the layout model fields on the same frame as shown below

Report will be generated as shown below.

The above method is not correct

To generated as per the requirement, the application to be developed as explained in the following steps

Manually ( 1^st method )

Step1: Data Model

1) Query ( same as above )

select D.deptno, D.dname, E.empno, E.ename, E.sal, E.comm from emp E, Dept D where E.deptno=D.deptno

2) Develop the data model as shown below

             Drag the (G_Deptno group to bottom)

           

 separate the Deptno and Dname from G_Deptno group by dragging to top as shown below.

              

              

Right click on the groups and define the names of the groups as G_Dept and G_Emp

Step2: Layout Model

1)      Design a frame

2)      Inside  the frame place the Master frame ( Repeated Frame ) for dept details

3)      Inside the Master frame  place Detailed frame ( Repeated frame ) for emp details.

4)      Select the source for the master frame as G_Dept

5)      Select the source for the master frame as G_Emp

The layout model as shown below                              

Step3: Compile and run the module

The report generates as shown below

2)      Using Data Link Object

Step1 : Design the Data Model

            1) develop two queries for two groups

                        Query Q1: select * from dept

                        Query Q2: select * from emp

            2) Define the names of the groups G_Dept, G_Emp

            3) select the data link object from tool palette and establish the link Deptno in EMP Group and Dept Group

            After establishing link query will be as the previous example

select D.deptno, D.dname, E.empno, E.ename, E.sal, E.comm from emp E, Dept D where E.deptno=D.deptno

Step2 : Design the Layout Model as explained in the previous example ( manually )

Step3 : compile and run the module.

Note: Master fields can be displayed on the detailed Repeating frame but not vice versa

For the above report we can add the summary columns and formula columns

Ex: user wish to obtain the out put as shown below.

DeptNo	DeptName	EmpNumber	EmpName	EmpSalary	EmpComm
10	Sales	7565	Allen	7000	500
		7415	Smith	8000	400
		1465	Rakesh	5600	300
		7235	Ramesh	7500	200
	Total No.of Employees	4
	Sum of the Salaries	28100
	Average Salary	7000.50
20	Finance	6542	Sharma	7500	0
		1267	Suresh	3500
		3865	Ravi	9500	300
	Total No.of Employees	3
	Sum of the Salaries	20500
	Average Salary	6833.33

Step1 : Data Model

1) develop two queries for two groups

                        Query Q1: select * from dept

                        Query Q2: select * from emp

            2) Define the names of the groups G_Dept, G_Emp

            3) select the data link object from tool palette and establish the link Deptno in EMP Group and Dept Group

            After establishing link query will be as the previous example

select D.deptno, D.dname, E.empno, E.ename, E.sal, E.comm from emp E, Dept D where E.deptno=D.deptno

1)      select summary columns for Total No.of Employees ( Emp_Count )and Sum of the Salaries(Sum_Sal ) at the Dept group level

2)      right click on the summary column set properties

Name : Emp_Count

Source : Empno

Reset at : G_Dept

For salary

Name : Emp_Sal

Source : sal

Reset at : G_Dept

3)      select formula column for average salary  ( return (: Sum_Sal/: Emp_Count) at the Dept group level

4)      right click on the formula column set properties

Name : Avg_Sal

PL/SQL formula :

function Avg_SalFormula return Number is

begin

  return ( :Sal_Sum/:Emp_Count);

end;

Step2: Layout Model :

            Add the following to the Master frame;

            Total No.of Employees            :

            Sum of the salaries      :

            Average Salary                         :

Step3: Compile and run the from

Output generates as shown below

Report level columns can also be displayed on the frame

Select parent frame  ( at the end of the Tool bar of Report builder ) à used to find out the parent of the particular field ( object ) . This is a situation where the application consists many no.of frames and repeating  fames.

Flex Mode : ( on/of )

    On : the icon shows as

                                                                                       

    Off : the icon shows as              

If the flex mod is on, then parent object will be increased / decreased automatically, if child object increased / decreased.

If the flex mod is off, then there is no effect.

Confined Mode  ( lock Mode ):

If it is in lock mode, then we can not take out the child objects out of the parent objects.

If it is in unlock mode, then we can take out the child object out of the parent object and can be placed any where on the frame.

Irrespective of Lock or Unlock mode a Parent Object Can be moved Child.

Frame / Repeating Frame / Text Filed etc….. Properties.

Contract  : Means the horizontal size of the object decreases, if the formatted objects or data within it are wide enough, but it cannot increase to a width greater than that shown in the Report Editor. Truncation of data may occur; look at the examples.  (You can think of this option as meaning "only contract, do not expand.")

Expand :    Means the horizontal size of the object increases, if the formatted objects or data within it are wide enough, but it cannot decrease to a width less than that shown in the Report Editor.  (You can think of this option as meaning "only expand, do not contract.")Post settings

Fixed :   Means the width of the object is the same on each logical page, regardless of the size of the objects or data within it. Truncation of data may occur; look at the examples.  The width of the object is defined to be its width in the Report Editor.

Variable : Means the object may expand or contract horizontally to accommodate the objects or data within it (with no extra space), which means the width shown in the Report Editor has no effect on the object's width at runtime.

Program Units :

Database  Program Units ( PL/SQL Objects ) :        These are server side objects

Stored program units (also known as stored subprograms) can be compiled separately and stored permanently in an Oracle database, ready to be executed.  Once compiled and stored in the data dictionary, they are schema objects, which can be referenced by any number of applications connected to that database.

Stored program units offer higher productivity, better performance, memory savings, application integrity, and tighter security.  For example, by designing applications around a library of stored procedures and functions, you can avoid redundant coding and increase your productivity.

Stored program units are stored in parsed, compiled form.  So, when called, they are loaded and passed to the PL/SQL engine immediately.  Also, they take advantage of shared memory.  So, only one copy of a program unit need be loaded into memory for execution by multiple users.

Report Builder Program Units

                  These are client side objects. It provides the facility to create following program units.

1) Procedures 2) Functions 3) Package Spec 4) Package Body

Difference between PL/SQL Objects and Report Builder Program Units

              PL/SQL Objects               Report Builder Program Units

Program Units :

            Select Program units and Create

           

            Name : DATA_PKG  ( package name )

            Select  Package Spec

           

          Write the following code

            PACKAGE Data_PKG IS

                        procedure log_data( p_nRec number, p_msg varchar2, p_retCode out number);

                        function  validate( p_deptno number ) return boolean;

END;

Again Select Program units and Create

Name : DATA_PKG  ( package name )

            Select  Package body

            Write the following code

            PACKAGE BODY DATA_PKG IS

procedure log_data( p_nRec number, p_msg varchar2, p_retCode out number)

   as

  begin

            insert into emp_report_log values (p_nRec,p_msg);

            commit;

            p_retCode:=0;

  exception

            when others then

            p_retCode:=1;

  end;

 

 function  validate( p_deptno number ) return boolean

 as

    l_deptno number;

 begin

            select deptno into l_deptno from dept where deptno= p_deptno;

            return true;

            exception

            when others then

            return false;

 end;

 END;

Data Model Query

            select * from emp where deptno = :P_Deptno

            Take Summary Column for the Count of Employees

            Name : CS_COUNT

Write the after parameter to validate deptno

function AfterPForm return boolean is

begin

  if(DATA_PKG.validate(:P_Deptno))then

            return  true;

  end if;

  srw.Message(120,'Invalid Deptno');

            return (false); 

end;

Write the before report trigger for logging data

function BeforeReport return boolean is

l_retCode number;

begin

  DATA_PKG.log_data(:CS_COUNT, 'HELLO',l_retCode);

  if(l_retCode=0) then

            srw.Message(100, 'successfully logged');

            return (TRUE);

  end if;

  srw.Message(100, 'fail to log');

return false;

end;

Creating a procedure

1)      Select program units and create

2)      Select Procedure from the new program unit and specify the name procedure

3)      Write the following code

PROCEDURE Test IS

BEGIN

  insert into log_table 

  ( Sno, Total_Rec, SDate)

  values ( SnoSeq.nextval, :Emp_Count, sysdate );

  commit;

END;

Calling a procedure

AfterReport  Trigger

function AfterReport return boolean is

begin

  Test;  -- Calling a procedure

  return (TRUE);

end;

Attached libraries

            It is a group of program units. It can be attached any Report / Form. Attached libraries are external PL/SQL libraries that you have associated with a report or another external library.  When an external library is attached, you can reference its packages, functions, and procedures from within your report.  For example, if you attached an external library name MYLIB to your report and it contained a function named ADDXY, then you could reference ADDXY from any PL/SQL in the report.

File extensions are ====  .pll à Program link library ( source code )

                                          .plx à program link executable ( Executable code)

Place Holder Column

It is to store some value. It can be accessed across the report builder. It works like a global variable

Place holder column is used in the situation where the two values are to be returned from a function

1)      Value returned by the function 2) Value taken into the place holder.

Eg:

Empno             Ename             Salary             Dname             Grade

In this example Dname is value, which will be obtained by using place holder and

Grade will be obtained by return value of the function.

Steps to develop the above sample report

Step1 : Data Model

1)      Query

select empno, ename, sal, deptno from emp

2)      Take the Place Holder column for Dname ( P_Dname ) and define the properties

Name : P_Dname ( as desired )

Data Type : Char

Width : 100

            3) Take the formula column for Grade and define the properties

           

Name : P_Grade ( as desired )

Data Type : Char

Width : 1

PL/SQL Formula : double click and write the following code

function F_GradeFormula return Char is

begin

       select dname into :P_Dname from dept where deptno=:deptno;

      exception

      when others then

      :P_Dname := 'No DEPT';

End;          

if ( :sal>=10000) then

                  return ('A');

      elsif ( :sal>=5000 ) then

                  return 'B’;

      elsif ( :sal>=3000 ) then

                  return 'C';

      else

                  return 'D';

      end if;

end;

in the above PL/SQL program Dept Field is taken into the :P_Dname ( place holder column ) and returned a char for Grade filed.

Step2 : Layout model

            Design the layout model as explained in the previous examples and set properties

            Dname : Source = P_Dname

            Grade : Source = F_Grade

Step3 : Compile and run the module

Link file

To  link the text file to report

Ex: in trailer section of the report, data to be obtained from text file

1)      go to layout model and select the trailer section

2)      select the link file from the tool palette

3)      place on canvas

4)      define the properties

Name : as desired

Source file format : text

Source file Name : C:\Documents and Settings\Administrator\Desktop\MYFILE.txt

Vertical Elasticity : Expand

Horizontal Elasticity : Variable

Anchor :

Anchors are used to determine the vertical and horizontal positioning of a child object relative to its parent.  The end of the anchor with a symbol on it is attached to the parent object.

Eg:

Name : xxxx

S/o       : yyyyy

Street No;

Door No:

City :

State :

Country :

In the above some times street No may not be available then report should not display the null value ( empty . instead of the Street No should be replaced with Door No and continue  for remaining.

Here Street No is a parent object and Door No is a child object

Properties

Conditional Formatting : to apply the colors based on some condition

Right click on filed/frame or any object and define the conditional Formatting.

Page Protect

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:

DeptNo           Dname            Empno EmpName       Salary

10                    Sales                7565                AAAAAA        5600

                                                6215                BBBBBB         4500

                                                5423                CCCCCC        65000

20                    Marketing         3672                XXXXXX        5400

                                                1543                ZZZZZZZ        2300

                                                7524                NNNNNN      3876

In this example the report is generated that, the 3672 EmpNO of 20^th dept is displayed in the first page and remaining employees information of 20^th dept is displayed in the next page. Instead of that, all records of 20^th dept to be displayed in the next page then, Set the Page Protect to Yes.