Oracle Reports

Anchor in Reports:

      Anchors are used to determine the vertical and horizontal positioning of a child object relative to its parent.

      Since the size of some layout objects may change when the report runs (and data is actually fetched), you need anchors to define where you want objects to appear relative to one another. An anchor defines the relative position of an object to the object to which it is anchored. Positioning is based on the size of the objects after the data has been fetched rather than on their size in the editor. It should also be noted that the position of the object in the Layout editor effects the final position in the report output. Any physical offset in the layout is incorporated into the percentage position specified in the Anchor property sheet.

Summary Column:

      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.

Formula Column:

      A formula column performs a user-defined computation on another column(s) data, including placeholder columns. Formula Columns should not use to set values for the Parameters.

Placeholder Column:

      A placeholder is a column for which you set the data type and value in PL/SQL that you define. You can set the value of a placeholder column in the following places:

  • the Before Report Trigger, if the placeholder is a report-level column
  • a report-level formula column, if the placeholder is a report-level column
  • a formula in the placeholder's group or a group below it (the value is set once for each record of the group)

Format Trigger:

      A format trigger is a PL/SQL function executed before an object is formatted. A trigger can be used to dynamically change the formatting attributes of the object.

      The function must return a Boolean value (TRUE or FALSE). Depending on whether the function returns TRUE or FALSE, the current instance of the object is included or excluded from the report output. You can access format triggers from the Object Navigator, the Property Palette, or the PL/SQL Editor.

Note 1:

      Format triggers do not affect the data retrieved by the report. For example, if a format trigger returns FALSE for a field, the data for the field is retrieved even though the field does not appear in the output.

Note 2:

      If a format trigger suppresses report output on the last page of the report, the last page will still be formatted and sent to the appropriate output and the page will be included in the total number of pages.

Validation Trigger:

      Validation triggers are PL/SQL functions that are executed when parameter values are specified on the command line and when you accept the Runtime Parameter Form. (Notice that this means each validation trigger may fire twice when you execute the report.) Validation triggers are also used to validate the Initial Value property of the parameter. The function must return a Boolean value (TRUE or FALSE). Depending on whether the function returns TRUE or FALSE, the user is returned to the Runtime Parameter Form. You can access validation triggers from the Object Navigator, the PL/SQL Editor, or the Property Palette (Validation Trigger property).

      Definition Level: parameter

      On Failure: user is returned to the parameter value in the Runtime Parameter Form where they can either change it or cancel the Runtime Parameter Form.

Action Trigger:

Description Action triggers are PL/SQL procedures executed when a button is selected in the Runtime Previewer. The trigger can be used to dynamically call another report (drill down) or execute any other PL/SQL. You can access action triggers from the Object Navigator, the Property Palette (PL/SQL Trigger property), or the PL/SQL Editor.

Definition Level: button

Usage Notes:

  • PL/SQL action triggers cannot be tested in the Live Previewer because the buttons are not active there. You must use the Runtime Previewer (choose View Runtime Preview from the Live Previewer).
  • You cannot use the PL/SQL Interpreter to debug action triggers because it is not available from the Runtime Previewer and buttons cannot be activated in the Live Previewer. To get around this, you can move your action trigger code to a report trigger to test it from the Live Previewer.

Action trigger example:

/* When the button is clicked, the action trigger defined by this procedure displays the Parameter Form for a report named web4.rdf, then displays the resulting report output on your screen.*/
      Procedure U_1ButtonAction is
      Begin
      Srw.run_report(’module=web4.rdf destype=Screen paramform=yes’);
      End;

Lexical references:

      Lexical references are placeholders for text that you embed in a SELECT statement. You can use lexical references to replace the clauses appearing after SELECT, FROM, WHERE, GROUP BY, ORDER BY, HAVING, CONNECT BY, and START WITH.

      You create a lexical reference by entering an ampersand (&) followed immediately by the column or parameter name. A default definition is not provided for lexical references. Therefore, you must do the following:

  • Before you create your query, define a column or parameter in the data model for each lexical reference in the query. For columns, you must enter Value if Null, and, for parameters, you must enter Initial Value. Report Builder uses these values to validate a query with a lexical reference.
  • Create your query containing lexical references.

      Ex: SELECT &P_ENAME NAME, &P_EMPNO ENO, &P_JOB ROLE FROM &Table_name

Bind references / Bind Variables:

      Bind references (or bind variables) are used to replace a single value in SQL or PL/SQL, such as a character string, number, or date. Specifically, bind references may be used to replace expressions in SELECT, WHERE, GROUP BY, ORDER BY, HAVING, CONNECT BY, and START WITH clauses of queries. Bind references may not be referenced in FROM clauses or in place of reserved words or clauses.

      You create a bind reference by entering a colon (:) followed immediately by the column or parameter name. If you do not create a column or parameter before making a bind reference to it in a SELECT statement, Report Builder will create a parameter for you by default.

How to perform DDL Statements in Oracle Reports?

      Using srw.do_sql, u can perform DDL Statements. We cannot use Execute immediate in Oracle Reports.

      Ex: srw.do_sql ('Create table jdm_test (p_empno Number, p_ename Varchar2 (40), p_sal Number)');

How to run a report with in a Report?

      Using Srw.run_report

      Ex: srw.run_report('module=web4.rdf destype=Screen paramform=yes');

      SRW.DO_SQL_FAILURE:

      /* Suppose you want your own error message raised, ** instead of the default error message. ** You could handle this exception in the following way: */

      EXCEPTION
      when SRW.DO_SQL_FAILURE then
      srw.message(1000, 'Error occurred while creating
      table CHECKS.');

What are the different Layout objects?

Frames

      Frame surrounds other layout objects, enabling control of multiple objects simultaneously, ensuring that they maintain their positions relative to each other in the report. A frame might be used to surround all objects owned by a group to surround column labels. Frame fires only once.

Repeating Frames

      Repeating frames act as placeholder for groups (repeating values) and present rows of data retrieved from the database. The repeating frame is fired once for each record of the group.

Fields

      Fields act as placeholder for column values. They define the formatting attributes for all columns displayed in the report. A field is one of the objects that can be located inside a frame or repeating frame.

Boilerplates

      Boilerplates consist of text and graphics that appear in a report each time it is run. Ex: A leabl appearing above a column of data is Boiler text. Graphics drawn in the layout as well as text added to the layout are boilerplate.

What is data model?

      specify the data for the report, a data model should be defined. A data model is composed of some or all of the following data definition objects.

1. Query

      Queries are ANSI-standard SQL Select statements that fetch data from a standard database such as Oracle, DB2 etc. These select statements are fired each time the report is run. You can select any number of queries to select data from any number of tables.

2. Groups

      Groups determine the hierarchy of data appearing in the report and are primarly used to create breaks in the report. Oracle report automatically creates a group for each query, but you are not limited to this default. You can create a new group in the data model and included a column that you want ti use as the break column.

3. Column

      Column contains the data values for a report. Default report columns, corresponding to the tables included in the query’s SELECT list are automatically created by the table, Oracle Report, then each column is replaced in the group associated with the query that selected the column. If you want to perform summaries and computations on the database column values you can create new columns. You can also reassign one or more columns to a group you have created.

4. Parameters

      Parameters are visible for your report that enables you to change selection criteria at runtime. Oracle report automatically creates a set of System Parameters a t runtime, but you can create your own as well. You can create parameters to replace either single literal values or entire expressions in any part of a query. You can reference parameters elsewhere in the report such as PL/SQL constructs providing conditional logic for the report.

5. Data Link

      Data Link is used to establish parent-child relationship between queries and groups via column matching.

What is Group Filter?

      A group filter is a PL/SQL function that determines which records to include in a group, if the Filter Type property is PL/SQL. The function must return a boolean value (TRUE or FALSE). Epending on whether the function returns TRUE or FALSE, the current record is included or excluded from the report. You can access group filters from the Object Navigator, the Property Palette (the PL/SQL Filter property), or the PL/SQL Editor.

Definition Level: group

On Failure: Excludes the current record from the group.

Group filter example

function filter_comm return boolean is
begin
       if :comm IS NOT NULL then
             if :comm < 100 then
                   return (FALSE);
             else
                   return (TRUE);
             end if;
       else
             return (FALSE); -- for rows with NULL commissions
       end if;
end;

Group filter Restrictions

  • Group filters cannot be added to groups if Filter Type is First or Last.
  • Group filters cannot be added to cross-product groups.
  • In a group filters, you can read the values of Report Builder columns and parameters of the correct frequency (look at the rule below), but you cannot directly set their values. For example, you can use the value of a parameter called COUNT1 in a condition (e.g., IF :COUNT1 = 10), but you cannot directly set its value in an assignment statement (e.g., :COUNT1 = 10). Note also that the use of PL/SQL global variables to indirectly set the values of columns or parameters is not supported. If you do this, you may get unpredictable results. You also cannot reference any page-dependent columns (i.e., Reset At of Page) or columns that rely on page-dependent columns in a group filter.
  • The function that you enter for a group filter can only depend upon the following columns:
    1. A database column owned by the group’s query or a query above it in the data Model hierarchy
    2. Computed columns (formulas or summaries) that depend on unrelated queries (i.e., computed columns that do not depend upon columns in the group, the group’s ancestors, or the group’s descendants)

Oracle Reports 6.0 Flexfield Support API:

      Using Oracle Applications flexfields routines with Oracle Reports, you can build reports that display flexfields data easily and in a number of ways:

  • Display any individual segment value, prompt, or description.
  • Display segment values, prompts, or descriptions from multiple flexfield structures (or contexts) in the same report.
  • Display segment values, prompts, or descriptions from different flexfields in the same report.
  • Display two or more flexfield segment values, prompts, or descriptions, concatenated with delimiters, in the correct order. This includes description information for dependent,
  • Independent, and table validated segments.
  • Restrict output based upon a flexfield range (low and high values).
  • Prevent reporting on flexfield segments and values that users do not have access to (flexfield value security).
  • Specify order by, group by, and where constraints using one or more, or all segment columns.

Oracle Reports and Flex fields Report–Writing Steps:

      

Step 1: Define your Before Report Trigger (this step is always the same)

      You always call FND SRWINIT from the Before Report Trigger:
            SRW.USER_EXIT (’FND SRWINIT’);

      This user exit sets up information for use by flex fields, user profiles, the concurrent manager, and other Oracle Applications features. You must include this step if you use any Oracle Application Object Library features in your report (such as concurrent processing).

Step 2: Define your After Report Trigger (this step is always the same)

      You always call FND SRWEXIT from the After Report Trigger:
            SRW.USER_EXIT (’FND SRWEXIT’);

      This user exit frees all the memory allocation done in other Oracle Applications user exits. You must include this step if you use any Oracle Application Object Library features in your report (such as concurrent processing).

Step 3: Define your required parameters

      You define the parameters your report needs by using the Data Model Painter. You use these parameters in the user exit calls and SQL statements.

      You must always create the P_CONC_REQUEST_ID lexical parameter. ”FND SRWINIT” uses this parameter to retrieve information about the concurrent request that started this report. The P_FLEXDATA parameter holds the SELECT fragment of the SQL query. The initial value is used to check the validity of a query containing this parameter and to determine the width of the column as specified by the column alias. Its initial value is some string that contains columns with a cumulative width more than the expected width required to hold the data. Make sure the width of this column is sufficient. If there are total 30 segments in the table then the safest initial value will be:
      (SEGMENT1||’\n’||SEGMENT2||’\n’||SEGMENT3 ...
      SEGMENT30)

      You determine the width by determining the length of that string. That length is roughly the number of characters in the table alias plus the length of the column name, times the number of segments your code combinations table contains, times the number of structures you expect,plus more for delimiter characters as shown in the string above.

Step 4: Define your other parameters

      You define the rest of the parameters your report needs by using the Data Model Painter. You use these parameters in the user exit calls and SQL statements.

Step 5 : Call FND FLEXSQL from your Before Report Trigger to populate P_FLEXDATA

      Next, given that you want to display flexfield information like concatenated values and descriptions, and arrange them in order, you make one call to FND FLEXSQL from the Before Report Trigger specifying the lexical parameters. This call changes the value of the lexical parameter P_FLEXDATA at runtime to the SQL fragment that selects all flexfields value data. For example, the parameter changes to
      (SEGMENT1||’\n’||SEGMENT2||’\n’||SEGMENT3||’\n’||SEGM ENT4).

      When you incorporate this lexical parameter into the SELECT clause of a query, it enables the query to return the concatenated segment values that are needed as input to other AOL user exits. These exits then retrieve the actual flexfield information for display purposes.

      Here is an example FND FLEXSQL call. Notice that the arguments are very similar to other flexfield routine calls; CODE= and NUM= designate the key flexfield and its structure, respectively. For a report on a different key flexfield (such as the System Items flexfield), you would use a different CODE and NUM.

      SRW.REFERENCE(:P_STRUCT_NUM);
      SRW.USER_EXIT(’FND FLEXSQL
      CODE=”GL#”
      NUM=”:P_STRUCT_NUM”
      APPL_SHORT_NAME=”SQLGL”
      OUTPUT=”:P_FLEXDATA”
      MODE=”SELECT”
      DISPLAY=”ALL”’);

      You should always reference any source column/parameter that isused as a source for data retrieval in the user exit. This guarantees that this column/parameter will contain the latest value and is achieved by ”SRW.REFERENCE” call as shown above.

Step 6: Call FND FLEXSQL from your Before Report Trigger to populate other parameters

      You call FND FLEXSQL once for every lexical parameter such as P_WHERE or P_ORDERBY.

Step 7: Define your report query or queries

      Define your report query Q_1:

      SELECT &P_FLEXDATA C_FLEXDATA
      FROM CODE_COMBINATIONS_TABLE
      WHERE
      CODE_COMBINATIONS_TABLE.STRUCTURE_DEFINING_COLUMN
      = &P_STRUCT_NUM

      The query fetches the data required to be used as input for the FLEXIDVAL user exit later.
      Note: Always provide a column alias (C_FLEXDATA in this example) in the SELECT clause that is the name of column. This name of the column is required in FND FLEXIDVAL.

      When the report runs, the call to FND FLEXSQL fills in the lexical parameters. As a result the second query would look something like:

      SELECT (SEGMENT1||’–’||SEGMENT2||’–’||SEGMENT3||’–’||
      SEGMENT4) C_FLEXDATA
      FROM CODE_COMBINATIONS_TABLE
      WHERE
      CODE_COMBINATIONS_TABLE.STRUCTURE_DEFINING_COLUMN = 101

Step 8: Create formula columns

      Now create columns C_FLEXFIELD and C_DESC_ALL (and any others your report uses) corresponding to the values and descriptions displayed in the report. They all are in group G_1. Be sure to adjust the column width as appropriate for the value the column holds (such as a prompt, which might be as long as 30 characters).

      Attention: Use word–wrapping for flexfield columns if necessary to avoid possible truncation of your values. Do this by setting Sizing to Expand.

Step 9: Populate segment values formula column

      To retrieve the concatenated flexfield segment values and description, you incorporate the flexfields user exits in these columns. In the column definition of C_FLEXFIELD, you incorporate the FND FLEXIDVAL user exit call in the formula field. You pass the concatenated segments along with other information to the user exit, and the user exit populates the concatenated values in this column as specified by the VALUE token. A typical call to populate segment

      SRW.REFERENCE(:P_STRUCT_NUM);
      SRW.REFERENCE(:C_FLEXDATA);
      SRW.USER_EXIT(’FND FLEXIDVAL
      CODE=”GL#”
      NUM=”:P_STRUCT_NUM”
      APPL_SHORT_NAME=”SQLGL”
      DATA=”:C_FLEXDATA”
      VALUE=”:C_FLEXFIELD”
      DISPLAY=”ALL”’);
      RETURN(:C_FLEXFIELD);

Step 10: Populate segment descriptions

      To populate the segment description use
      DESCRIPTION=”C_DESC_ALL” instead of VALUE=”C_FLEXFIELD” as in the previous call. The user exit call becomes:

      SRW.REFERENCE(:P_STRUCT_NUM);
      SRW.REFERENCE(:C_FLEXDATA);
      SRW.USER_EXIT(’FND FLEXIDVAL
      CODE=”GL#”
      NUM=”:P_STRUCT_NUM”
      APPL_SHORT_NAME=”SQLGL”
      DATA=”:C_FLEXDATA”
      DESCRIPTION=”:C_DESC_ALL”
      DISPLAY=”ALL”’);
      RETURN(:C_DESC_ALL);

      You have created parameters and columns that are containers of all the values to be displayed. Now, in the following steps, you create the layout to display these values on the report.

Step 11: Create your default report layout

      Use the Report Wizard to generate the default layout. Deselect C_FLEXDATA. Specify a ”Label” and a reasonable ”Width” for the columns you want to display.

      Oracle Reports takes you to the layout painter. Generate and run the report.

Step 12: Finish your report

      Adjust your report layout as needed.

Previous     For further information on Oracle Reports goto      FAQ/Oracle Reports