Column Aliasing When writing queries in Oracle Reports, always specify column aliases to keep column names clear and readable. This is extremely useful when future modifications are required to the Data Model. The reason is that the Layout model is driven by the Data model, so any changes in the Data model will cascade down to the Layout model. Using column aliases, protects the Layout model (to a certain degree), from such changes.
Summary, Formula and Placeholder columns Apart from columns which are selected from the database, there are other types of columns which can be created within the report itself. They are SUMMARY columns, FORMULA columns, and PLACEHOLDER columns.
In brief: A SUMMARY column allows you to perform aggregate functions such as SUM, AVG, COUNT, MAX, MIN etc.. A FORMULA column allows you to perform any pl/sql function that will return a value of type NUMBER, VARCHAR2, DATE or BOOLEAN.
However columns referenced in the formula must be in the same group or at a higher level, due to frequency constraints
A PLACEHOLDER column is merely a container, or a “place” to “hold” a value. A formula column is used to populate a placeholder column.
Where do these columns go? In which group do they belong? The answers are simple.
If you want to get the total number of employees in a department, the sum must reset at department level, so the SUMMARY column must be created in the department group.
If you want to calculate the commission in dollars for each employee, that is salary times commission percentage, the FORMULA column must be created in the same group as the salary and commission percentage columns, that is the employee group.
If you want to create a PLACEHOLDER column, or container into which values are assigned, place it in the same group as the formula column which is performing the assignment, or place it at a higher level.
When creating these types of columns, always use the default naming convention. This can save precious hours later if and when the report needs to be modified. For example: Summary column names are prefixed by CS_ Formula column names are prefixed by CF_ Placeholder columns names are prefixed by CP_
If possible try to calculate summary functions and formulae directly in the database, this will be more efficient compared to querying all the records from the server to the client and performing the calculations on the client side.
System Parameters and User Parameters These parameters also fall under the Data Model umbrella. They can be viewed from the Object Navigator, not from the Data Model window. Why do these parameters belong to the Data Model? Because they represent DATA that is required to run the report. For example, some of the System Parameters are DESTYPE (File, Printer, Mail etc.), DESNAME (Output filename, Printer name, Email address etc.), MODE (Character, Bitmap), ORIENTATION (Portrait, Landscape), DESFORMAT (PDF, HTMLCSS, etc.) and so on. The User Parameters are entirely custom created and can be of type VARCHAR2, NUMBER or DATE. These types of parameters are required in conjunction with the Runtime Parameter Form which will be discussed later on in this paper.
System Variables These are default variables such as Current Date or Physical Page Number, a list of which is found in the Source property of a field.
Global Variables These can be very useful when you need to assign values to variables in “mid-flight”, for example in the Between Pages Trigger or in a format trigger. To create a global variable, simply create a local PL/SQL program unit of type Package Spec, then declare a variable in it with the appropriate datatype. A Package Body is not required.
To assign values to this global variable, use the following syntax: package_spec_name.variable_name.
For example if the package spec is called Global and the variable declared in it is called v_Count, then in the report trigger or format trigger, we can assign a value to it as follows
:global.v_count := 10;
Page Breaks
Page Breaks Always use the “Maximum Records per Page” property on the appropriate Repeating Frame, to control page breaks. For example, if every new department must begin on a new page, go to the Department repeating frame and set the Maximum Records per Page property to 1.
Alternatively, if there is a requirement such as: “If a new department begins half way down the page and all the employees of that department cannot fit on the same page, then move the entire department to the start of the next page.” .. this is achieved by setting the Page Protect property on the department frame.
Summary, Formula and Placeholder columns Apart from columns which are selected from the database, there are other types of columns which can be created within the report itself. They are SUMMARY columns, FORMULA columns, and PLACEHOLDER columns.
In brief: A SUMMARY column allows you to perform aggregate functions such as SUM, AVG, COUNT, MAX, MIN etc.. A FORMULA column allows you to perform any pl/sql function that will return a value of type NUMBER, VARCHAR2, DATE or BOOLEAN.
However columns referenced in the formula must be in the same group or at a higher level, due to frequency constraints
A PLACEHOLDER column is merely a container, or a “place” to “hold” a value. A formula column is used to populate a placeholder column.
Where do these columns go? In which group do they belong? The answers are simple.
If you want to get the total number of employees in a department, the sum must reset at department level, so the SUMMARY column must be created in the department group.
If you want to calculate the commission in dollars for each employee, that is salary times commission percentage, the FORMULA column must be created in the same group as the salary and commission percentage columns, that is the employee group.
If you want to create a PLACEHOLDER column, or container into which values are assigned, place it in the same group as the formula column which is performing the assignment, or place it at a higher level.
When creating these types of columns, always use the default naming convention. This can save precious hours later if and when the report needs to be modified. For example: Summary column names are prefixed by CS_ Formula column names are prefixed by CF_ Placeholder columns names are prefixed by CP_
If possible try to calculate summary functions and formulae directly in the database, this will be more efficient compared to querying all the records from the server to the client and performing the calculations on the client side.
System Parameters and User Parameters These parameters also fall under the Data Model umbrella. They can be viewed from the Object Navigator, not from the Data Model window. Why do these parameters belong to the Data Model? Because they represent DATA that is required to run the report. For example, some of the System Parameters are DESTYPE (File, Printer, Mail etc.), DESNAME (Output filename, Printer name, Email address etc.), MODE (Character, Bitmap), ORIENTATION (Portrait, Landscape), DESFORMAT (PDF, HTMLCSS, etc.) and so on. The User Parameters are entirely custom created and can be of type VARCHAR2, NUMBER or DATE. These types of parameters are required in conjunction with the Runtime Parameter Form which will be discussed later on in this paper.
System Variables These are default variables such as Current Date or Physical Page Number, a list of which is found in the Source property of a field.
Global Variables These can be very useful when you need to assign values to variables in “mid-flight”, for example in the Between Pages Trigger or in a format trigger. To create a global variable, simply create a local PL/SQL program unit of type Package Spec, then declare a variable in it with the appropriate datatype. A Package Body is not required.
To assign values to this global variable, use the following syntax: package_spec_name.variable_name.
For example if the package spec is called Global and the variable declared in it is called v_Count, then in the report trigger or format trigger, we can assign a value to it as follows
:global.v_count := 10;
Page Breaks
Page Breaks Always use the “Maximum Records per Page” property on the appropriate Repeating Frame, to control page breaks. For example, if every new department must begin on a new page, go to the Department repeating frame and set the Maximum Records per Page property to 1.
Alternatively, if there is a requirement such as: “If a new department begins half way down the page and all the employees of that department cannot fit on the same page, then move the entire department to the start of the next page.” .. this is achieved by setting the Page Protect property on the department frame.
