Creating 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.

Defining Ledger Criteria

When you define ledger criteria, you can choose 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.

To define ledger criteria:

On the Layout Definition dialog box, select the row, column, or cell to apply ledger criteria.

Use the Navigate controls to select the location.
Select the Source tab and select Ledger as the source type.
Select a ledger from the Ledger Name drop-down list.
Choose a ledger amount column by clicking the Get List button and clicking the available columns that are displayed.
Choose a TimeSpan to limit the ledger data.
To have columns or rows containing TimeSpan details automatically inserted, select nPlode Time Spans.

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 Reverse Sign.

For example, you may want to see revenue reported as a positive number. This is normally set for query criteria at the cell, row, or column level, not the worksheet level.
If the ledger contains separate credit and debit columns, they appear in the Ledger Amount Column field. Select the amount to report on.
Click Apply to save your changes and define ledger criteria for a different group of cells, or click OK to save your changes and close the dialog box.

If you clicked Apply and you want to reuse all or part of the criteria you just applied, select the Retain Contents option. This 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 are using ledgers, but 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 are displayed 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 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. We recommend using 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 radio button 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:

Follow the procedure for adding tree node values.

See Using Filter Criteria.
From the Field Name dialog box, select Selected Summary ChartField Nodes.

Adding Label Criteria

Label controls are available on ledger-based layouts, just as they are with 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 a ChartField’s detail or tree node table. You can also specify a special label for nPloded TimeSpans in the format YYYY-PP (year-period) by entering ACCOUNTING_PERIOD as 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).