Hi CA forum members,
Kindly help us with following problem.
Desired status description/ steps to reproduce:
Our goal is to have a report on project/person level within date from – date to period for hours entered into timesheets.
All these constraints are as parameters in report built in Crystal Reports and set-up in Clarity PPM.
The report take data from a view on top of the Clarity PPM database (on Oracle), which extracts timesheet entries from data mart.
SELECT slc.slice "HOURS"
,tsk.PRNAME "TASK_NAME"
,slc.slice_date "DATE"
,res.prid "RESOURCE_ID"
,RES.PRNAME "NAME"
,RES.PRUSERNAME "USERNAME"
,rle.PRNAME "ROLE"
,prj.UNIQUE_NAME "UNIQUE_NAME"
,prj.NAME "PROJECT_NAME"
,prj.id "PROJECT_ID"
FROM PRJ_BLB_SLICES slc
JOIN PRJ_BLB_SLICEREQUESTS slrqst
ON slc.slice_request_id = slrqst.id
JOIN PRTIMEENTRY tme
ON tme.prid = slc.prj_object_id
JOIN PRASSIGNMENT assi
ON tme.prassignmentid = assi.prid
JOIN PRRESOURCE res
ON assi.prresourceid = res.prid
AND res.PRISROLE = 0
LEFT JOIN PRRESOURCE rle
ON res.prprimaryroleid = rle.prid
AND rle.PRISROLE = 1
JOIN PRTASK tsk
ON assi.prtaskid = tsk.prid
JOIN ODF_CA_TASK oct
ON tsk.prid = oct.id
JOIN SRM_PROJECTS prj
ON tsk.PRPROJECTID = prj.id
JOIN odf_ca_project ocp
ON prj.id = ocp.id
WHERE slrqst.field = 5 /* 5 = PRTimeEntry.prActCurve */
AND slc.slice >= 0;
Problem description:
It worked fine, but then we presumably hit the ‘rollover’ issue – can’t see the timesheet entries in the PRJ_BLB_SLICES table anymore (for selected project/person), while I can still see the timesheet entries in the application.
Next steps and open questions?
Do we need to use exact Slice (defined via UI) or just utilize timeentry entity?
Which application setting does have the control over how long the data are kept in data mart ?
Alternatively, is it better to obtain these data outside of the datamart – and if so, where to look for it ?
thank you very much