How do I read data from the Daily Time Slices?

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

I am trying to create a portlet that pulls slice data for the standard Daily Time Slices.  How can I join the slice data to the right table to get good information?

These are the Time Slices I am interested in using:

 

Slice Id	Slice Request Name
1	DAILYRESOURCEAVAILCURVE
2	DAILYRESOURCEACTCURVE
3	DAILYRESOURCEESTCURVE
10	DAILYRESOURCEALLOCCURVE
11	DAILYRESOURCEBASECURVE

  

Resolution

The individual queries below will show you how the slices table is joined to project and resource profiles.  

Be sure to replace the where clause criteria values using mixed lower and upper case characters.

Slice #1: Daily Resource Availability Hours

SELECT Slice, slice_date, r.full_name
FROM prj_blb_slices s, srm_resources r
WHERE s.prj_object_id = r.id  and

r.unique_name like 'Replace with Resource Code'

and slice_request_id = 1

  

  

Slice #2: Daily Resource Actual Hours

SELECT Slice, slice_date, r.full_name, t.prname Task_name, p.name project

FROM prj_blb_slices s, prassignment a, prtask t, srm_resources r, srm_projects p
WHERE s.prj_object_id = a.prid and a.prtaskid = t.prid and
t.prprojectid = p.id and a.prresourceid = r.id and
p.unique_name like 'Replace with Project Code' and
r.unique_name like 'Replace with Resource Code' and
slice_request_id = 2

Slice #3: Daily Resource Estimate Hours
SELECT Slice, slice_date, r.full_name, t.prname Task_name, p.name project
FROM prj_blb_slices s,  prassignment a, prtask t, srm_resources r, srm_projects p
WHERE s.prj_object_id = a.prid and a.prtaskid = t.prid and
t.prprojectid = p.id and a.prresourceid = r.id and
p.unique_name like 'Replace with Project Code' and
r.unique_name like 'Replace with Resource Code' and
slice_request_id = 3

Slice #10: Daily Resource Allocation Hours
SELECT Slice, slice_date, r.full_name, p.name project 
FROM prj_blb_slices s, prteam t, srm_resources r,  srm_projects p 
WHERE s.prj_object_id = t.prid and
t.prprojectid = p.id and t.prresourceid = r.id and
p.unique_name like 'Replace with Project Code' and
r.unique_name like 'Replace with Resource Code' and
slice_request_id = 10

Slice #11: Daily Resource Baseline Hours
SELECT Slice, slice_date, r.full_name, t.prname Task_name, p.name project 

FROM prj_blb_slices s, prassignment a, prtask t, srm_resources r, srm_projects p 

WHERE s.prj_object_id = a.prid and a.prtaskid = t.prid and 

t.prprojectid = p.id and a.prresourceid = r.id and 

p.unique_name like 'Replace with Project Code' and
r.unique_name like 'Replace with Resource Code' and

slice_request_id = 11