1. Administering Analytics and Reports for HCM
  2. Create Optimized Cross Subject Area Reports using Static Filters

Create Optimized Cross Subject Area Reports using Static Filters

Use static report filters to build optimized HCM OTBI cross subject area reports.

Some cross subject area reports may be costly in terms of response time, if they are not designed correctly. It is recommended you use at least -

  • One common dimension

  • One measure from each of the participating subject areas

  • Some filters on common dimension attributes for cross subject area OTBI reports.

In addition to the above three recommendations, you can improve the performance of the cross subject area reports, using this technique which:

  • Encapsulates the report-filtering logic in a dashboard prompt that generates a list of desired assignment_id values

  • Executes the cross subject area report for these specific assignment_id values.

Let's look at the tasks required to create this optimized cross subject area reports

  • Push the static report filters in a dashboard prompt

  • Fetch the list of assignment identifiers using the prompt's definition

  • Pass these assignment identifiers back to the main report

  • Data in main report is filtered on assignment identifiers

Note: In case of cross subject area reports, each single subject area physical SQL gets filtered on the selected assignment identifiers, thereby improving the overall performance of the cross subject area report.

Before you start

You need to build the following Webcat objects:

  1. Separate Report that has only filtering logic

  2. Hidden prompt based on report's filter

  3. OTBI HCM Report.

Create Separate_Report that has only filtering logic

  1. Review your current OTBI report's filters

  2. Build a separate report, which has the same filtering logic, as used in your report.

  3. Define filters on common dimension columns such as Worker, Job, Grade, Dept, and more.
    Note: You need to include the "Worker"."Assignment_ID" column.

For example: If your original report has filters on Country_Code and System_Person_Type, then use the same filters in the Separate_Report.

Define Hidden Prompt

  1. Define a column prompt: "Worker"."Assignment Identifier"

  2. In Check Boxes Values, select SQL Results, which is the default selection.

    View the sample Logical_SQL associated with this prompt.

SELECT
"Worker"."Assignment ID"
FROM "Compensation - Salary Details Real Time"
WHERE
((DESCRIPTOR_IDOF("Worker"."System Person Type") = 'EMP')
AND ("Location"."Country Code" <> 'US'))
FETCH FIRST 65001 ROWS ONLY

OTBI HCM report

Note: In the Criteria tab, set the Assignment Identifier as is marked.

View the Logical_SQL definition of a cross-SA report with three subject areas:

SELECT
0 s_0,
"Business Unit"."Business Unit Name" s_1,
"Grade"."Grade Name" s_2,
"Job"."Job Name" s_3,
"Location"."Worker Location Name" s_4,
"Salary Basis"."Salary Frequency" s_5,
"Salary"."Salary Currency" s_6,
"Worker"."Assignment ID" s_7,
"Worker"."Employee Name" s_8,
"Worker"."Person Number" s_9,
DESCRIPTOR_IDOF("Business Unit"."Business Unit Name") s_10,
DESCRIPTOR_IDOF("Job"."Job Name") s_11,
DESCRIPTOR_IDOF("Salary Basis"."Salary Frequency") s_12,
"Salary"."Annualized Salary" s_13,
"Workforce Management - Person Real Time"."Person"."Person Count" s_14,
"Workforce Management - Worker Assignment Real Time"."Worker Assignment"."Head Count" s_15
FROM "Compensation - Salary Details Real Time"

Hide the Prompt

  1. Select Page setting > Hide the Prompt

  2. Edit Dashboard > page 1 > Section 1 > Wheel icon > Format Section > Custom CSS Style Options (HTML Only) > Use Custom CSS Style:Display:none