Dynamic Report

If you have many reports that deal with the same table.  You should  create Dynamic report.

Assume that you are required to write  three reports on EMP.

The first lists all employees by  employee number,

The Second lists all employees by Employee name,

and the Third lists all employees by Salary.

Instead of writing three different reports, you must consider writing one Dynamic reports as follows

1-  In the Navigator, Create user parameter (called lexical) with the  following properties   (Do not forget the Default values)

          Data Type  :  CHARACTER  (all lexical parameters  must be of type Character) 

          Name                 :   P1

          Default   Value :   ENAME    (The default value must be one of the character columns )   

           Data Type  :  CHARACTER  (all lexical parameters  must be of type Character) 

          Name                 :   P2

          Default   Value :   ENAME    (The default value must be one of the character columns )   

          Data Type  :  CHARACTER  (all lexical parameters  must be of type Character) 

          Name                 :   P3

         Default   Value :   ENAME    (The default value must be one of the character columns )   

2- Create your SQL  as follows

          Select  &P1   col1 , &P2  col2,  &P3 col3 , job, mgr, hiredate  ...  from emp

           order by 1

Note that col1, col2, col3  are aliases and will appear as the header of the report

3- Create your default layout as usual

When you run your report,  the report will show the Parameter Form with the 3 three  parameters  P1, P2 and P3.  with default values of Ename appearing.  If  you run the report as is, you will get the first field equal to Ename,  we will clear these fields so that the user is forced to fill

 In the navigator choose the Before parameter report trigger

 BEGIN

   :p1 := null;

   :p2 := null;

   :p3 :=null;

END;

4- You can also use the After Parameter trigger to fill default value if the user leaves any of the fields NULL. It is suggested that you include an empty dummy field in the TABLE, then enter the name of this field as a default for all the field that are left blank

 PASSING RECORD GROUP TO A REPORT

1-Design A REPORT called emp_rec. Use the following SELECT statement

                SELECT  * FROM EMP

2-Give the Query the Following Name

               EMP_QUERY

3-Design a Form with a push button that will call the report.

4- Write the following when-button-pressed trigger

declare

  pl_id ParamList;

  x recordgroup;

  y number;

BEGIN

  pl_id := Get_Parameter_List('tmpdata');

  IF NOT Id_Null(pl_id) THEN

    Destroy_Parameter_List( pl_id );

  END IF;

  -- Create the 'tmpdata' parameter list afresh.

  pl_id := Create_Parameter_List('tmpdata');

  /*

  ** Add a data parameter to this parameter list that will

  ** establish the relationship between the named query

  ** 'EMP_QUERY' in the report, and the record group named

  ** 'EMP_RECS' in the form.

  */

  x:=create_group_from_query('EMP_RECS','select * from emp');

  y:=populate_group('EMP_RECS'); 

Add_Parameter(pl_id,'EMP_QUERY',DATA_PARAMETER,'EMP_RECS');

-- Note: It is important to use 'EMP_QUERY' after pl_id as it is the

--       the name of the query in the report.  The record group result

--       will be passed to the EMP_QUERY and therefore, the report

--       will not re-execute the query

  /*

  ** Run the report synchronously, passing the parameter list

  */

  Run_Product(REPORTS, 'c:\emp_rec', SYNCHRONOUS, RUNTIME,

           FILEsYSTEM, pl_id, NULL);

END;