AnsweredAssumed Answered

DWH Jasper reports not showing results for 2018

Question asked by Sreeram601 on Jan 30, 2018
Latest reply on Apr 12, 2018 by Sreeram601

Hi

I've designed a jaspersoft report using DWH db. I run "Load DWH" every minute to sync the Clarity db with DWH db. All the new projects, new tasks which was created in 2018 are updated in DWH db, except the timeperiods. Time periods of 2018, didn't get updated in DWH tables(DWH_CMN_Period). I've opened the Time period for January month and created & activated the fiscal period for the whole year 2018. I ran the time slicing(newjob), update BO reports job, Load DWH access rights and Load DWH(Full and non-full Load) jobs. But still the DWH_CMN_period is not updated.

Please advise how do I proceed to get entries in DWH

 

please find the query(if in case)

 

SELECT
RES.RESOURCE_NAME
,RES.IS_ACTIVE ACTIVE
,INV.INVESTMENT_KEY INVESTMENT_KEY
, INV.INVESTMENT_NAME
, TASK.TASK_NAME TASK_NAME
,TASK.TASK_KEY TASK_ID
, TASK.WBS_LEVEL WBS_LEVEL
, TASK.WBS_SEQUENCE WBS_SEQUENCE
, ASSIGNT.ASSIGNMENT_KEY
, PERIOD.PERIOD_TYPE_KEY
, PERIOD.MONTH_START_DATE
, TO_CHAR(PERIOD.MONTH_START_DATE, 'YYYY/MM') TIME_PERIOD
, SUM(TEF.TIME_HOURS) / 8 pending_Actuals
FROM
DWH_TME_ENTRY TE
INNER JOIN
DWH_TME_ENTRY_FACTS TEF
ON
(
TEF.TIMEENTRY_KEY = TE.TIMEENTRY_KEY
)
INNER JOIN
DWH_CMN_PERIOD PERIOD
ON
(
PERIOD.PERIOD_KEY = TEF.PERIOD_KEY
)
--AND PERIOD.PERIOD_TYPE_KEY = 'DAILY'
--AND PERIOD.PERIOD_TYPE_KEY = 'MONTHLY'
INNER JOIN
DWH_TME_SHEET TS
ON
(
TE.TIMESHEET_KEY = TS.TIMESHEET_KEY
)
AND TS.TIMESHEET_STATUS_KEY <> 4 AND TS.TIMESHEET_STATUS_KEY <> 5
INNER JOIN
DWH_TME_PERIOD TP
ON
(
TP.TIME_PERIOD_KEY = TS.TIME_PERIOD_KEY
)
--AND IS_ACTIVE = 1
INNER JOIN
DWH_INV_ASSIGNMENT ASSIGNT
ON
(
ASSIGNT.ASSIGNMENT_KEY = TE.ASSIGNMENT_KEY
)
INNER JOIN
DWH_INV_TASK TASK
ON
(
TASK.TASK_KEY = ASSIGNT.TASK_KEY
)
INNER JOIN
DWH_RES_RESOURCE RES
ON
(
RES.RESOURCE_KEY = ASSIGNT.RESOURCE_KEY
)
INNER JOIN
DWH_INV_INVESTMENT INV
ON
(
INV.INVESTMENT_KEY = ASSIGNT.INVESTMENT_KEY
)

AND PERIOD.MONTH_START_DATE = '01-JAN-2018'
GROUP BY
PERIOD.PERIOD_TYPE_KEY
,INV.INVESTMENT_KEY
, ASSIGNT.ASSIGNMENT_KEY
, PERIOD.MONTH_START_DATE
, RES.RESOURCE_NAME
, RES.IS_ACTIVE
, INV.INVESTMENT_NAME
, TASK.TASK_NAME
,TASK.TASK_KEY
, TASK.WBS_LEVEL
, TASK.WBS_SEQUENCE

Outcomes