Clarity

Hi, I'm working with Clarity 13.3 and I was wondering if you can help me out. I need to get the query behind this portlet:

I even need something quite more simple than this whole portlet. I need to be able to get the investment name, the allocation and the ETC for an specific set of roles I will add to the query as parameter. I tried using the SQL trace to see If I can get what is running behind this, but that was not good enough.

I tried writing my own simple sql to start but I was able to get data only for the ETC and Allocation in case the role or resource in question is assigned on the effort task and the data I got is only the one for that particular task and not on the other potencial proyect tasks. This is the query I was working with:

select distinct inv.code, inv.name,(team.prallocsum/3600)ALLO, (asig.prestsum/3600)ETC

from niku.inv_investments inv, niku.srm_resources res, niku.prteam team, niku.odf_ca_project ca, niku.prassignment asig

where

INV.ID = TEAM.PRPROJECTID

and TEAM.PRRESOURCEID = RES.ID

and TEAM.PRRESOURCEID = asig.PRRESOURCEID

and inv.id = ca.id

and asig.PRID = team.prid

and ca.obj_request_category ='category102'

and asig.PRRESOURCEID = '5001***'

I know the issue with that query is the part where I state "and asig.PRID = team.prid" but without that I get duplicated data and weird things.

Any idea or guidance will be highly appreciated.

Regards,

Juan

Check if this helps -

SELECT @SELECT:DIM:USER_DEF:IMPLIED:RESOURCE:rownum:row_num@,

@SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:csu.user_name:Resource_ID@,

@SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:SRM.FULL_NAME:Resource_name@,

@SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:(select srm1.full_name from srm_resources srm1 where srm1.user_id=SRM.MANAGER_ID):Resource_Manager_Name@,

@SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:prt.prid:task_internal_id@,

@SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:prt.prname:TaskName@,

@SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:INV.id:project_internal_id@,

@SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:INV.CODE:project_code@,

@SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:INV.NAME:project_name@,

@SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:(select srmr.full_name from srm_resources srmr where SRMR.USER_ID=INV.MANAGER_ID ):project_manager@,

@SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:PRTP.PRSTART:Week@,

@SELECT:METRIC:USER_DEF:IMPLIED:sum(prtimeen.practsum/3600):Total_hours@

FROM prassignment prass,

prtask prt,

srm_resources srm,

cmn_sec_users csu,

inv_investments inv,

prtimeentry prtimeen,

prtimesheet prts,

prtimeperiod prtp

WHERE

prass.prtaskid = prt.prid

AND csu.id = srm.user_id

AND srm.ID = prass.prresourceid

AND prt.prprojectid = inv.ID

and PRTIMEEN.PRASSIGNMENTID = PRASS.PRID

and PRTS.PRID = PRTIMEEN.PRTIMESHEETID

and PRTS.PRTIMEPERIODID = PRTP.PRID

and PRTS.PRSTATUS = 4

and (PRTP.PRSTART >= (@WHERE:PARAM:USER_DEF:DATE:PERIODFROM@) or(@WHERE:PARAM:USER_DEF:DATE:PERIODFROM@ is null))

AND (   EXISTS (

SELECT 1

FROM prj_obs_associations poa, obs_units_flat_by_mode ofm

WHERE poa.unit_id = ofm.linked_unit_id

AND (   ofm.unit_id = (@WHERE:PARAM:USER_DEF:INTEGER:param_z_pro@)

OR (@WHERE:PARAM:USER_DEF:INTEGER:param_z_pro@) IS NULL

)

AND poa.record_id = inv.ID

AND poa.table_name = 'SRM_PROJECTS'

AND (ofm.unit_mode = (@WHERE:PARAM:USER_DEF:STRING:param_z_proassociation@) or (@WHERE:PARAM:USER_DEF:STRING:param_z_proassociation@) is null)))

AND (   EXISTS (

SELECT 1

FROM prj_obs_associations poa, obs_units_flat_by_mode ofm

WHERE poa.unit_id = ofm.linked_unit_id

AND (   ofm.unit_id = (@WHERE:PARAM:USER_DEF:INTEGER:param_z_rso@)

OR (@WHERE:PARAM:USER_DEF:INTEGER:param_z_rso@) IS NULL

)

AND poa.record_id = srm.ID

AND poa.table_name = 'SRM_RESOURCES'

AND (ofm.unit_mode = (@WHERE:PARAM:USER_DEF:STRING:param_z_rsoassociation@) or (@WHERE:PARAM:USER_DEF:STRING:param_z_rsoassociation@) is null)))

AND @FILTER@

GROUP BY

rownum,

inv.id,

inv.code,

INV.NAME,

prt.prid,

PRT.PRNAME,

csu.user_name,

SRM.FULL_NAME,

INV.MANAGER_ID ,

PRTP.PRSTART,

srm.manager_id

HAVING @HAVING_FILTER@

NJ

NJ, I don't think this is what I was talking about. If I'm not understanding the query wrong it brings task info, weekly periods and only actuals, nothing referred to ETC or Allocation.

The portlet I'm referring to shows totals for an investment on ETC and Allocations for a particular resource.

Please let me know if I'm not understanding the query you provided.

Thanks,

Juan

Yep, sorry, my bad , you mentioned about tasks, and so thought of sharing.

Have you check these ?

Query Team Allocations

Re: Help with SQL Query

Resource weekly ETC query

NJ

Task Assignment ETC

NJ

If you got to configure on that portlet and then to General tab you can see that the data provider is Team Allocations.

You can create a custom portlet based on that system data provider and it will bring the columns defined for that from which you can choose what you want. If the columns/data you want is not there you are out of luck. You have to create a query based portlet. You could start with the query this portlet is using, but to get that you would have to put Action trace on.

On the other hand you can see the available columns in addition to the currently selected columns in the list columns layout tab

Thanks urmas, I got my hands into the data provider but since this is not an object I cannot register new attributes so I'm out of luck with this and I think still need the query behind this.

Thanks,

Juan

Did run a trace in v12.1 and this looks like the query

SELECT  TM.PRID

                  , TM.PRID team_id

                  , TM.PRRESOURCEID

                  , TM.PRPROJECTID

                  , TM.PRROLEID

                  , INV.SCHEDULE_START, INV.SCHEDULE_FINISH

                  , ISNULL(TM.PRAVAILSTART,INV.SCHEDULE_START) PRAVAILSTART

                  , ISNULL(TM.PRAVAILFINISH,INV.SCHEDULE_FINISH) PRAVAILFINISH

                  , TM.PRALLOCCURVE

                  , RS.DATE_OF_HIRE

                  , RS.DATE_OF_TERMINATION

                  , RS.RESOURCE_TYPE

                  , niku.PRJ_MAX_ACTTHRU_FCT(TM.PRPROJECTID, TM.PRRESOURCEID) PRACTTHRU

                  , RP.PRISROLE

                  , (CASE

                    WHEN RS.RESOURCE_TYPE < 2 THEN TM.PRALLOCSUM / 3600

                    ELSE TM.PRALLOCSUM END) PRALLOCSUM

                  , (CASE

                    WHEN RS.RESOURCE_TYPE < 2 THEN TM.HARD_SUM / 3600

                    ELSE TM.HARD_SUM END) HARD_SUM

                  , TM.HARD_START

                  , TM.HARD_FINISH

                  , TM.PRBOOKING

          FROM    PRJ_RESOURCES RP

                  , SRM_RESOURCES RS

                  , INV_INVESTMENTS INV

                  , PRTEAM TM

          WHERE   RS.ID = TM.PRRESOURCEID

          AND     INV.ID = TM.PRPROJECTID

          AND     RP.PRID = RS.ID  AND TM.PRID IN (5000167,5000141,5000018,5000058)

urmas, thanks again for your help. Do you have any idea why the ETC seems not to be there on that SQL Trace? I got similar results on my SQL Trace, not all columns of the portlet are showing there. In my case I just need the ETC one to be there. I cleaned the query a little bit to add the extra fields and attributes I was looking for so now it looks like this:

TM.PRID, INV.NAME, INV.CODE, INV.SCHEDULE_START, INV.SCHEDULE_FINISH,

(CASE WHEN RS.RESOURCE_TYPE < 2 THEN TM.PRALLOCSUM / 3600 ELSE TM.PRALLOCSUM END) PRALLOCSUM,

(CASE WHEN RS.RESOURCE_TYPE < 2 THEN TM.HARD_SUM / 3600 ELSE TM.HARD_SUM END) HARD_SUM

FROM niku.SRM_RESOURCES RS, niku.INV_INVESTMENTS INV, niku.PRTEAM TM, niku.ODF_CA_PROJECT CA

WHERE RS.ID = TM.PRRESOURCEID

AND INV.ID = TM.PRPROJECTID

AND INV.ID = CA.ID

AND TM.PRRESOURCEID = '5001005'

AND CA.OBJ_REQUEST_CATEGORY ='category102'

I just need to add the ETC there and that will do it. Any idea how can I get it there?

Thanks.

Juan

That is easy (  ;-)  )

ETC is prassingment.PRESTSUM

Then you just join prassingment.prresourceID to SRM_RESOURCES.id and prassignmenet.prtaskid and further prtask.prprojectid to inv_investments.id

(See the tech ref for more)

Before starting any fancy SQL I should check if you can configure Resource workload portlet to your needs of use Resources with Aggregation data provider.

One of the advantages of these two system providers is that you can display may of the attributes timescaled.

urmas, thank you very much for the last tips on the joins. My small query is up and running.

It looks like this just in case any other community member needs this:

TM.PRID, INV.NAME, INV.CODE, INV.SCHEDULE_START, INV.SCHEDULE_FINISH, (ASIG.PRESTSUM/3600)ETC,

(CASE WHEN RS.RESOURCE_TYPE < 2 THEN TM.PRALLOCSUM / 3600 ELSE TM.PRALLOCSUM END) ALLOC

FROM

niku.SRM_RESOURCES RS,

niku.INV_INVESTMENTS INV,

niku.PRTEAM TM,

niku.ODF_CA_PROJECT CA,

niku.PRTASK TS,

niku.PRASSIGNMENT ASIG

WHERE RS.ID = TM.PRRESOURCEID

AND INV.ID = TM.PRPROJECTID

AND INV.ID = CA.ID

AND ASIG.PRRESOURCEID = RS.ID

AND TS.PRID = ASIG.PRTASKID

AND TS.PRPROJECTID = INV.ID

AND TM.PRRESOURCEID = '5001005'

AND CA.OBJ_REQUEST_CATEGORY ='category102'

Thank you again.

Juan

Did you remember to  verify that your query is returning accuarte results?