The community will be in read-only from Tuesday 11:59pm (PST) to Wednesday 7:30am (PST)
The community will be in read-only from Tuesday 11:59pm (PST) to Wednesday 7:30am (PST)
Project and Portfolio Management Practitioners Forum
cancel
Showing results for 
Search instead for 
Did you mean: 

Retrieve Timesheet information from the database in PPM

Highlighted
bdpdchew
Occasional Contributor

Retrieve Timesheet information from the database in PPM

We have a request to retrieve all the records that users input into their timesheet in PPM. Currently, everyone inputs their timesheet at the end of the week. Each manager is only able to view their team members' timesheets respectively. Thereafter, the managers have to send to a single point of contact (SPOC) to consolidate all the timesheet information. 

 

User would like to retrieve the latest submitted timesheet "at one go" that comprises of everyone's timesheet through probably database end? As they would like to save it into a spreadsheet for presentation.

 

I am not sure if PPM has this functionality that enable a "super user" to view all the user's input timesheet? Otherwise, we are exploring the back end database option. If possible, please highlight the query to run.

 

Many thanks.

Desmond

11 REPLIES
Utkarsh_Mishra
Honored Contributor

Re: Retrieve Timesheet information from the database in PPM

You can take reference for out of the box Time Mgmt report....

 

You can look into the SQL used within these reports to implement your requirement.

 

  1. Actual Time Summary - Shows actual time information by a variety of groupings and filters 
  2. Delinquent Time Sheets - Shows information on Delinquent Time Sheets in the Time Management module 
  3. Time Sheet Details - Detailed information for one or more Time Sheets. Includes work item information and time reported against each work item. 
  4. Time Sheet Summary - Shows time sheet information by a variety of groupings and filters 
  5. Work Allocation Details - Shows information on Work Allocations in the Time Management module
Cheers..
Utkarsh Mishra

-- Remember to give Kudos to answers! (click the KUDOS star)
bdpdchew
Occasional Contributor

Re: Retrieve Timesheet information from the database in PPM

Hi,

 

As I wasn't involved in deploying the PPM and I am not familiar with the reports. Can you shed some lights on how to obtain these reports in order to view their SQL please. Or would it be possible you email me the SQL of these reports or show me how to get around with it.

 

Thanks.

Celil
Esteemed Contributor

Re: Retrieve Timesheet information from the database in PPM

Hi Desmond

It's long and complicated queries. I'm earning through these kind of queries :) But I can shed some lights by giving table names. These tables came from a query what is similar you want:

TM_TIME_SHEETS tm,
TM_TIME_SHEET_LINES tml,
TM_USERS_TIME_APPROVERS tmapr,
TM_ACTUALS tma,
TM_ACTUALS_EFFORT tmae,
ITG_COMPONENTS itgday,
KNTA_LOOKUPS_NLS lu1,
KNTA_LOOKUPS_NLS lu2,
KNTA_USERS ku,
RSC_RP_DISTRIBUTION_ENTRIES rpd,
RSC_RESOURCE_POOLS rp1,
RSC_RESOURCE_POOLS rp2,
RSC_RESOURCE_POOLS rp3,
RSC_RESOURCE_POOL_MANAGERS rpm1,
RSC_RESOURCE_POOL_MANAGERS rpm2,
RSC_RESOURCE_POOL_MANAGERS rpm3,
ktmg_periods tp
Celil

IT Governance Professional
& PPM Solution Architect
AlexSavencu
Honored Contributor

Re: Retrieve Timesheet information from the database in PPM

Hello, Desmond,

 

you should also investigate in the Data Model document, which you should have available with your PPM instance. The tables and their relationships are described fairly well.

 

After you get acquainted to the data model, you should take the examples from the aforementioned OOTB reports.

 

cheers

alex


--remember to kudos people who helped solve your problem
bdpdchew
Occasional Contributor

Re: Retrieve Timesheet information from the database in PPM

I do have a copy of the 1000+pages pdf. I am looking for a guide or template where I can filter and drill down into the information that I need to generate the reports to suit my requirements. 

 

I will check on those reports you mentioned. Thanks anyway.

 

 

bdpdchew
Occasional Contributor

Re: Retrieve Timesheet information from the database in PPM

I am curious to know why doesn't PPM application comes with a functionality that allows user or the super user to collectively gather all the timesheet information for various teams? Rather than the need to perform a backend database operation and also we do not have a official queries and need some trial and error. 

Celil
Esteemed Contributor

Re: Retrieve Timesheet information from the database in PPM

Ups, I couldn't understand what you want to say, sorry :)

Celil

IT Governance Professional
& PPM Solution Architect
bdpdchew
Occasional Contributor

Re: Retrieve Timesheet information from the database in PPM

Pardon me. My requirement is pretty simple. My user wishes to collect all the timesheet that belongs to Team A, Team B, C etc.... What happenes now, I think she has to individuall retrieve team A --> all the members in team A's timesheet. Export to csv. Then proceed with Team B and so on.

 

Is there a way to automate or at least to retrieve all the team members' timesheet group by team and display into a excel spreadsheet?

 

Apparently she was unable to do so and asked if can be explored via the database backend.

 

 

AlexSavencu
Honored Contributor

Re: Retrieve Timesheet information from the database in PPM

Hi,

 

ever since Mercury, the ITG/PPM business model heavily relies on involving professional services, so don't be surprised if basic functionalities, such as your requirement, are missing from the core product.

 

I believe you should go ahead and implement a custom report to satisfy your needs.

 

Also: you can investigate the option to implement Operational Reporting, which comes with severalbuilt-in reports related to Time Management, and you can create way easier your custom reports. You did not mention your PPM version, so I am not sure if this applies to you.

 

cheers

alex


--remember to kudos people who helped solve your problem
bdpdchew
Occasional Contributor

Re: Retrieve Timesheet information from the database in PPM

Thanks dude.  We are on version PPM 9.12

AlexSavencu
Honored Contributor

Re: Retrieve Timesheet information from the database in PPM

There you go, Operational Reporting may be worth while investigating.

 

Welcome, dude :)

 

cheers

alex


--remember to kudos people who helped solve your problem
//Add this to "OnDomLoad" event