I have a need to report about the demand by role on Ideas and projects. In the case of the Ideas its Team tab is for the most part populated with roles within our organization. While running a SQL query against the PRJ_BLB_Slices for Daily allocations, I noticed that it holds the daily allocations just for named resources and not for roles..
Roles and Resource allocation information stores on same time slice. You can check either slice table or insta Slice table
PRJ_BLB_SLICES_M_ALC OR PRJ_BLB_SLICES_W_ALC
Query as below
SELECT I.CODE, I.NAME, R.FULL_NAME, SLICE_DATE, SLICE FROM PRJ_BLB_SLICES SL JOIN PRTEAM TE ON TE.PRID = SL.PRJ_OBJECT_IDJOIN PRJ_BLB_SLICEREQUESTS SLR ON SL.SLICE_REQUEST_ID = SLR.ID AND SLR.REQUEST_NAME = 'MONTHLYRESOURCEALLOCCURVE'JOIN INV_INVESTMENTS I ON I.ID = TE.PRPROJECTID AND I.CODE = 'PR1037'JOIN SRM_RESOURCES R ON R.ID = TE.PRRESOURCEID AND R.FULL_NAME = 'Developer'
My sincere apologies for the delay.
I was using a similar query (shown here) that inner joins the PRTeam table with the PRJ_BLB_Slices for Daily Resource Allocations (Slice Request ID of 10). The output did not include the daily allocation details for Roles.
Select teamdb.prresourceid, teamdb.prroleid, sum(slicedb.slice), min(slicedb.slice_date), max(slicedb.slice_date)
From prj_blb_slices sliceDB
Inner join prteam teamdb on sliced.prj_object_id - teamdb.prid and
teamdb.prprojectid = <five million number for the prj on your system>
Group by teamdb.prresourceid, teamdb.prroleid
I did not attempt running against the monthly slices but am assuming that the outcome would be similar.
The steps I had followed were
The output did not include the allocation details for the role that was added.
I even changed the query such that prj_object_id was compared against the five million number for the role added to the team and there were no results.
I then had my colleague run the same steps and query on a higher version of CA PPM and his outcomes were similar to mine, (i.e.) no data in the Prj_Blb_Slices table for Daily resource allocations.
This is very puzzling and I was of the opinion that all allocation details irrespective of roles or named resources are stored under the same slice request id.
Getting to this data is crucial because one can create allocation segments for the roles being added to the team and attempts to extract the daily demand for the roles has hit this road block.
This is why my earlier question about the slice request ID pertinent to allocation data for Roles.