Understanding Ledger-Based Matrix Layouts
This chapter provides an overview of ledger-based matrix layouts and discusses how to:
Define ledger criteria.
Use TimeSpans.
Use filter criteria.
Add label criteria.
Use nPlosion.
Understanding Ledger-Based Matrix Layouts
A ledger is a special type of query in PS/nVision, with an implied aggregate operation (sum) and record and field names specified through the ledger definition. In PS/nVision, a ledger-based layout is essentially a matrix layout that uses the ledger table in place of a query. Typically, you use ledger-based layouts with applications such as PeopleSoft General Ledger or Enterprise Performance Management. Although you can use ledgers and queries in the same report, ledger and query specifications are mutually exclusive for a row or column, because a row, column, or cell can have only one data source.
This chapter discusses the differences between ledger-based layouts and reports and query-based matrix layouts.
See Also
Defining Ledger Criteria
When you define ledger criteria, you can select the same options as with query-based matrix layouts. As with query criteria, you can apply ledger criteria at the worksheet, column, row, or cell level.
This is an example of the PeopleSoft nVision Layout Definition – Source dialog box:
To define ledger criteria in PS/nVision:
Open any existing layout or create a new one.
Select nVision, Layout Definition from the menu.
In the PeopleSoft nVision Layout Definition dialog box, select the row, column, or cell that apply ledger criteria.
Use the Navigate controls to select the location.
Select the Source tab, and then select Ledger as the source type.
Select a ledger from the Ledger Name drop-down list box.
Select a ledger amount column by clicking the Get List button and clicking the available columns that appear.
Select a TimeSpan to limit the ledger data.
To have columns or rows containing TimeSpan details automatically inserted, select the nPlode Time Spans option.
The nPlode Time Spans option appears only if you have a row or column selected.
To reverse the sign of the amounts returned from the database, select the Reverse Sign option.
For example, you may want to see revenue reported as a positive number. This setting is normally selected for query criteria at the cell, row, or column level, not the worksheet level.
In the Ledger Amount Column field, select the ledger amount to report on.
Note. If the ledger contains separate credit and debit columns, they appear in the Ledger Amount Column field.
Click the Apply button to save your changes and define ledger criteria for a different group of cells.
Alternatively, click the OK button to save your changes and close the dialog box.
If you clicked the Apply button and you want to reuse all or part of the criteria you just applied, select the Retain Contents option. This selection preserves the dialog box information when you navigate to a new cell selection. Then, repeat the procedure to define more ledger criteria.
Using TimeSpans
TimeSpans express fiscal-year and accounting-period ranges relative to the main as-of date specified in the report request. TimeSpans control the periods for which data is extracted from the database. Many TimeSpans are expressed relative to the current period, so that they automatically adapt the content of a report to the report as-of date. TimeSpans are required when you use ledgers, but they are optional with queries.
An example of using TimeSpans is an earnings summary report that compares earnings from the end of 2000 to the end of 2001, broken down by four quarters. Revenue from operations and net earnings are listed down the left side of the report, while quarterly earnings appear across the top of the report as column headings. You select the appropriate QTR TimeSpan for each quarter at the column level. Then, you specify the Accounts ledger in the criteria for the entire spreadsheet. For the rows, specify the individual accounts whose earnings you want to report on.
Relative Adjustment Periods
Use the following example to specify the Relative Adjustment Period. To retrieve x number of periods back:
"BaseAdjustmentPeriod - x" where BaseAdjustmentPeriod = (FirstAdjustmentPeriod - 1).
For example, FirstAdjustmentPeriod = 901 (for period 1). BaseAdjustmentPeriod = 900. To specify the last two adjustment periods, Relative StartAdjustmentPeriod should be 898.
To retrieve x number of periods ahead:
"BaseAdjustmentPeriod + x".
Using the example above, to retrieve two periods ahead, define Relative EndAdjustmentPeriod as 900 + 2 = 902.
Using Filter Criteria
As with other matrix layouts, you use filter criteria to specify the character field values (such as ACCOUNT) selected for rows and columns of the report.
As in query-based layouts, filter criteria can be expressed in terms of detail values or tree nodes, and can be nPloded to generate multiple detail rows or columns. However, with ledger-based layouts, filter criteria also can be expressed as summary ChartField nodes.
Using Summary ChartField Nodes
When defining filter criteria for a database containing ledgers, you can use summary ChartField nodes as criteria values. This use of summary ChartField nodes retrieves data from a summary ledger ChartField that contains tree nodes as values. Detail ledger ChartFields serve as keys to the detail ledger by categorizing posted total amounts. You can create summary ledgers that roll up detail amounts based on specific detail values or on selected tree nodes. When detail values are summarized using tree nodes, you must use summary ChartFields in the summary ledger data record. The maximum length of a node name is 20 characters.
For example, values contained in a Department ChartField (such as DEPTID 0100, 0200, 0300, and so forth) on a detail ledger can be rolled up (using an organizational tree) into a Division ChartField on a summary ledger. You can store these values with summary ChartField node names such as Sales, Marketing, and Administration.
You can use summary trees or summary ChartField nodes to access data from a summary ledger of this type. You should use summary trees because then you can create different rollups of the summarized nodes and use nPlosion on them. When you drill down, summary trees also let you translate summary criteria to the corresponding detail criteria back in the general ledger. When specifying criteria via a summary tree, click the Selected Tree Nodes option in conjunction with the summary ChartField.
Note. PS/nVision does not support the translation of summary ChartField nodes when you are drilling down to the detail ledger. Use the summary tree criteria.
To add a field and summary ChartField node values to filter criteria in PS/nVision:
Follow the procedure for adding tree node values.
From the Field Name dialog box, select the Selected Summary ChartField Nodes option.
Adding Label Criteria
Label controls are available on ledger-based layouts, just as they are on any matrix layout.
If you are basing the layout on a ledger, you can retrieve label text from a number of fields defined in Application Designer for the detail or tree node table of a ChartField. You can also specify a special label for nPloded TimeSpans in the format YYYY-PP (year-period) by entering ACCOUNTING_PERIOD in the Detail Values field.
Using nPlosion
For PeopleSoft General Ledger, note that nPlosion is available for detail ledger ChartFields or summary ledger ChartFields that contain detail values, and for summary ledger ChartFields accessed through summary trees. nPlosion is not available for summary ledger ChartFields specified as Selected Summary ChartField Nodes. For other applications, nPlosion is available for criteria fields that have value tables listing the valid values of the field (usually, this is the prompt table for that field).
See Also