Understanding 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 do not 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 Tabular Layouts
To create a tabular layout in PS/nVision:
Create a new layout by selecting nVision, New Layout from the nVision menu.
Define the layout as a tabular layout.
Select nVision, Layout Definition from the nVision menu.
Select the Tabular Layout Sheet option and click the OK button.
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 the OK button.
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.
Define options for your worksheet, rows, and columns.
See Also
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.
This is an example of the PeopleSoft nVision Layout Definition – Column dialog box:
To map layout columns to query columns in PS/nVision:
With a nVision layout open, select nVision, Layout Definition from the nVision menu.
Select the Tabular Layout Sheet option from the Layout Options dialog box, and click the OK button.
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 the View Heading option is selected. To see the names of record fields associated with each result column, select the View Record Field option.
Select Resize column to fit data to use the Microsoft Excel AutoFit command to adjust column width at runtime.
This selection makes the column as wide as the defined field length when a report is run.
Click the Apply button to save your changes and map a different query column to a different layout column, or click the OK button 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 the Apply button, navigate to a new column and repeat steps 3 and 4 to map another query result column.
Before PeopleTools release 8.48, if a query had a self-join and, therefore, had more than one column with the same record.field name, then nVision could not distinguish between those two columns.
For example, consider the following query SQL:
Select A.EmpName, B.EmpName from EMPLOYEE A, EMPLOYEE B where A.MgrNo = B.EmpNo
If A.EmpName and B.EmpName were mapped to two columns in a tabular layout, then the results for A.EmpName will also be used for B.EmpName.
In PeopleTools 8.48 and later, nVision can distinguish between two columns having same record.field in a query with a self-join. Also, the criteria string for a column in a tabular layout will now include a new code “A<xyz>”, where <xyz> represents the Alias for that record, such as:
|
Employee %,HEmployee,REMPLOYEE,FEmpName,AA |
Manager %,HManager,REMPLOYEE,FEmpName,AB |
|
Smith, John |
Wilson, Dave |
|
Patterson, William |
Adams, Tracie |
Defining Layout Options
Use these steps to access the PeopleSoft nVision Layout Definition – Options dialog box:
With a nVision layout open, select nVision, Layout Definition from the nVision menu.
From the Layout Definition dialog box, select a layout column to map to a query column.
Select the Column tab.
The PeopleSoft nVision Layout Definition – Options dialog box appears.
This is an example of the PeopleSoft nVision Layout Definition – Options 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 in the PeopleSoft nVision Layout Definition dialog box, you may have to clear a column or the worksheet.
Sheet Options
These options apply to the entire layout.
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 option. |
|
Heading Row |
The selected row displays the heading of each query column in the layout. If you don’t select a heading row, the layout uses Row 3 as the heading 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. |
|
|
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. |
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. |
|
Any Excel formula found at the intersection of the selected column and the total row is copied to all rows in the column. This option enables 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 PeopleSoft nVision 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
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 Microsoft Excel documentation.