Project and Portfolio Management Practitioners Forum
cancel
Showing results for 
Search instead for 
Did you mean: 

PPM Actual Daily Time Report

Highlighted
Jason Duryea
Occasional Advisor

PPM Actual Daily Time Report

Does anyone know the best method for getting the actual time sheet information from PPM using Crystal Reports? I need to collect user name, daily time sheet entries including hours, and time sheet line description. It appears to require several tables linked in CR to get this to work, but the report is taking a long period of time to run.
1 REPLY
Celil
Esteemed Contributor

Re: PPM Actual Daily Time Report

Hi Jason,

My advice is, you could use views from PPM Oracle database.

you can use this query in view:

select
tm.TIME_SHEET_ID,
tm.DESCRIPTION,
tm.EXCEPTION_MESSAGE,
tm.RESOURCE_ID,
tm.PERIOD_ID,
tm.CALENDAR_ID,
tm.STATUS_CODE,
lu1.MEANING TM_STATUS,
tm.TIME_SHEET_REQUIRED_FLAG,
tml.TIME_SHEET_LINE_ID,
tml.WORK_ITEM_ID,
tml.WORK_ITEM_SET_ID,
tml.WORK_ITEM_TYPE,
tml.STATE,
lu2.MEANING TML_STATUS,
tmae.ACTUAL_EFFORT,
tmae.EFFORT_ORDER,
itgday.START_TIME
from
TM_TIME_SHEETS tm,
TM_TIME_SHEET_LINES tml,
TM_ACTUALS tma,
TM_ACTUALS_EFFORT tmae,
ITG_COMPONENTS itgday,
KNTA_LOOKUPS_NLS lu1,
KNTA_LOOKUPS_NLS lu2
where
tm.TIME_SHEET_ID = tml.TIME_SHEET_ID
AND tml.TIME_SHEET_LINE_ID = tma.TIME_SHEET_LINE_ID
AND tma.ACTUALS_ID = tmae.ACTUALS_ID
AND tm.CALENDAR_ID = itgday.CALENDAR_ID
AND tmae.EFFORT_ORDER = itgday.COMPONENT_SEQ
AND (lu1.LOOKUP_TYPE = 'TM - Time Sheet Statuses' AND lu1.LOOKUP_CODE = tm.STATUS_CODE)
AND (lu2.LOOKUP_TYPE = 'TM - Time Sheet Line Statuses' AND lu2.LOOKUP_CODE = tml.STATE)
AND tma.TOTALS_FLAG = 'N'
order by tm.RESOURCE_ID, itgday.start_time


Does it helpful for u?

Celil
Celil

IT Governance Professional
& PPM Solution Architect
//Add this to "OnDomLoad" event