Timesheet Data

[Linda Grant]

Good morning everyone!

I'm looking for a way to export timesheet data out of Clarity.  I know I can use JasperStudio, but i'm struggling a bit.

I've looked in advanced reporting and also reports and cannot seem to get the data i need .  I need the last two years of data for auditing purposes. 

Does anyone have any ideas/suggestions, etc?  I'll add I am not versed in SQL.  Any help is appreciated.

Thanks,
Linda

[Ming Cheung]

Hi Linda,

It will depend on the data you need.  If you are looking for timesheet summary data (time period, resource, number of hours submitted, approval status, etc) you can try the list view offered by the modern UX.  The list view is based on the common grid component which means you can pick the data you want to export, apply the filters you need and is capable of exporting up to 250,000 rows of data.  With 15.8.0, the timesheet grid will also display virtual timesheets (timesheets belonging to users who have not opened a timesheet but were available during a specified time period and are open for time entry).  Unfortunately my demo environments do not contain two years of data, so I am unable to test how far back the timesheet grid can go.

If you need to see detailed daily time data, you will need to use an NSQL based portlet.  NSQL uses the older grid portlets to display any query you write in an exportable grid, however, the older grid portlets are limited to 25,000 rows.  Daily time data exports can easily exceed that row count.  You would need to compile the data using multiple runs of the queries to extract the 2 years worth of data.

[Linda Grant]

Yiu Ming,

Thank you for the response.  I am looking for summary data of (time, resource, project, number of hours submitted) I am currently on 15.5.1.  Am I still able to use the list view for this type of data? 

As mentioned in my previous post, I'm not versed in NSQL, so i'm not sure what kind of query, what table and where to write the query.  Can you explain what you mean by NSQL based portlet?

Thanks,
Linda

[Ming Cheung]

Hi Linda,

The by-project would be the kicker.  Most of our summary reports are provided by resource/time period.  The Project information is brought into the detail reports, which also breaks the data out into daily numbers.  Here is what I would suggest:

1. In your Clarity instance, go to a project you have access to with timesheets available.
2. On the Team tab, select Actions --> Views --> timesheet review
3. This is an out-of-the box portlet that displays, on a per project basis, the timesheets that have been submitted for a given project
4. If this portlet provides the type of data you are looking for, you can duplicate this portlet and make a few modifications to meet your needs.

The portlet can be copied either using the XML Open Gateway (XOG), or by copy/pasting the details.  If you use XOG remember to replace the unique code in both the query and the portlet.  To use Copy/Paste, do the following:

1. Open the list of queries (Administration --> Studio --> Queries)
2. Open the Timesheet Review Query (Code: cop.invTimeReview)
3. In a new window, create a new query and copy the details over tab by tab (providing a different name and different query ID
4. When you get to the NSQL tab, you will need to make some query modifications
   1. The first one you need to remove is part of the Join statement between the INV_INVESTMENTS table and the PRTASK table
      1. You want remove the statement that says "AND inv.id = @NVL@(@WHERE:PARAM:XML:INTEGER:/data/id/@value@, inv.id)"
5. once you remove this statement, the query should return all timesheets
6. Continue copying the settings in the other tabs

Once the query is created, you will need to create the corresponding GRID portlet and associate the grid portlet to a portlet page to display the results.  Due to the number of results this query may return, I recommend setting the portlet to only return filtered data.  There are a number of other things you may need to tweak, but the core SQL that you need is contained in the above query.

Many of our partners have done this in the past, so if you have access to a partner, I would recommend leveraging them if you can.  If you want to experiment, I recommend trying it in a DEV environment.

Hope this helps.
​

[Martin Snizek]

Hello Linda,

you can also use this query

select
tp.PRSTART timeperiod_start,
tp.PRFINISH timeperiod_finish,
inv.NAME investment_name,
inv.CODE investment_id,
res.FULL_NAME resource_full_name,
sum(te.PRACTSUM)/3600 actual_hours
from PRASSIGNMENT pra
left join PRTASK tsk on tsk.PRID = pra.PRTASKID
left join INV_INVESTMENTS inv on inv.ID = tsk.PRPROJECTID
left join SRM_RESOURCES res on res.ID = pra.PRRESOURCEID
left join PRTIMEENTRY te on te.PRASSIGNMENTID = pra.PRID
left join PRTIMESHEET tsh on tsh.PRID = te.PRTIMESHEETID
left join PRTIMEPERIOD tp on tp.PRID = tsh.PRTIMEPERIODID
where te.PRACTSUM is not null
and tsh.PRSTATUS = 4 -- filtering for posted timesheets only
group by tp.PRSTART, tp.PRFINISH, inv.NAME, inv.CODE, res.FULL_NAME​

Regards,
Martin