Reporting and Analyzing Results

This chapter provides overviews of Planning and Budgeting predefined reports, custom reports, and reporting tables.

This chapter also discusses how to use online reporting and analysis.

Understanding Predefined Reports

We deliver with Planning and Budgeting some basic and fairly generic predefined reports. These reports include:

• Structured Query Reports.

• nVision report samples.

• Online analysis reports that you can use with any planning model.

Structured Query Reports

We deliver predefined Structured Query Reports (SQR) that can be used directly against the planning model data stored in an activity scenario for your planning center. There are no modifications required; the reports can be used immediately against your staged planning model data.

The SQR reports perform a read-only lock against the activity data to which the user has access, as defined by the user role name and security group associated with the activity scenario. A preparer generates the report at the detail level, but a non-preparer can alternatively choose a summary level for most of the reports delivered. The dimensions available to generate the report (the View By option available for most reports) are determined by those defined for the planning model and activity scenario selected for reporting.

These SQR reports can be copied and customized to your organization’s requirements, if they do not meet your needs as delivered.

See Planning and Budgeting Reports.

nVision Report Samples

We deliver three sample nVision reports that are only intended to be examples. These sample reports can be referenced or copied, as a starting point when building your own nVision reports. You can run the nVision reports against both the planning model data staging tables (activity details) or the source OWE (Operational Warehouse Enriched) tables where ledger data is stored for actuals and budget ledger types.

For balance sheet planning accounts, the nVision report totals include the starting balances when available.

The following table is a summary of the sample report name, the activity type to which the report applies, the nVision layout ID as it appears in your sample nVision/layout folder, and a short description of what the report provides. These sample reports were written against the source ledger data for Planning and Budgeting, namely the standard budget ledger called BP_LED_BUDG_F00.

See For information regarding the running of nVision reports, refer to the Enterprise PeopleTools PeopleBook on PS/nVision.

Online Analysis Reports

We deliver Planning and Budgeting with online reporting and analysis functionality. These online analysis reports can be used directly against the planning model data stored in an activity scenario for your planning center. There are no modifications required; the analysis reports can be used immediately against your staged planning model data. With this feature you use inquiry pages to generate planning and budgeting reports on assets, positions, or line items. You can view the data by user-defined activities, planning dimensions, and scenarios. You run the report to load the data to an ACE (Analytic Calculation Engine) grid. The analytic grid allows you to drag and slice the data elements, to modify or expand the view of the data, as needed for your analysis.

See Using Online Reporting and Analysis.

See Also

Planning and Budgeting Reports

Enterprise PeopleTools PeopleBook: PeopleSoft Analytic Calculation Engine

Understanding Custom Reports

You can write your custom reports against the planning model tables which are updated during the budgeting process, or against the source/final tables, also known as OWE (Operational Warehouse Enriched) tables.

Use any delivered reporting tools (such as nVision, PS Query, and Crystal) or third party reporting tools to write your reports. Your reporting requirements will depend upon where you will access the data.

Although Planning and Budgeting does not support the ability to calculate the summary (aggregate) time periods as an average of the underlying months, this functionality can be achieved through an nVision report.

Understanding Reporting Tables

In general, you must consider two data storage areas when reporting against planning and budgeting data:

• The planning model tables, also known as the activity detail tables, where activity details are stored during the budgeting process.

  While the budgeting process is under way, the activity details are stored in a series of tables. These are the tables that get updated after a staging process has been performed, and when users are entering budget data during the budget process.

• The source/final tables, also known as the OWE (Operational Warehouse Enriched) tables, where both the source and final (master version) data are stored.

  Once the budget is finalized, the coordinator will want to run the export process for line item or position activity data, the master data is exported to the OWE tables.

Planning Model Tables

The planning model tables – known as the activity detail tables – are populated by the data staging process or by end users updating budget/plan data. These activity detail tables contain all version data, and are actively updated by users when an activity scenario is in a released state. You should evaluate the contents of each of the tables to determine what tables you need to combine in order to generate meaningful reports. For example, combining two sets of tables, you could create a query-based nVision report that joins the Line Item Activity table (PS_BP_LI_TBL) with the Dimension Detail Key table (PS_BP_DIM_DTL_TBL) to produce a report for total method amounts. Of course, it would not contain the total budget amount unless you also included the Line Item Adjustments table (PS_BP_LI_ADJ_TBL).

The following table provides the table names where line item data and details are stored:

The following is a list of tables populated by the data staging process, that may also be referenced for reporting:

The position activity data is stored in the following tables. The distribution tables listed for salary, earnings, benefits, and tax represent the annual amount of expense by effective date, whereas if you were looking for the breakdown of the annual amount by budget period, you would find that information in the period detail table referred to as the Employee/Default Expense by Period table (PS_BP_PBD_CALC_TBL).

The two main tables in which asset data is stored are the asset detail table containing information like cost, and the depreciation table containing the corresponding depreciation rows by period for the asset:

The asset catalog data for default details is stored in the PS_BP_ASSET_ITEMS table. Additionally, you can find the capital acquisition plan and corresponding details in the PS_CAP and PS_CAP_DET tables.

Finally, there are several views used in Planning and Budgeting to generate the analytic or analysis (ACE) reports. If you choose to write your own custom analytic reports, or modify the analysis reports, the following table provides a list of views that can be useful in extracting the information you require for reporting. A read-only lock based on your security access is performed on the data.

Note. All of the views have the PROBINST field from the PS_BP_SLICE_TBL and PS_BP_SLICE_DTL records as a foreign key. For purposes of custom reporting, PROBINST is a surrogate key for the following fields (where applicable): BP_BUDGET_CENTER1 and BP_BUDGET_VERSION.

Source/Final Tables

The source/final tables used by Planning and Budgeting are the same as the OWE (Operational Warehouse Enriched) tables, and are used as both a source of data to be staged into the planning model, and a final or target table when data is exported back from the planning model.

The following table provides the table names where financial ledger data is stored. The data located in these tables (OWE) can be populated by your ETL jobs from your source general ledger system, or by exporting a planning model’s line item activity scenario back to the source budget ledger (master version). When you decide to export the data back to the general ledger, a different export table is used as a staging table (PS_BP_LEDGER_BDEXP). This table is used in conjunction with an ETL job to move data back to the general ledger source system.

The following table provides a list of the primary table names used where position-related data is stored. The data located in these tables (OWE) can be populated by your ETL jobs from your source human resource system, or by exporting a planning model’s position activity scenario back to the source position-related tables (master version). When you decide to export the data back to the human resource system, the same tables are used in conjunction with ETL jobs and views that move data back to the source system.

There is no delivered export process to move asset and asset depreciation data back to your source tables or asset management system. You may only keep your capital acquisition plan (CAP) data synchronized between PeopleSoft Asset Management and Planning and Budgeting by activating the PeopleSoft Application Messaging Enterprise Integration Point (EIP) for BUDGET_CAP_SYNC using PeopleSoft Application Designer. The CAP tables that are updated by this EIP are listed in the following table.

See Integrating With Other Applications.

Using Online Reporting and Analysis

The delivered online analysis reports perform a read-only lock against the activity data to which you have access, as defined by the user role name and the security group associated with the activity scenario being accessed. The dimensions available in the line item activity analysis reports are determined by those defined for the planning model and activity scenario selected for reporting. The analysis reports for position and asset data only provide the account dimension, but contain additional parameters specific to these two types of reports.

The following sections discuss how to:

• Analyze position data.

• Analyze asset data.

• Analyze variance.

• Compare budget versions.

Pages Used for Online Reporting and Analysis

Analyzing Position Data

Access the Position Analysis page.

Use the online inquiry page to access and load position activity data to an analytic grid for analysis. You can access cost and FTE data by account, position, job code, and employee.

Note. You must have access to the position activity to run the analysis report.

Specify the parameters for the report by entering the Role Name, Business Unit, Planning Model ID, Activity, Scenario, Planning Center, and Budget Version ID. Select the row and column headings in the Labels field. The Labels options are Code, Code and Description, or Description.

Click Run to generate the report online.

Report Results

When you execute the report the system displays the results in an analytic grid.

Selection Criteria	Click the link to return to the Position Analysis page where you can redefine the report parameters and rerun the report.
Workspace	Click the link to go to the My Planning Workspace page with these parameters.
Preferences	Click to access a window where you can modify the layout of the grid by hiding some of the columns.
Job Code	Select the job code ID that you want to display.
Employee ID	Select the employee ID that you want to display.
Account	Select the account ID that you want to display.
Budget Period	Select the budget period that you want to display.

In the analytic grid you can modify the view of the data by dragging any dimension from the row axis to the slicer bar or column axis, and by dragging cubes from the column axis to the slicer bar or row axis.

When viewing data by budget period, the results reflect the spread ratio defined by the Spread ID assigned to the position. Positions selected as Exclude from Budget Calc on the Position Data page may appear in the Position Analysis report. However, the budget amounts and FTE associated with the positions are excluded. These values are zeroes for positions selected as Exclude from Budget Calc.

Note. If the report errors out when you run it, then dimension leveling may be an issue; that is, you may have accounts with data stored at more than one level on the dimension tree. For that reason, you should only import data at the detail level and not at the node level.

Analyzing Asset Data

Access the Asset Analysis page.

Use the online inquiry page to access and load asset activity data to an analytic grid for analysis. You can access data on an asset, asset account, capital acquisition plan, capital acquisition sequence, or asset catalog item.

When viewing the asset data, the system automatically performs a checkout of the asset information, temporarily placing a read-only lock on the data in PS_BP_ASSET and PS_BP_ASSET_DEPR for the planning center under review. Asset inquiries are based on the data contained in those records.

Note. You must have access to the Asset activity to run the analysis report.

Specify the parameters for the report by entering the Role Name, Business Unit, Planning Model ID, Activity, Scenario, Planning Center, and Budget Version ID. Specify the row and column headings in the Labels field.

Click Run to generate the report online.

Report Results

When you execute the report the system displays the results in an analytic grid.

Selection Criteria	Click the link to return to the Asset Analysis page where you can redefine the report parameters and rerun the report.
Workspace	Click the link to go to the Planning Workspace page with these parameters.
Preferences	Click to access a window where you can modify the layout of the grid by hiding some of the columns.
Account	Select the account ID that you want to display.
Asset Status	Select the asset status that you want to display. Options include Budgeted, In-service, or all.
Asset Profile ID	Select the asset profile that you want to display.
Capital Acquisition Plan #	Select the capital acquisition plan number that you want to display.
Capital Acquisition Sequence	Select the capital acquisition sequence that you want to display.
Budget Period	Select the budget period that you want to display.

In the analytic grid you can modify the view of the data by dragging any dimension from the row axis to the slicer bar or column axis, and by dragging cubes from the column axis to the slicer bar or row axis.

Note. Assets selected as Exclude from Budget Calc on the Asset Data page may appear in the Asset Budgeting Inquiry report. However, the budget amounts associated with the asset are excluded.

Note. If the report errors out when you run it, then dimension leveling may be an issue; that is, you may have accounts with data stored at more than one level on the dimension tree. For that reason, you should only import data at the detail level and not at the node level.

Analyzing Variance

Access the Variance Analysis page.

Use the online inquiry page to access and load line item activity data to an analytic grid for analysis. You can compare any two scenarios for a single activity and planning center in the planning model. The scenarios are further qualified by selecting the versions and time periods for each scenario.

Note. You must have full access to the Line Item Budgeting activity for your planning center to run these analysis reports. When you click Run the system checks whether you have full access to the planning center version. If you have only partial access, the system displays an error message that you cannot run this report unless you have access to every account in this planning center.

For line item analysis, Planning and Budgeting uses the analysis calculation definition that you enter for account types on the Account Type Options page. Depending on the calculation rules that you define, the system adds or subtracts account values that you include in the analysis and stores the results in the totals field. For example, if you deduct expense accounts and add revenue accounts during analysis calculations, the total reflects revenues minus expenses.

Specify the parameters for the report on this page.

Click Run to generate the report online.

Report Results

When you execute the report the system displays the results in an analytic grid.

Display Variance	Specify whether you want to view the variance as an Amount, Amount and Percent, or Percent.
Variance Filter Criteria	You can choose to display all variances or specific variance amounts or ranges. Click Refresh to display new data after you modify the variance filter criteria.
Selection Criteria	Click the link to return to the Variance Analysis page where you can redefine the report parameters and rerun the report.
Workspace	Click the link to go to the Planning Workspace page with these parameters.
Preferences	Click to access a window where you can modify the layout of the grid by hiding some of the columns.
Budget Period	Select the budget period against which you want to compare.
Currency Code	Specify the entry currency that you want to view in your analysis. This field is hidden when Statistical Account Analysis is selected on the criteria page.
Statistics Code	Specify the statistics code that you want to display.

In the analytic grid you can modify the view of the data by dragging any dimension from the row axis to the slicer bar or column axis, and by dragging cubes from the column axis to the slicer bar or row axis.

The available dimensions are based on the selected dimensions for the activity.

Comparing Budget Versions

Access the Version Analysis page.

Use the online inquiry page to access and load line item activity data to an analytic grid for analysis. You can compare one to all budget versions for a single activity, scenario and planning center in the planning model.

Note. You must have full access to the Line Item Budgeting activity for your planning center to run these analysis reports. When you click Run the system checks whether you have full access to the planning center version. If you have only partial access, the system displays an error message that you cannot run this report unless you have access to every account in this planning center.

For line item analysis, Planning and Budgeting uses the analysis calculation definition that you enter for account types on the Account Type Options page. Depending on the calculation rules that you define, the system adds or subtracts account values that you include in the analysis and stores the results in the totals field. For example, if you deduct expense accounts and add revenue accounts during analysis calculations, the total reflects revenues minus expenses.

Specify the parameters for the report on this page.

Click Run to generate the report online.

Report Results

When you execute the report the system displays the results in an analytic grid.

Selection Criteria	Click the link to return to the Version Analysis page where you can redefine the report parameters and rerun the report.
Workspace	Click the link to go to the Planning Workspace page with these parameters.
Preferences	Click to access a window where you can modify the layout of the grid by hiding some of the columns.
Budget Period	Select the budget period against which you want to compare.
Currency Code	Specify the entry currency that you want to view in your analysis. This field is hidden when Statistical Account Analysis is selected on the criteria page.
Statistics Code	Specify the statistics code that you want to display.

In the analytic grid you can modify the view of the data by dragging any dimension from the row axis to the slicer bar or column axis, and by dragging cubes from the column axis to the slicer bar or row axis.

The available dimensions are based on the selected dimensions for the activity.