PMO Accelerator Advanced Reporting Setup

ccppmop1592
To view the reports with updated information, set up the jobs, calendar, time slices, language, locale, timezone, and other required items:
 
 
2
 
 
PMO Accelerator Advanced Reporting Data Warehouse Calendar Periods and FTE Calculations
The calendar periods in reports and the amounts that are converted to FTE depend on the Data Warehouse calendar table. This table is populated by running the 
Load Data Warehouse
 job. By default, this job populates calendar periods for 365 daily periods back and 365 daily periods forward, 52 weekly periods back and 52 weekly periods forward, and 36 monthly periods back and 36 monthly periods forward.
If you need to change the number of calendar periods, you must consistently change all of the Data Warehouse slice requests for that number of periods. Although there is no limit for the number of periods, if you select a large timeframe the 
Time Slicing
 job will take longer to complete and you may experience performance issues.
For period type of week to display data, there are two places in 
Classic PPM
 that must have consistent start days of the week:
  • The start day of the week for the report column headings is retrieved from the 
    First Day of Work Week
     field set in 
    Classic PPM
     (
    Administration/Project Management-Settings
    ).
  • The time slices referenced by the report must all start on the day that you set as the first day of the work week.
The amounts being converted to FTE depend on the Data Warehouse calendar table.  This table is populated by running the 
Load Data Warehouse
 job.  This job populates calendar periods and calculates weekly and monthly FTE amounts based on the availability of the resource with Resource ID of 'admin' and the time slice with the name WEEKLYRESOURCEAVAILCURVE or MONTHLYRESOURCEAVAILCURVE (
Administration/Data Administration-Time Slices
), respectively.
There are twelve time slice requests used for advanced reporting. Of these twelve time slice requests, eleven of them are used via the Data Warehouse. There is only one time slice request used for advanced reporting that is not a Data Warehouse request. See the 
Administration Guide
 for more details regarding the Data Warehouse time slice requests.
The Data Warehouse time slice requests default to the following time periods, based on the database server system date:
  • One year back and forward for Daily. The From Date of the slice request is one year prior to the system date.
  • One year back and forward for Weekly. The From Date is one year prior to the system date and is the same day as the 
    First Day of Work Week
    . To locate the 
    First Day of Work Week
     field, open the Administration menu and from Project Management, click Settings.
  • Three years back and forward for Monthly. The From Date is three years prior to the system date and is always the first day of the month.
PMO Accelerator Advanced Reporting and Data WareHouse Jobs
The following 
Classic PPM
 jobs are required by reports; however, which jobs are required depends on the report. This documentation includes Report Prerequisites and Report Security sections that detail the jobs required to populate data in each report.
  • Create and Update Jaspersoft Users
  • Load Data Warehouse
  • Load Data Warehouse Access Rights
  • Time Slicing
  • Update Report Tables
  • Post Timesheets
  • Post Transactions to Financials
  • Post to WIP
  • Import Financial Actuals
  • Investment Allocation
  • Update Earned Value and Cost Totals
  • Synchronize portfolio investments
The 
Load Data Warehouse
 and 
Load Data Warehouse Access Rights
 jobs are required by almost all reports. These jobs populate dimensions, facts, and lookup tables in the Data Warehouse (DWH). If the Data Warehouse is not populated, the reports display a message that there are no matching records found and most of the report parameters do not display data.
 If not already active, activate the Load Data Warehouse and Load Data Warehouse Access Rights jobs before you run them.
The 
Load Data Warehouse
 job extracts the data from the 
Classic PPM
 database, transforms the data, and loads the data into the Data Warehouse. The job populates the Data Warehouse for stock objects and attributes and any custom objects and attributes that have been explicitly enabled for inclusion in the Data Warehouse. The job also updates the advanced reporting domains if you have set up advanced reporting with Jaspersoft.
 Limit any long string attributes that you enable for the Data Warehouse to a maximum of 1000 characters. The Data Warehouse database includes only the first 1000 characters from long string attributes.
Run the 
Load Data Warehouse
 job with the Full Load option selected in the following cases:
  • You add a new language in the Data Warehouse system options. Running an incremental load of the job does not update the Data Warehouse with the new language.
  • You change the entity for fiscal periods in the Data Warehouse options.
  • You change the 
    First Day of the Work Week
     field set in 
    Classic PPM
     (
    Administration/Project Management-Settings
    ).
The majority of attributes installed by the PMO Accelerator are enabled for inclusion in the Data Warehouse. These attributes have the Included in the Data Warehouse field checked. Do 
not
 uncheck this field because reports depend upon them. The PMO Accelerator attribute IDs are renamed when their respective columns are created in the Data Warehouse.
 If you uncheck any PMO Accelerator attributes to exclude from the Data Warehouse, the reports and input controls that reference these fields will no longer work.
PMO Accelerator Advanced Reporting Time Slices
 By default, the Data Warehouse time slice requests are set consistently for each slice period (Daily, Weekly, Monthly). If you edit these slice requests, set the From Date and Number of Periods to be the same for each slice period type. If these values are not set correctly, the data in the Data Warehouse will be inconsistent. For more information about editing the time slices, see Configure the Data Warehouse and Advanced Reporting. If you need to change any of the slices, you must consistently change all of the Data Warehouse slice requests for that slice period type.
Specifically, follow these rules when setting the From Date and Number of Periods for the Data Warehouse time slice requests:
  • There is only one daily time slice request referenced by reports. The DAILYRESOURCEAVAILCURVE time slice request is used by the Missing Time report. See the 'Report Prerequisites' section of the Missing Time report for more details. The recommended setting for this slice request is in the following table.
Time Slice Name
Item
From Date
Slice Period
Number of Periods
DAILYRESOURCEAVAILCURVE
Availability
The first day of the month, six months prior to the current month.
Daily
400
  • For all weekly time slice requests, the From Date must be the same day as the 
    First Day of the Work Week
     set in Administration under Settings, Project Management. For example, if the 
    First Day of the Work Week
     is a Monday, then all weekly time slice requests must be set with a From Date that is a Monday.
  • For all monthly time slice requests, the From Date must be always the first of a month.
The time slice requests (
Administration/Data Administration-Time Slices
) in the following table are referenced by reports. The time slice requests must be configured to cover the periods displayed in the reports or the reports will not display data for those periods. The individual report sections of this document indicate which reports reference the time slices so you may search the document to determine which reports are dependent upon them.
 
Time Slice Name
 
 
Item
 
 
Slice Period
 
 
Data Warehouse
 
DAILYRESOURCEAVAILCURVE
Availability
Daily
No
DAILYRESOURCETIMECURVE
Time Entry
Daily
Yes
WEEKLYRESOURCEACTCURVE
Actuals
Weekly
Yes
WEEKLYRESOURCEALLOCCURVE
Allocations
Weekly
Yes
WEEKLYRESOURCEAVAILCURVE
Availability
Weekly
Yes
WEEKLYRESOURCEESTCURVE
Estimates
Weekly
Yes
MONTHLYRESOURCEACTCURVE
Actuals
Monthly
Yes
MONTHLYRESOURCEALLOCCURVE
Allocations
Monthly
Yes
MONTHLYRESOURCEAVAILCURVE
Availability
Monthly
Yes
MONTHLYRESOURCEBASECURVE
Baseline
Monthly
Yes
MONTHLYRESOURCEESTCURVE
Estimates
Monthly
Yes
MONTHLYRESOURCEHARDALLOC
Hard Allocation
Monthly
Yes
 
PMO Accelerator Advanced Reporting Populate Option
Beginning with Release 14.3.0.3, most reports include the Populate check box to improve the performance of certain query-based parameters. Prior to this patch, you might experience slow performance in the following areas:
  • Clicking a report to view the input control page and report – this page might be slow to render
  • Clicking a specific query-based input control in the report to select a value – the input control might be slow in returning all the values
The query-based parameters which contain the new Populate check boxes in reports are:
  • Investment
  • Project
  • Resource
  • Role
  • Primary Role
  • Investment Manager
  • Project Manager
  • Business Owner
  • Resource Manager
  • Booking Manager
The following query-based parameters also include extra modifications to improve performance: Investment, Project, and Resource. These parameters are now cascading based on the OBS Unit and Manager you select. For example, if you select an Investment OBS Unit, the Investment parameter now only displays those investments that are directly linked to that OBS unit or descendants.  Likewise, if you select an Investment Manager, the Investment parameter only displays investments for that manager.
For example, the Investment parameter has a 
Populate Investments?
 check box. Performance is optimum because no investments appear. To view investments, select the 
Populate Investments?
 check box. If you have also selected an Investment OBS Unit, then only the investments that are associated to the unit or a descendant appear. Likewise, if you have selected an Investment Manager, only the investments for the manager appear.
The following image shows the Investment parameter and list of investments after you select the 
Populate Investments?
 check box.
 
The image shows the Investment parameter displaying a list of investments after you select the Populate Investment check box.
 
 
PMO Accelerator Advanced Reporting Language, Locale, and Time Zone
The language, locale, and time zone settings that are selected in 
Classic PPM
 (
Home, Account Settings, Personal Information
) are passed to Jaspersoft and apply to the reports as follows:
  • The language setting that is selected in 
    Classic PPM
     applies to report and parameter labels and the data returned in the report.
  • The locale setting that is selected in 
    Classic PPM
     applies to number, time, and date formats.
  • The time zone setting that is selected in 
    Classic PPM
     applies to the report run date and time that appear in the footer of all reports.
The time zone setting that is selected in 
Classic PPM
 does not apply to business dates in reports. These dates are not adjusted for time zone conversions. Also, business dates in reports might not match if they are configured in 
Classic PPM
 to show the time because time zone conversion is applied.
To display consistent time zone in 
Classic PPM
 and reports, the Jaspersoft server must be set to the same time zone as the 
Classic PPM
 application and database servers. 
 A known issue prevents the Advanced Reporting repository folder structure and sub-items (including reports, input controls, and templates) from appearing in their localized form. The item names and their descriptions appear in English for all languages. Input controls display the $R{} expression that is used by Jaspersoft for localization. This expression is only converted when running the reports; however, the report output is fully localized. See 
Change Impact and Upgrade
 for more details.
 
PMO Accelerator Advanced Reporting Source Files
The report source files are included in a zip file (jaspersoft_csk.zip) and are available after the installation in the 
Classic PPM
 reporting content folder: <install>\reporting\content\csk\jaspersoft\package. If a patch is installed, any updated report source files will be included in a patch zip file (jaspersoft_csk_upgrade_[patch_number].zip).
The JRXML report files require the Jaspersoft Studio report designer development tool. The Jaspersoft Studio report designer is an eclipse-based development tool that can be downloaded and installed locally on your computer. See the 
CA
 
Clarity
Release Notes
 for the supported version of Jaspersoft Studio.
When you extract the contents of the zip file, the report source files are available under \resources\organizations\org_template\ca_ppm\reports followed by their corresponding management area. The source files in the following table are included:
 
Report
 
 
Source Files
 
Capacity vs. Allocation by OBS
\resource_management\CSK_RES_CapVsAllocByOBS_files
Capacity vs. Booking Status by OBS
\resource_management\CSK_RES_CapVsBookingByOBS_files
Capacity vs. Demand by Resource
\resource_management\CSK_RES_CapVsDemandByResource_files
Capacity vs. Demand by Role
\resource_management\CSK_RES_CapVsDemandByRole_files
Data Warehouse Schema
\administration\CSK_ADM_DataWarehouseSchema_files
Financial Budget vs. Forecast by Period
\financial_management\CSK_FIN_BudgetForecastByPeriod_files
Financial Budget vs. Forecast by Period Detail
\financial_management\CSK_FIN_BudgetForecastByPeriodDetail_files
Financial Capitalization by Investment
\financial_management\CSK_FIN_CapitalizationByInv_files
Financial Capitalization Detail
\financial_management\CSK_FIN_CapitalizationByInvDetail_files
Financial Forecast Review by Investment
\financial_management\CSK_FIN_ForecastReviewByInv_files
Financial Forecast Review by Plan Grouping
\financial_management\CSK_FIN_ForecastRevByPlanGroup_files
Investment Allocations and Assignments
\investment_management\CSK_INV_AllocationAssignment_files
Investment Assignments by Task
\investment_management\CSK_INV_AssignmentsByTask_files
Investment Baseline vs. Plan by Task
investment_management\CSK_INV_BaseVsPlanByTask_files
Investment Transaction Inquiry
\financial_management\CSK_FIN_InvTransactionInquiry_files
Key Task and Milestone Status
\project_management\CSK_PRJ_KeyTaskMilestoneStatus_files
KPIs by Project Type
\project_management\CSK_PRJ_KPIsByProjectType_files
Missing Time
\time_management\CSK_TME_MissingTime_files
Over/Under Allocation by Resource
\resource_management\CSK_RES_OverUnderAllocation_files
Over/Under Allocation by Role
\resource_management\CSK_RES_OverUnderAllocByRole_files
Portfolio Plan Changes
\portfolio_management\CSK_PFM_PortPlanChanges_files
Portfolio Plan Comparison
\portfolio_management\CSK_PFM_PortPlanComparison_files
Clarity
Schema
\administration\CSK_ADM_PPMSchema_files
Program Budget vs. Forecast by Period
\program_management\CSK_PRG_BudgetForecastByPeriod_files
Program Budget vs. Forecast by Period Detail
\program_management\CSK_PRG_BudgetForecastByPeriodDetail_files
Program Milestone Dependencies
\program_management\CSK_PRG_MilestoneDependencies_files
Program Status Detail
\program_management\CSK_PRG_ProgramStatusDetail_files
Project Change Request Register
\project_management\CSK_PRJ_ChangeRequestRegister_files
Project Cost and Effort
\project_management\CSK_PRJ_ProjectCostAndEffort_files
Project Earned Value
\project_management\CSK_PRJ_EarnedValue_files
Project Issue Register
\project_management\CSK_PRJ_IssueRegister_files
Project Portfolio Summary
\project_management\CSK_PRJ_PortfolioSummary_files
Project Risk, Issue, and Change Summary
\project_management\CSK_PRJ_RiskIssueChangeSummary_files
Project Risk Register
\project_management\CSK_PRJ_RiskRegister_files
Project Status Detail
\project_management\CSK_PRJ_ProjectStatusDetail_files
Project Status Report List
\project_management\CSK_PRJ_StatusReportList_files
Project Status Summary
\project_management\CSK_PRJ_ProjectStatusSummary_files
Project Storyboard
\project_management\CSK_PRJ_ProjectStoryboard_files
Project Task Dependencies
\project_management\CSK_PRJ_TaskDependencies_files
Resource Allocations and Assignments
\resource_management\CSK_RES_AllocationAssignment_files
Resource Assignments by Task
\resource_management\CSK_RES_AssignmentsByTask_files
Resource Availability
\resource_management\CSK_RES_ResourceAvailability_files
Resource Baseline vs. Plan by Employment Type
\resource_management\CSK_RES_BaseVsPlanByEmpType_files
Resource Forecasted Utilization
\resource_management\CSK_RES_ForecastedUtilization_files
Resource Forecasted Utilization Detail
\resource_management\CSK_RES_ForecastedUtilDetail_files
Resource Skills
\resource_management\CSK_RES_Skills_files
Resource Time Review by Manager
\time_management\CSK_RES_ResTimeReviewByMgr_files
Resource Time Summary and Detail
\time_management\CSK_RES_TimeSummaryAndDetail_files
Time Compliance
\time_management\CSK_TME_Compliance_files
Time Compliance Detail
\time_management\CSK_TME_ComplianceDetail_files
User Access Rights
\administration\CSK_USR_AccessRights_files
User Access Rights Detail
\administration\CSK_USR_AccessRightsDetail_files
User License and Activity
\administration\CSK_USR_LicenseActivity_files
User License and Activity Detail
\administration\CSK_USR_LicenseActivityDetail_files
 
PMO Accelerator Advanced Reporting Data Sources
The data sources define how and where to obtain the data displayed by ad hoc views, reports, and input controls. The Advanced Reporting content includes two data sources as follows:
  • Clarity
    BEAN – 
    Classic PPM
     Bean Data Source
The
Clarity
BEAN data source is a bean data source type required by the Advanced Reporting content to access the 
Classic PPM
 transactional schema.
  • Clarity
    DWH BEAN – 
    Classic PPM
     Data Warehouse Bean Data Source
The
Clarity
DWH BEAN data source is a bean data source type required by the Advanced Reporting content to access the Data Warehouse schema.
Almost all reports and input controls are using the 
Classic PPM
 DWH BEAN data source. Some reports display data that is not available in the Data Warehouse and for this reason they are using the 
Classic PPM
 BEAN data source. Each report has a 'Report Properties' section that indicates which data source is referenced by the report. The data source connections must be valid for the Advanced Reporting content to work. You can use the Test Connection button to validate the data source connection. The Test Connection button is available when you edit the data source (
Home/Personal/Advanced Reporting/View/Repository/<organization>/
Clarity
/Data Sources-Edit
). If the validation fails, contact your administrator to ensure that the databases are running. The Bean Name and Bean Method properties defined in the data source properties should not be modified.