1. Reference for Fusion HCM Analytics
  2. Report Authoring Tips

A Report Authoring Tips

This section provides tips and guidelines for creating effective and timely reports.

Topics

Common Report Authoring Tips

Improve all of your Oracle Fusion Analytics Warehouse reports with these recommendations and answers to frequently asked questions regarding reporting. The information isn’t exhaustive and is updated regularly with additional information and authoring tips.

Tips for Filters

When applying filters to reports, follow these guidelines:

  • Use dashboard filters instead of workbook or canvas filters for user interactions in analyses.
  • Use workbook filters for hidden and non-interaction filters.
  • Set the Limit by Values to None for all filters in custom workbooks or dashboards to improve performance of prompts.

Tips for Brushing

Disable brushing to improve report performance. See Update Canvas Properties.

Tips for Working with Currency

To display the currency format for currency amounts, set the number format in each visualization as a custom currency. Then in the subject area folder, select the currency column. See Set Currency Symbols for Visualizations.

Tips for Reporting on Attributes Across Multiple Dimensions

When reporting on attributes against multiple dimensions, always use a measure in the report. In subject areas with more than one fact, the measure sets the correct context in the query, builds an accurate navigational path, and returns the expected results. If you don’t want the measure to show in the report, hide it. See Hide or Delete a Column.

Tips for Presentation Hierarchies

To improve performance, use presentation columns instead of presentation hierarchies. See Work with Presentation Tables and Columns.

Report Authoring Tips for Oracle Fusion HCM Analytics

Improve your Oracle Fusion HCM Analytics reports with these recommendations and answers to frequently asked questions regarding reporting. The information isn’t exhaustive and is updated regularly with additional information and authoring tips.

Tips for Improving Performance of all HCM Reports

  • Use the prebuilt fact metrics whenever possible under the subject area you're using for your report. For each subject area, select the closest or most appropriate prebuilt metric for your reports, and then apply filters as needed.
    Description of workforcecoreprebuiltmetrics.png follows
    Description of the illustration workforcecoreprebuiltmetrics.png

    Hover over the metric name to see a description of the metric to use for your workbook. For example, to get a headcount of employees use the Employee Headcount metric.
    Description of employeeheadcountdetails.png follows
    Description of the illustration employeeheadcountdetails.png

    See Prebuilt in this guide for details.
  • Use prebuilt metrics to build custom calculations.
  • Validate each customization for accuracy.
  • Review the latest updates in this publication (Reference for Oracle Fusion HCM Analytics) to learn about the most appropriate objects and methods for creating customized reports.

Tips for Improving Performance of Reports with Department or Organization Hierarchy Folders

When creating a report that uses attributes from the Department or Organization Hierarchy folders, set filters for Tree Version Effective Start Date and Tree Code to improve performance.

Tips for Improving Performance of Reports on Top Manager, Manager Hierarchy, Assignment Manager List, or Bottom-Up Manager Hierarchy Queries

To improve the performance of Top Manager, Manager Hierarchy, Assignment Manager List, or Bottom-Up Manager Hierarchy queries, follow these guidelines:

  • Run the refresh process Refresh Manager Hierarchy with a null parameter and then with a zero (0) parameter in Fusion Applications prior to running the ETL to see the data in the Manager Hierarchy or Assignment Manager List folders.
  • Filter on Manager Name and Time: Year, Quarter, Month, or Date when any user other than Line Manager runs a report with attributes from the Manager Hierarchy or Assignment Manager List folders in either the select list or the filter list because data in these hierarchy folders are date effective.
  • Use attributes from the Assignment Manager List folder (instead of from the Manager Hierarchy folder) when you aren't drilling to Manager Hierarchy to improve report performance.

Tips for Improving Performance of Reports on Worksheet Manager Hierarchy or Worksheet Manager Hierarchy List in HCM – Workforce Rewards – Workforce Compensation Queries

To improve performance on Worksheet Manager Hierarchy or Worksheet Manager Hierarchy List in HCM – Workforce Rewards – Workforce Compensation queries, follow these guidelines:

  • Run the Synchronize Hierarchy process by providing Plan Name and Cycle Name as parameters in Fusion Applications prior to running the ETL.
  • Filter on Manager Name when any user other than Line Manager runs a report that has attributes from the Worksheet Manager Hierarchy or Worksheet Manager Hierarchy List folders either in the select list or in the filter list.
  • Use attributes from the Worksheet Manager Hierarchy List folder (instead of from the Worksheet Manager Hierarchy folder) when you aren't drilling to Manager Hierarchy to improve report performance.

Tips for Improving Performance of Reports on HCM - Workforce Core Subject Area Queries

To improve performance on Reports in HCM – Workforce Rewards – Workforce Compensation queries, follow these guidelines:

  • When reporting on headcounts, use a date filter applied to the most recent refresh date (or month) to report on the latest snapshot of data. Using To_DateTime(VALUEOF(NQ_SESSION.CURRENT_GREGORIAN_DAY), 'DD-Mon-YY') significantly reduces the time and resources necessary to run the report.
  • Filter using event-based flags

Tips for Improving Performance of Reports on Headcounts

When creating a report using the Headcount metric, the returned value is the last day of the selected period, or if you do a daily refresh, is the most recent snapshot refresh date. If you use the Headcount metric without a filter, you will only see the most recent snapshot. Apply a date filter on the most recent refresh date to select only the most recent data.

Use the expression To_DateTime(VALUEOF(NQ_SESSION.CURRENT_GREGORIAN_DAY), 'DD-Mon-YY') to create the filter on the most recent date, which significantly reduces the query time and resources to return the data you want.
Description of lastrefreshfilter.png follows
Description of the illustration lastrefreshfilter.png

You can use the same principle to select headcount by month. Since headcount by month is returned using the last day of the month, apply an explicit filter to use the last day of the month and the most recent snapshot date to improve performance.
Description of monthendfilter.png follows
Description of the illustration monthendfilter.png

Tips for Using Event-Based Flag Filters

The Assignment Event fact table records all changes and events related to an employee's employment. Use the provided filters to get the results you want. For example, to see Employee Hires by Month, you can use the action type EMPL_ADD_EMP to select new employee hires.


Description of editcalculation.png follows
Description of the illustration editcalculation.png

In this example, you don't have any filters applied to the Assignment Event fact table. You can use the HIRE_EVENT_IND flag to identify hire events and optimize performance by filtering directly on the fact table.

Note:

You'll need to use the semantic model extension to use this flag if it's not already available in the subject area.
  1. Create a new step for Add Column.
    Description of stepdialog.png follows
    Description of the illustration stepdialog.png

  2. Add the HIRE_EVENT_IND physical column from the fact table and map it to a new logical column named Hire Event Ind. Be sure the new column is set to Display.
  3. Save and publish the new step.
    Description of addphysicalcolumn.png follows
    Description of the illustration addphysicalcolumn.png

    In the folder where you published the new step, you see the new Hire Event Ind column.

  4. In the Employee Hire column calculation in the workbook, replace the EMPL_ADD_EMP filter with a new calculation Hire Event IND = 1, and name it New Employee Hires. Add the filter to select employees only.
    Description of editcalculationnewemployeehires.png follows
    Description of the illustration editcalculationnewemployeehires.png

Tips for Improving Performance of Reports on HCM - Diversity Analysis Subject Area Queries

To improve performance on HCM - Diversity Analysis subject area queries, follow these guidelines:

  • When a report uses any statistical attributes from the folders Promotion Details (Age), (Ethnicity), (Gender) or Termination Details; (Age), (Ethnicity), (Gender) or Salary Details; (Age), (Ethnicity), or (Gender), you need to include the following columns in the report to ensure the statistical attributes are calculated at the correct grain:
    • Reporting Establishment.Reporting Establishment Name
    • Time.Year
    • Legislative Job Category.EEO-1 Category
    • Country.Country Name
    • If you are reporting on Age, Ethnicity, or Gender, also select Age code, Ethnicity, or Gender from the same folder as the statistical attributes.
  • When a report has any statistical attributes from the folders Hiring Details (Age), (Ethnicity), or (Gender), you need to include the following columns in the report to ensure the statistical attributes are calculated at the correct grain:
    • Time.Year
    • Location Details. Location Name
    • Legislative Job Category.EEO-1 Category
    • Country.Country Name
    • If you are reporting on Age, Ethnicity, or Gender, also select Age code, Ethnicity, or Gender from the same folder as the statistical attributes.

Tips for Improving Performance of Reports on HCM - Learning Subject Area Queries

When building a report using the HCM - Learning subject area, include a Time dimension attribute such as Time.Date in the report.

Tips for Improving Performance of Reports on HCM – Talent Acquisition Subject Area Queries

When applying a filter on an attribute, use a Code column (when available) instead of a Name or Description column.

Tips for Improving Performance of Reports on HCM - Workforce Time and Labor Management Subject Area Queries

When designing visualizations, always define default values for the prompts, and choose the most selected values as defaults.

Tips for Using Timestamp Fields As Filters in Reports On HCM - Health And Safety Incidents Subject Area Queries

When filtering on Timestamp fields, use "is greater than or equal to", "is greater than", "is less than or equal to", "is less than", or "between".

Recommended Filters

Subject Area Presentation Columns (* Required Filter)
HCM – Workforce Core
  • Time – Year/QTR/Month* or Time – Enterprise Year/Enterprise Quarter/Enterprise Period*
  • Business Unit.Business Unit Name
  • Country.Country Name
  • Job Family.Job Family Name
HCM - Workforce Absence Management

Content based on facts in the Absences folder:

  • Time – Year/QTR/Month* or Time – Enterprise Year/Enterprise Quarter/Enterprise Period*
  • Business Unit.Business Unit Name

Content based on facts in the Employment Plan Participation folder:

  • Time – Year/QTR/Month* or Time – Enterprise Year/Enterprise Quarter/Enterprise Period*
  • Business Unit.Business Unit Name
  • Absence Plan.Plan Type

When reporting on Accrual Balance, you must include one of these filters:

  • Accrual Balance Details.Balance Calculation Date
  • Attribute Accrual Balance Details.Accrual Period End Date
HCM - Workforce Rewards - Salary Basis
  • Time – Year/QTR/Month* or Time – Enterprise Year/Enterprise Quarter/Enterprise Period*
  • Business Unit.Business Unit Name
HCM - Goals and Career Development

Content based on Performance Goals-related measures:

  • Review Period.Review Period Name*
  • Performance Goal Plan.Goal Plan Name

Content based on Development Goals related measures:

  • Assignment Manager List.Manager Name
HCM - Profile Matching
  • Job Profile.Profile Name*
  • Model Job.Job Name

When reporting on content based on attributes from Person Profile – Skill Gap Details, add these filters:

  • Person Profile – Skill Gap Details.Content Type*
  • Person Profile – Score Details.Percentile
HCM - Workforce Rewards - Payroll Balances

Time – Year/QTR/Month: Filter using smaller time periods to improve performance

Payroll Period – Payroll Period Name (in particular when the report is at Person Grain)

Payroll.Payroll Name

When using Balance Value (base measure) in a report, apply a filter on Balance Dimension.Balance Dimension Name.to prevent metric values from being multiplied by the number of configured balance dimensions
HCM - Workforce Rewards - Payroll Costing

Time – Year/QTR/Month: Filter using smaller time periods to improve performance).

Element – Element Classification

Use at least one segment from the Costing Segments folder; use the Segment code wherever possible

Payroll.Payroll Name
HCM - Diversity Analysis

Promotions/Salary/Terminations-based reports:

Time.Year*

Reporting Establishment.Reporting Establishment Name

Legislative Job Category.EEO-1 Category (Job Category)

Country.Country Name

Hiring-related reports:

Time.Year*

Location Details.Location Name

Legislative Job Category.EEO-1 Category (Job Category)

Country.Country Name

HCM – Succession Management Time – Year or Time – Enterprise Year
HCM - Performance Management
  • Time – Year or QTR or Month* or Time – Enterprise Year or Enterprise Quarter or Enterprise Period*
  • Performance Document.Performance Document Name
  • Review Period.Review Period Name
HCM - Talent Profile
  • Business Unit.Business Unit Name

When reports use metrics from a Model profile based on a Job or Position, use one of these filters:

  • Job Model Profile Details.Profile Name
  • Position Model Profile Details.Profile Name

When a report has metrics from Person Profile, use the filters from: Person Profile - Skills and Qualifications or Talent Ratings
HCM - Workforce Gains and Losses

Time.Year or QTR or Month* or Time – Enterprise Year or Enterprise Quarter or Enterprise Period*

This subject area provides gains and losses based on various Assignment events, so when reporting on this subject area, use a filter from the list below to identify gains and losses from a particular type of event:

  • Gain and Loss Details.Business Unit Gain Loss Flag
  • Gain and Loss Details.Department Gain Loss Flag
  • Gain and Loss Details.Full Time/ Part Time Gain Loss Flag
  • Gain and Loss Details.Grade Gain Loss Flag
  • Gain and Loss Details.Job Gain Loss Flag
  • Gain and Loss Details.Location Gain Loss Flag
  • Gain and Loss Details.Position Gain Loss Flag
  • Gain and Loss Details.Supervisor Gain Loss Flag
HCM - Talent Review Meetings Time - Year or QTR or Month* or Time – Enterprise Year or Enterprise Quarter or Enterprise Period *
HCM - Check-Ins

Time - Year or QTR or Month* or Time – Enterprise Year or Enterprise Quarter or Enterprise Period*

Review Period.Review Period Name
HCM – Journeys

Time – Year or QTR or Month* or Time – Enterprise Year or Enterprise Quarter or Enterprise Period*

Allocated Journeys.Allocated Journey Name

Manager Information.Manager Name
HCM - Workforce Rewards - Workforce Compensation

Compensation Plan Cycle.Plan Cycle Name*

Business Unit.Business Unit Name

Compensation Plan.Plan Name*
HCM – Worker Seniority Seniority Details.Seniority Rule Name*
HCM - Learning

Time – Year or QTR or Month *

Common folder: Business Unit Name or Department Name or Location Name

Learning Records: Learning Item Type

Learning Enrollments: Enrolled on Date/ Enrollment Record Due Date
HCM - Talent Acquisition

Time - Year/QTR/Month/Date*

Organization.Organization Name
HCM – Workforce Time and Labor Management

Time – Year or QTR or Month*

Reported Time Card Start Date and Time

Reported Time Card End Date and Time

Common folder: Business Unit Name or Department Name or Legal Employer name.
HCM - Health and Safety Incidents

Time - Year or QTR or Month or Date*
HCM - Positions

Time – Year or QTR or Month*