Creating Tabular Layouts

This chapter provides an overview of tabular layouts and discusses how to:

Create tabular layouts.
Map tabular layout columns.
Define layout options.
Create report titles.

Understanding Tabular Layouts

Tabular layouts use PeopleSoft queries to retrieve data. Unlike matrix layouts, tabular layouts don’t rely on the intersection of rows and columns to retrieve data. The columns in the report correspond to the fields selected by the query. The rows display the entire query result set, although you can use a scope to limit the results. By using query criteria, you can retrieve data from almost any table in a PeopleSoft database.

When defining a tabular layout, you select a query and then select the query columns to use and the layout columns to map them to.

See Also

Creating Matrix Layouts

Creating Tabular Layouts

To create a tabular layout:

Create a new layout.
Define the layout as a tabular layout.
Select nVision, Layout Definition.

The Layout Options dialog box appears.
Select Tabular Layout Sheet and click OK.

You can also optionally select a setID, effective date, and business unit to be used as prompts when searching for your layouts.

The Layout Definition dialog box appears.
Select the Source tab to specify the query name you want to use.
Select a query name from the drop-down list and click OK.

Because you have chosen to create a tabular layout sheet, PS/nVision knows that you will be basing this layout on a query. Therefore, the only option available in the Type drop-down list is Query.
Map the layout columns to query columns.

See Mapping Tabular Layout Columns.
Define options for your worksheet, rows, and columns.

See Defining Layout Options.

See Also

Creating Layouts

Mapping Tabular Layout Columns

Make sure that the layout columns are mapped to the appropriate query columns. When you run a report based on the layout, these columns contain the data that the query returns.

To map layout columns to query columns:

From the Layout Definition dialog box, select a layout column to map to a query column.

Use the Navigate buttons to select a result column in the layout. The column appears as dark gray when selected, and the column tab appears on the Layout Definition dialog box.
Select the Column tab that appears on the Layout Definition dialog box.

You use this tab to map a query result column to the selected layout column.
Select a query result column.

The list box shows the output columns associated with the selected query. By default, the list box displays the headings assigned to the result columns in PeopleSoft Query, and View Heading is selected. To see the names of record fields associated with each result column, select View Record Field.
Select Resize column to fit data to use Excel's AutoFit command to adjust column width at runtime.

This makes the column as wide as the defined field length when a report is run.
Click Apply to save your changes and map a different query column to a different layout column, or click OK to save your changes and close the dialog box.

The name of the column you selected is inserted into the third cell of the selected column as a temporary label. When you run a report, this label does not appear. However, you can define a row to display column headings in the report.

If you clicked Apply, navigate to a new column and repeat steps 3 and 4 to map another query result column.

Defining Layout Options

Select the Options tab on the Layout Definition dialog box.

The controls available in the dialog box vary depending on whether you have a cell, row, column, or the entire worksheet selected.

Note. If the Options tab does not appear, you may have to deselect a column or the worksheet.

Sheet Options

These options apply to the entire layout.

Instance Criteria

Select an option to dictate how PS/nVision applies the scope specified in the report request. With a tabular layout, PS/nVision implements scope by adding selection criteria to the query that the layout uses. For each report instance, it adds a criterion that restricts the returned values to those subsets that correspond to the current value of the scope variables. Options include:

Inherit All: Select to have criteria specified in the scope definition apply to the layout. If PS/nVision does not find scope definition criteria in the query’s table, it displays an error message.

Inherit Matching: Select to specify that only the scope definition criteria found in the query are inherited. PS/nVision ignores all scope definition criteria not found in the query’s table.

Inherit None: Select to indicate that PS/nVision should not use the assigned scope when processing the query.

Row Options

These options are available only if you have selected a layout row.

None	No special functionality is applied to the selected row. This is the default.
Heading Row	The selected row displays the heading of each query column in the layout. If you don’t choose a heading row, the layout uses Row 3 as the heading row.
First Result Row	The selected row displays the first row of data returned by the query. The rest of the data rows are inserted immediately below this row. Note. You must define one first result row in your layout.
Total Row	The selected row can contain Excel formulas that perform calculations on the result rows in the column. You define the total row in position relative to the first result row; in the report instances, the total row actually appears relative to the last data row. For example, if you leave one blank row in the layout between the first result row and the total row, there will be one blank row between the end of the query data and the totals. Note. After you select this option, you must manually enter the Excel formulas you want in your total row. All PS/nVision dialog boxes must be closed before you can manually insert cell contents. If a formula is to operate on an entire column, be sure to specify a range starting with the first result row and ending one row down. When reports are run, the range is expanded to include all the inserted result rows.

Column Options

These options are available only if you have selected a layout column.

None

No special functionality is applied to the selected column. This is the default.

Copy Formula

Any Excel formula found at the intersection of the selected column and the total row is copied to all rows in the column. This allows you to easily create columns that perform calculations based on the other layout columns. For example, if your query returns projected and actual budget data, you might want to add a column that displays the variance.

Note. After you select this option, close the Layout Definition dialog box and access the layout. Highlight the cell at the intersection of the total row and the calculated column and enter the Excel formula you want to use for the column.

See Also

Defining Report Scopes

Creating Report Titles

Because you cannot insert variables into a tabular layout, you cannot generate report titles automatically, as you would using a matrix layout. To create a report title in a tabular layout, insert a second sheet into your Excel workbook and create a matrix layout. Use the %RTT% variable to create the report title in the matrix layout, and then do an intra-sheet reference in Excel; the report title appears on your tabular report.

See Also

Excel documentation.