Missing periods in Jaspersoft ad-hoc view
search cancel

Missing periods in Jaspersoft ad-hoc view

book

Article ID: 246959

calendar_today

Updated On:

Products

Clarity PPM On Premise

Issue/Introduction

There are missing periods in the default Ad-hoc reporting field(Resource management domain - Calendar periods - Team - Year) where as the same is available in the application. Planned resources allocations on project level till 2040. We increased amount of periods for monthly, fiscal time slices for team allocation curve. The Data warehouse job is run in full mode but only periods till 2025 is found.

Environment

Clarity 16.0.2 Data Warehouse and Jaspersoft Reporting server 7.8

Cause

The missing periods in the YEAR field is related to monthly Data Warehouse slice configuration, which does not give the required duration and is only till 2025.

Resolution

Query that is run against the Data Warehouse schema behind the scenes for the YEAR field 

select top 200001 "DWH_CMN_PERIOD_M_V"."YEAR_NAME" as "DWH_CMN_PERIOD_M_V_YEAR_NAME"
from "ppm_dwh"."DWH_RES_RESOURCE" "DWH_RES_RESOURCE"
inner join "ppm_dwh"."DWH_RES_PER_FACTS_M_V" "DWH_RES_PER_FACTS_M_V" on ("DWH_RES_RESOURCE"."RESOURCE_KEY" = "DWH_RES_PER_FACTS_M_V"."RESOURCE_KEY")
inner join "ppm_dwh"."DWH_CMN_PERIOD_M_V" "DWH_CMN_PERIOD_M_V" on ("DWH_RES_PER_FACTS_M_V"."PERIOD_KEY" = "DWH_CMN_PERIOD_M_V"."PERIOD_KEY" and "DWH_CMN_PERIOD_M_V"."LANGUAGE_CODE" = 'en')
group by "DWH_CMN_PERIOD_M_V"."YEAR_NAME"
order by "DWH_CMN_PERIOD_M_V_YEAR_NAME"

The issue was narrowed down to the monthly timeslice periods missing in the Data warehouse view DWH_CMN_PERIOD_M_V. The view used for segregating periods of monthly fact data type is made of DWH_CMN_PERIOD and DWH_CMN_PERIOD_LN. 

The monthly time slices was identified via the following SQL query and slice periods was adjusted in clarity to match the required time period. 

select * from PRJ_BLB_SLICEREQUESTS SR
where SR.IS_DWH_REQUEST = 1
and SR.PERIOD = 3
order by SR.id

 

Additional Information

For the how-to on configuring Data Warehouse slices: Configuring the Data Warehouse Time Slices