AnsweredAssumed Answered

Resource timesheet portlet

Question asked by mcanovas on Dec 15, 2011
Latest reply on Dec 19, 2011 by mcanovas
Hi all,

I am trying to create a portlet that shows the information of posted actuals of a user in a given period. This is an example of what I am trying to do:

[size=4]Filter[size]
Resource: Doe,Jason
Period start: 1/10/2011 (dd/mm/yyyy)
Period finish: 31/10/2011 (dd/mm/yyyy)

[size=4]Layout[size]

Day ---- Project/application ------------------- Task/Incident ------- Hours
1/10/2011 Project A----------------------------------Task A1--------------------4
2/10/2011 Project A ---------------------------------Task A1--------------------5
3/10/2011 Project B--------------------------------- Task B1-------------------- 3
4/10/2011 Application A --------------------------- Incident A1---------------- 5
5/10/2011 Application B --------------------------- Incident A1-----------------7
.....


I've checked that the timesheet data about project-task are in PRJ_BLB_SLICES table. But does someone know where I can find timesheet data about Application-Incident?
I've noticed that the table PRJ_BLB_SLICES_D_INCI_ACT should contain this information but the table does not have data.

This is my first version of the query (only project-task information):
SELECT @SELECT:DIM:USER_DEF:IMPLIED:POSTED:t.unique_id:unique_id@
,@SELECT:DIM_PROP:USER_DEF:IMPLIED:POSTED:t.resour_id:resour_id@
,@SELECT:DIM_PROP:USER_DEF:IMPLIED:POSTED:t.resUNIQUE_NAME:resUNIQUE_NAME@
,@SELECT:DIM_PROP:USER_DEF:IMPLIED:POSTED:t.resource_name:resource_name@
,@SELECT:DIM_PROP:USER_DEF:IMPLIED:POSTED:t.PrimaryRole_id:PrimaryRole_id@
,@SELECT:DIM_PROP:USER_DEF:IMPLIED:POSTED:t.PrimaryRole:PrimaryRole@
,@SELECT:DIM_PROP:USER_DEF:IMPLIED:POSTED:t.id_project:id_project@
,@SELECT:DIM_PROP:USER_DEF:IMPLIED:POSTED:t.prjUNIQUE_NAME:prjUNIQUE_NAME@
,@SELECT:DIM_PROP:USER_DEF:IMPLIED:POSTED:t.project:project@
,@SELECT:DIM_PROP:USER_DEF:IMPLIED:POSTED:t.id_task:id_task@
,@SELECT:DIM_PROP:USER_DEF:IMPLIED:POSTED:t.task_name:task_name@
,@SELECT:DIM_PROP:USER_DEF:IMPLIED:POSTED:t.date_of_actual:date_of_actual@
,@SELECT:DIM_PROP:USER_DEF:IMPLIED:POSTED:t.actual_hours_task:actual_hours_task@
FROM
(
SELECT 
convert(varchar,res.ID)+convert(varchar,prj.id)+convert(varchar,tsk.prid)+convert(varchar,slic.slice_date)+convert(varchar,slic.slice) unique_id
,res.ID resour_id
,res.UNIQUE_NAME resUNIQUE_NAME
,res.FULL_NAME resource_name
,prec.prPrimaryRoleID PrimaryRole_id
,res2.FULL_NAME PrimaryRole
,prj.id id_project
,prj.UNIQUE_NAME prjUNIQUE_NAME
,prj.NAME project
,tsk.prid id_task
,tsk.PRNAME task_name
,slic.slice_date date_of_actual
,slic.slice actual_hours_task
FROM PRAssignment assi
    left join PRTASK tsk
        join SRM_PROJECTS prj
        on tsk.PRPROJECTID = prj.id
    on assi.prtaskid = tsk.prid
    left join srm_resources res
        left join PRJ_RESOURCES prec
                left join prj_resources prec2
                    left join srm_resources res2
                    on prec2.prid = res2.id
                on prec.prPrimaryRoleID = prec2.prid
        on prec.prid = res.id and prec.PRISROLE = 0
    on assi.PRRESOURCEID = res.id
    left join PRJ_BLB_SLICES slic
    on assi.prid = slic.PRJ_OBJECT_ID and slic.slice_request_id = 2
WHERE prj.IS_TEMPLATE = 0
AND res.UNIQUE_NAME is not null
)t
WHERE
@FILTER@
Thanks in advance.

Regards

Outcomes