This chapter contains the following topics:
Tabular sections are specialized types of columnar sections. While the report data is presented in a columnar format, tabular sections provide spreadsheet capability by enabling you to define the data in columns, rows, and cells.
When you include fields that display numeric values, tabular sections automatically total the values. For example, if you include an object that displays open amounts, tabular sections calculate a grand total of all the open amounts in the section. Because totaling is automatic in tabular sections, level break footers are not available. You must suppress totals for any columns that you do not want calculated.
In addition, level break headers are not available in tabular sections. Use the Row Description column to describe the data.
When you create tabular sections, observe these guidelines for defining combinations of columns, rows, and cells:
| Condition | Guideline |
|---|---|
| Define columns only. | Define columns when the information in the report is based solely on the data that is contained in tables. When you define the columns, rows are generated at runtime based on the selection, sequence, and level break criteria that you defined. |
| Define columns and rows. | Define rows in addition to columns when you include details in the report, such as underlines, spaces, and blank lines, as well as special calculations, such as interim totals. Row information is set up horizontally on the report. |
| Define columns, rows, and cells. | Define cells to override information that is defined by columns and rows. A cell is the intersection of a column and row. |
This section provides an overview of Row Description columns, lists the prerequisites, and discusses how to:
Create Row Description columns manually.
Delete Row Description columns.
When you create tabular sections, the system automatically includes a Description column in the section layout. The data in this column is based on the data sequencing fields that you define as level break fields. If you remove the Description column from the section layout, you can select it from the list of business view columns.
If the level break fields that are included in the Description column have data dictionary triggers attached, the system prints the description of the field in the Row Description column. Otherwise, the system prints the field value. For example, if you define company as a level break field and retrieve records for company 00001, the description of Financial Reporting Company appears in the Row Description column. This is because the company field has a trigger attached in data dictionary. If no trigger were attached to the company data dictionary field, the system would display the field value of 00001.
The Row Description column has special capability for level breaks that are associated with the Subledger, Cost Object, and Object Subsidiary fields. When a level break causes a change in one of these fields in a tabular section, the Description row automatically displays this information:
Subledger and subledger type.
Cost object and cost object type.
Object subsidiary and object subsidiary type.
Before you begin working with Row Description columns, ensure that you:
Create a batch application object.
Complete the design of the report template using a tabular section.
Open a tabular section report in Oracle's JD Edwards EnterpriseOne Report Design Aid (RDA).
This section provides an overview of decimal scaling, lists the prerequisites, and discusses how to:
Change decimal scaling for individual fields.
Change decimal scaling for all fields in a tabular section.
Change decimal scaling for all fields in all tabular sections of a report.
Decimal scaling enables you to simplify the way large numbers appear in tabular sections. You can change decimal scaling for a single field in a tabular section, for all fields in a tabular section, or for all fields in all tabular sections of a report.
This table illustrates how the amounts in a report appear when the numeric data is scaled to 1000:
| Numeric Data | Displayed Value |
|---|---|
| 100,000.42 | 100 |
| 10,041.62 | 10 |
| 1,021.75 | 1 |
| 1,512.69 | 1.5 |
When modifying decimal scaling at the report level, you must consider the existing batch versions.
See Chapter 6, "Working with Report, Section, Field, Column, and Row Properties".
Before you begin working with objects unique to tabular sections, ensure that you:
Create a batch application object.
Complete the design of the report template using a tabular section.
Open a tabular section report in RDA.
In the tabular section, double-click the variable of the numeric column that you want to change.
On the Column Variable Properties form, select the Decimal Scaling tab.
Select the appropriate level of decimal scaling, and click OK.
To return the field to its default decimal scaling setting, click the Defaults button.
Open a tabular section report in RDA.
Double-click the tabular section that you want to change.
On the Section form, select the Decimal Scaling tab.
Select the appropriate level of decimal scaling, and click OK.
To return all of the fields in the section to their default decimal scaling settings, click the Defaults button.
The decimal scaling changes that you make on this form affect all of the numeric fields in the section, except for those fields that have been modified individually. To override individual settings and apply the changes to all fields in the section without exception, select the Apply settings to all Objects option.
Open a tabular section report in RDA.
From the File menu, select Report Properties.
On the Properties form, select the Decimal Scaling tab.
Select the appropriate level of decimal scaling, and click OK.
To return all of the fields in all tabular sections of the report to their default decimal scaling settings, click the Defaults button.
The decimal scaling changes that you make on this form affect all of the numeric fields in all tabular sections of the report. The exceptions are all fields that are located in a tabular section where the section decimal scaling has been defined and fields that have been modified individually. To override these settings and apply the changes to all of the fields in all tabular sections of the report without exception, select the Apply settings to all Objects option.
Note:
When modifying decimal scaling at the report level, you must consider the existing batch versions.See Chapter 6, "Working with Report, Section, Field, Column, and Row Properties".
This section provides an overview of calculation columns, lists the prerequisites, and discusses how to:
Define calculation columns
Define percent calculations
Remove column calculations
Calculation columns contain the results of mathematical calculations. You can perform a calculation involving any number of columns.
When you create an application report using the Director, you have the option on the Select Columns form to create calculation columns. From the Director, you can create calculation columns using only the smart field columns that you selected for the section layout.
After the initial design of the report template is complete, you can insert calculation columns. You can create these calculation columns using:
Any column from the attached business view.
Smart field columns that are included in the section layout.
Other calculation columns.
Important:
Calculation columns are based on the amount signs (debit or credit) as they appear in the report, not the actual value that is stored in the database.You can display numbers in a column as a percent of the total in another column. This type of calculation is used in all reporting types, but most often in financial reports such as standard income statements. When used in income statements, the percent calculation is referred to as the percent of revenue.
Before you begin working with calculation columns, ensure that you:
Create a batch application object.
Complete the design of the report template using either a tabular section or an application report.
Perform these actions:
Create a percent calculation column from the Director.
For example, if you are creating a financial report that is based on account balances, create a column called Percent of Revenue.
Insert a numeric variable column.
Add rows to the tabular section.
This step is necessary to create the cell to use as the denominator. For example, add data rows to hold the revenue sales and a calculation row to hold the total revenue.
Open an application report or a tabular section report in RDA.
Select the tabular section in which you need to add calculation columns.
Insert a data dictionary field or a numeric variable to hold the calculated value.
Drag the field to the appropriate location.
Change the name of the column heading and variable to reflect the purpose of the calculation.
Click the newly created column.
From the Column menu, select Define Calculation.
On the Expression Manager form, define the calculation by performing these actions, and click OK:
Double-click fields from the Available Information list.
Click the appropriate calculator functions to build the expression.
Open an application report or a tabular section report in RDA.
After adding the appropriate rows to the tabular section, click the column that you inserted for the percent calculation.
From the Column menu, select Define Calculation.
On the Expression Manager form, define the percent calculation and click OK.
This section provides an overview of tabular rows, lists the prerequisites, and discusses how to:
Add data rows
Add calculation rows
Add sum rows
Add underline rows
Add constant rows
Generate rows automatically
In tabular sections, rows consist of information that is presented horizontally. Typically, rows contain data that is read from individual database records; however, you can add rows to include details in the report section such as:
Underlines
Blank lines
Special calculations
Tabular sections are the only detail sections for which you can define rows. In all cases, after you create a row, you can drag it to a new location or delete it by selecting Delete from the Edit menu.
Rows can be defined manually or through automatic row generation. Automatic row generation is used primarily in financial reports. A report that displays a chart of accounts is an example of data that can be generated using the automatic row generation feature.
Rows that are defined manually are defined individually and each data row includes its own data selection.
By using RDA, you can manually add these types of rows:
| Row Type | Description |
|---|---|
| Data rows | Fetch data from Oracle's JD Edwards EnterpriseOne tables. You must define the rows and identify the data using the business view that is attached to the tabular section. Data rows represent groups of data fields that are associated with the columnar amounts. For example, you can have a data row that displays revenue (column) for a range of items. In addition, you can add a row that displays the direct costs (column) for another range of fields. |
| Calculation rows | Display amounts that are calculated from other rows. For example, you can calculate the gross margin of the revenue and direct costs rows. |
| Sum rows | Define a special type of calculation. The calculation performs totaling for all numeric columns in a range of rows. The total can include or exclude rows within the sum range that are themselves row calculations. |
| Underline rows | Enable you to create underlines to separate various rows in the report. |
| Constant rows | Contain only text. Enables you to describe or label information in the tabular section, such as identification information for a group of rows. |
| Automatically generated rows | In tabular sections, the system automatically generates rows that define a chart of accounts for a business unit or represent the merging of accounts from several business units. After the generation, you can insert rows that calculate account roll-up totals at various levels of detail. For example, you might want the system to automatically generate rows to create a balance sheet report that is based on the current month and the prior year's account balance. |
The system optimizes rows with two or less logical expressions (nodes) that are joined by an AND operator. The optimization accelerates report processing speed. If you include a cover page with the report, you can review which rows the system is optimizing. In the Optimized Row column of the cover page, the system prints a Y next to the rows that are optimized and an N next to the rows that are not. The cover page also includes the total number of rows that are optimized, Number of Optimized Inclusion Rows, and the total number that are not, Number of Non Optimized Inclusion Rows. You can also find the total number of optimized rows in the UBE log file, Tabular Optimization.
The row optimization feature is enabled by default. As a troubleshooting measure, you can disable row optimization by adding this line to the UBE section of the jde.ini:
[UBE] UBETabOpt=0
To again enable row optimization, set the variable to 1, or delete the row from the jde.ini file.
Before you begin working with tabular rows, ensure that you:
Create a batch application object.
Complete the design of the report template using a tabular section.
Open a tabular row section report in RDA.
Click the tabular section.
From the Row menu, select Create, Data.
On the Data Row Properties form, enter the name of the data row in the Name field on the General tab.
The name appears in the Name field on the Row List tab of the Tabular Section properties and on the Data Row Properties form.
In the Description field, enter a meaningful description of the data.
The description appears on the report in the Description column of the Tabular section, on the Row List tab of the Tabular Section properties, and in the Description field of the Data Row Properties form. This Description column can consist of multiple lines to accommodate as much text as required. To indent the text on the report, enter spaces in front of the text.
Set other properties that you want on other tabs, and click OK.
On the Data Selection form, define the criteria that you want to apply to the data row, and click OK.
At any time in the future, you can modify the data selection by selecting Define Data Selection from the Row menu.
To add rows directly beneath the data row, click the data row (indicated by a black box around the row) and add another row.
Open a tabular row section report in RDA.
Click the tabular section.
From the Row menu, select Create, Calculation.
On the Calculation Row Properties form, enter the name of the calculation row in the Name field on the General tab.
The name appears in the Name field on the Row List tab of the Tabular Section properties and on the Calculation Row Properties form.
In the Description field, enter a meaningful description of the calculation.
The description appears on the report in the Description column of the Tabular section, on the Row List tab of the Tabular Section properties, and in the Description field of the Calculation Row Properties form.
Set other properties that you want on other tabs, and click OK.
On the Expression Manager form, define the calculation, and click OK.
To add rows directly beneath the calculation row, click the calculation row (indicated by a black box around the row) and add another row.
Open a tabular row section report in RDA.
Click the tabular section.
From the Row menu, select Create, Sum Row.
On the Sum Row Properties form, enter the name of the sum row in the Name field on the General tab.
The name appears in the Name field on the Row List tab of the Tabular Section properties and on the Sum Row Properties form.
In the Description field, enter a meaningful description of the sum.
The description appears on the report in the Description column of the Tabular section, on the Row List tab of the Tabular Section properties, and in the Description field of the Sum Row Properties form.
In the From Row list, select a row to use as the beginning in a range of rows to be included in the sum.
In the To Row list, select a row to use as the ending in a range of rows to be included in the sum.
Select the Include Intermediate Calculation option if you want the sum to include calculation rows that reside between the From and To rows.
Set other properties that you want on other tabs, and click OK.
To add rows directly beneath the sum row, click the sum row (indicated by a black box around the row) and add another row.
Open a tabular row section report in RDA.
Click the tabular section.
From the Row menu, select Create, Underline.
On the Underline Row Properties form, you do not need to enter a name in the Name field on the General tab.
Set other properties that you want on other tabs, and click OK.
For example, select the Font/Color tab to modify the color of the line or the Options tab to modify the thickness and spacing of the line.
To add rows directly beneath the underline row, click the underline row (indicated by a black box around the row) and add another row.
Open a tabular row section report in RDA.
Click the tabular section.
From the Row menu, select Create, Constant.
On the Constant Row Properties form, enter the name of the constant row in the Name field on the General tab.
The name appears in the Name field on the Row List tab of the Tabular Section properties and on the Constant Row Properties form.
In the Description field, enter a meaningful description of the constant.
The description appears on the report in the Description column of the Tabular section, on the Row List tab of the Tabular Section properties, and in the Description field of the Constant Row Properties form.
Set other properties that you want on other tabs, and click OK.
To add rows directly beneath the constant row, click the constant row (indicated by a black box around the row) and add another row.
Open a tabular row section report in RDA.
Click the tabular section.
From the Row menu, select Automatic Row Generation.
Note:
Depending on how the section is designed, some fields might not appear on the form.On the Financial Account Level of Detail Row Generation form, enter the business unit for which you want to generate account information in the Business Unit field.
In the From Account field, enter the beginning account number to appear on the report.
If you leave the From Account field blank, no accounts are generated.
In the Thru Account field, enter the ending account number to appear on the report.
If you leave the Thru Account field blank, no accounts are generated.
In the Ledger Type (Optional) field, enter the ledger type to appear on the report.
If specified, the ledger type is included in the data selection for that row.
In the Level of Detail field, enter the level of detail to appear on the report.
You can generate additional account level of detail rows at level of detail breaks. The default is to generate total rows. The row amounts are based on account ranges that are specified through selection criteria.
Select the Totals option to generate totals for each level of detail.
Select one of these options under the Add Row Options heading:
Replace
Replaces all previously defined rows with the automatically generated rows. This is the default option.
Insert
Inserts automatically generated rows after the row that you clicked before selecting the automatic row generation option.
Append
Appends automatically generated rows to the bottom of previously defined rows. Use the Append and Insert options to build hybrid account structures from several different business units.
This section provides an overview of cell properties and overrides, lists the prerequisites, and discusses how to:
Override cells in data or calculation rows.
Override data selection in data row cells.
Override cell properties in underline rows.
Override cell properties in constant rows.
Cells contain information that is located at the intersection of a column and a row in tabular sections. Cells are populated based on how you define the row. For example, if you define a row to report an inventory item, one cell might be the item cost of the inventory item.
When you override cell properties, you modify the way the data in the cell appears. When you override a cell, you override the data that is located in the cell.
You can define cell overrides to override the properties of cells. For example, to emphasize the data in a specific cell, you can override its properties, increase the size of the font, and make the font bold.
You can define cell overrides to override the data that appears in the cell. For example, to review the results of possible salary increases for a group of employees, you can override the monthly salary cell by employee to change the annual income calculation. This can help you get an idea of how specific raise amounts or percentages might affect an entire department or business unit.
To override a cell in a tabular section, you must first select Cell Mode from the RDA Cell menu. Cell Mode is different from most menu items in RDA because it is a toggle option. When you click the Cell Mode menu option once, the option appears inset. In cell mode, when you click an individual cell, the cell is outlined by a solid-line rectangle. When you are not in cell mode and you try to click an individual cell, the entire row is outlined by a solid-line rectangle.
When a cell is overridden, it is outlined by a dashed-line rectangle. You can override cells in these types of rows:
Data rows
Calculation rows
Underline rows
Constant rows
Before you begin overriding individual cells, ensure that you:
Create a batch application object.
Complete the design of the report template using a tabular section with tabular rows.
Open a tabular row section report in RDA.
Click the tabular section.
From the Cell menu, select Cell Mode.
On a data or calculation row, click the cell that you want to override.
From the Cell menu, select Create Override, Calculation.
On the Cell Properties form, modify the name and description on the General tab, if appropriate.
Set properties on the available tabs as appropriate, and click OK.
On the Expression Manager form, define the expression to populate the cell with a new value, and click OK.
Open a tabular row section report in RDA.
Click the tabular section.
From the Cell menu, select Cell Mode.
On a data row, click the cell that you want to override.
From the Cell menu, select Create Override, Data.
On the Cell Properties form, modify the name and description on the General tab, if appropriate.
Set properties on the available tabs as appropriate, and click OK.
On the Data Selection form, add or modify data selection, and click OK.
Open a tabular section report in RDA.
Click the tabular section.
From the Cell menu, select Cell Mode.
On an underline row, click the cell that you want to override.
From the Cell menu, select Create Override, Underline.
On the Cell Properties form, modify the name and description on the General tab, if appropriate.
Select the Options tab, and modify the thickness on the line.
Set other properties on the available tabs as appropriate, and click OK.
Open a tabular section report in RDA.
Click the tabular section.
From the Cell menu, select Cell Mode.
On a constant row, click the cell that you want to override.
From the Cell menu, select Create Override, Constant.
On the Cell Properties form, modify the name and description on the General tab, if appropriate.
Set properties on the available tabs as appropriate, and click OK.