Clarity

Hi,
 
Anybody here who knows how to get the %allocation column in the Team -> Staff page in a Project's property in the database?I looked at the team object and says its in prallocdefault field but there's no such field like that in the PRTeam table.
 
Appreciate your help.
 
Thanks
inglife

Hi, i also need that project staff member properties -- Default % Allocation field for an report.i tried in prcalendar but no luck.If any body knows it will be helpful for me. sundar

Was there a resolution for this? I am needing as well to put allocaton % on a report.    

The % Allocation you see in the Team is derived from PRTEAM.PRALLOCCURVE which is not a simple numeric colum.  This is to allow for a team member's allocation can change over time (e.g. might be 100% for 6 weeks, 50% for next 2 weeks, 25% for 4 another weeks).  You can locate the information you need for reporting via SQL from time slice tables (using the entries based on allocation). As you can establish time slices in different time units choose the one most appropiate to your query (e.g. MONTHLYRESOURCEALLOCCURVE).    [PS: As a general rule if you find "CURVE" in a Clarity table field name, it is a special data type used to handle data that varies over a timescale, also often known as 'blobs' and the time slicing code known as 'blobcrack'.]                        

Paul,     Thanks very much.I got the Query for calculating Allocation Hours from Time Slices.But how can we calculate the % Allocation from this?     SELECT PRPROJECTID,PRRESOURCEID,SUM(DATA.SLICE) HRS
FROM  (
SELECT PRID,PRRESOURCEID,PRPROJECTID FROM PRTEAM  )S,  (  SELECT  * FROM  PRJ_BLB_SLICEREQUESTS R,   PRJ_BLB_SLICES S
WHERE  R.ID = S.SLICE_REQUEST_ID
AND R.REQUEST_NAME = 'MONTHLYRESOURCEALLOCCURVE'  )DATA   WHERE S.PRID=DATA.PRJ_OBJECT_ID
AND S.PRID = 5169948   --(PRTEAM PRID)  AND SLICE_DATE BETWEEN '01-AUG-2009' AND '31-AUG-2009'  GROUP BY PRPROJECTID,PRRESOURCEID     regards,  sundar

How about comparing allocation to dailyavailability?  Martti K.

? If using monthly allocation data then monthly availability data would be simpler.  MONTHLYRESOURCEAVAILCURVE  

Hi,               We can fetch the records for % allocation column from Team --> Staff Page in Project. Please Look at the attached fileFor Clarity 7.5.X
=================
 SELECT PRJ.UNIQUE_NAME "Project ID", PRJ.NAME "Project Name", SRM.UNIQUE_NAME "Resource ID",
SRM.FULL_NAME "Resource Name", ALLOC_START_DATE "Allocation Start Date", ALLOC_END_DATE "Allocation End Date",
SLICE "Allocated Hrs", RES_AVAIL "Available Hrs", CASE WHEN RES_AVAIL 0 THEN (SLICE/RES_AVAIL) * 100 END "% Allocation"
FROM PRTEAM TEAM, SRM_PROJECTS PRJ, SRM_RESOURCES SRM,(SELECT PRJ_OBJECT_ID, MIN(SLICE_DATE) ALLOC_START_DATE,
MAX(SLICE_DATE) ALLOC_END_DATE, MAX(SLICE) SLICE FROM PRJ_BLB_SLICES_D_ALC GROUP BY PRJ_OBJECT_ID, SLICE HAVING SLICE 0)
 ALLOCATION,(SELECT PRJ_OBJECT_ID, MAX(SLICE) RES_AVAIL FROM PRJ_BLB_SLICES_D_AVL GROUP BY PRJ_OBJECT_ID) AVAIL
WHERE TEAM.PRPROJECTID = PRJ.ID AND TEAM.PRRESOURCEID = SRM.ID AND TEAM.PRID = ALLOCATION.PRJ_OBJECT_ID
AND SRM.ID = AVAIL.PRJ_OBJECT_ID
--AND SRM.UNIQUE_NAME = '1246713'
ORDER BY PRJ.ID, SRM.ID, ALLOC_START_DATE  For Clarity 8.1.X
=================
 SELECT INV.CODE "Project ID", INV.NAME "Project Name",SRM.UNIQUE_NAME "Resource ID", SRM.FULL_NAME "Resource Name",
START_DATE "Allocation Start Date",END_DATE "Allocation End Date",SLICE "Allocated Hrs", AVAIL "Availability Hrs"
, CASE WHEN AVAIL 0 THEN (SLICE/AVAIL)*100 END "%Allocation" FROM PRTEAM TEAM, INV_INVESTMENTS INV, SRM_RESOURCES SRM,
(SELECT PRJ_OBJECT_ID, INVESTMENT_ID, RESOURCE_ID, MIN(SLICE_DATE) START_DATE,MAX(SLICE_DATE) END_DATE,MAX(SLICE) SLICE FROM PRJ_BLB_SLICES_D_ALC GROUP BY INVESTMENT_ID, PRJ_OBJECT_ID,
SLICE, RESOURCE_ID, TO_CHAR(SLICE_DATE,'Mon-YYYY') HAVING SLICE 0) ALLOC, (SELECT PRJ_OBJECT_ID, MAX(SLICE) AVAIL FROM PRJ_BLB_SLICES_D_AVL GROUP BY PRJ_OBJECT_ID) AVL
WHERE TEAM.PRPROJECTID = INV.ID AND TEAM.PRRESOURCEID = SRM.ID AND TEAM.PRID = ALLOC.PRJ_OBJECT_ID
AND TEAM.PRRESOURCEID = AVL.PRJ_OBJECT_ID ORDER BY INV.ID,START_DATE  ------------------Hard Allocation  SELECT INV.CODE "Project ID", INV.NAME "Project Name",SRM.UNIQUE_NAME "Resource ID", SRM.FULL_NAME "Resource Name",
START_DATE "Allocation Start Date",END_DATE "Allocation End Date",SLICE "Allocated Hrs", AVAIL "Availability Hrs"
, CASE WHEN AVAIL 0 THEN (SLICE/AVAIL)*100 END "%Allocation" FROM PRTEAM TEAM, INV_INVESTMENTS INV, SRM_RESOURCES SRM,
(SELECT PRJ_OBJECT_ID, INVESTMENT_ID, RESOURCE_ID, MIN(SLICE_DATE) START_DATE,MAX(SLICE_DATE) END_DATE,MAX(SLICE) SLICE FROM PRJ_BLB_SLICES_D_HARDALC GROUP BY INVESTMENT_ID, PRJ_OBJECT_ID,
SLICE, RESOURCE_ID, TO_CHAR(SLICE_DATE,'Mon-YYYY') HAVING SLICE 0) ALLOC, (SELECT PRJ_OBJECT_ID, MAX(SLICE) AVAIL FROM PRJ_BLB_SLICES_D_AVL GROUP BY PRJ_OBJECT_ID) AVL
WHERE TEAM.PRPROJECTID = INV.ID AND TEAM.PRRESOURCEID = SRM.ID AND TEAM.PRID = ALLOC.PRJ_OBJECT_ID
AND TEAM.PRRESOURCEID = AVL.PRJ_OBJECT_ID ORDER BY INV.ID,START_DATE  Thanks
Senthil.  

Paul:  Have you actually used the monthly slices?I tried to use them some years ago for reports and they wer just **** and then decided to use the daily slices which gave correct results   consistently.  Martti K.

Hi,  A light is been seen in this Topic.(Thanks to  Paul & senthil)  To use  % Default Allocation Reflecting in Reports & Portlets using the Query send by senthil.
 
 SELECT PRJ.UNIQUE_NAME "Project ID", PRJ.NAME "Project Name", SRM.UNIQUE_NAME "Resource ID",
 SRM.FULL_NAME "Resource Name", ALLOC_START_DATE "Allocation Start Date", ALLOC_END_DATE "Allocation End Date",
 SLICE "Allocated Hrs", RES_AVAIL "Available Hrs",
 CASE WHEN RES_AVAIL 0 THEN (SLICE/RES_AVAIL) * 100 END "% Allocation"
 FROM PRTEAM TEAM, SRM_PROJECTS PRJ, SRM_RESOURCES SRM,
 (
 SELECT PRJ_OBJECT_ID, MIN(SLICE_DATE) ALLOC_START_DATE,
 MAX(SLICE_DATE) ALLOC_END_DATE, MAX(SLICE) SLICE
 FROM PRJ_BLB_SLICES_D_ALC
 GROUP BY PRJ_OBJECT_ID, SLICE
 HAVING SLICE 0
 )ALLOCATION,
 (
 SELECT PRJ_OBJECT_ID, MAX(SLICE) RES_AVAIL
 FROM PRJ_BLB_SLICES_D_AVL
 GROUP BY PRJ_OBJECT_ID
 ) AVAIL
 WHERE
 TEAM.PRPROJECTID = PRJ.ID
 AND TEAM.PRRESOURCEID = SRM.ID
 AND TEAM.PRID = ALLOCATION.PRJ_OBJECT_ID
 AND SRM.ID = AVAIL.PRJ_OBJECT_ID
 AND SRM.UNIQUE_NAME = '1172507'
 ORDER BY PRJ.ID, SRM.ID, ALLOC_START_DATEi changed the value of % default Allocation(From 100% to 90%),this query is fetching Dynamically showing the Latest Allocation %.
So as for reports will show the Modified % dynamically.(Our Job runs for nearly 6 hours and scheduled daily eod).But there is an   dependency of the slice Job if only when the Availability is Changed.If availability is changed (from 8 to 7 hours) then in the Default Allocation % is automatically Changed based on the availability in the Query
but it is not reflecting in the application(Default Allocation Field).
May be after running the Job it should Reflect the Modified one(?).Question:-How the Daily Allocation slice is stored,on what logic.1)   If a Resource Availability is 8 hours then date of hire is 01-aug-2009 then he is
allocated to 1 project --- Project AAug 3 - Aug 14   --   10 mandaysAvailability is 8 hoursthen if the Allocation is 100 %   then the Daily Allocation will also be the same no as Availability(8 hours).I should see 8,8,8,8,8,0,0,8,8,8,8,8   in PRJ_BLB_SLICES_D_ALC.When the % Allocation is 50%I should see 4,4,4,4,4,0,0,4,4,4,4,4   in PRJ_BLB_SLICES_D_ALC.Is my understanding is correct?  2) If a Resource Availability is 8 hours then date of hire is 01-aug-2009 then he is
allocated to 2 projects at an same time say Project A and Project B fromAug 3 - AUg 14   --   10 mandaysAvailability is 8 hoursHe is over allocated in 2 projects then how it is stored in the Daily Alloc curve?When the % Allocation is 100% for both the proejctsI should see 8,8,8,8,8,0,0,8,8,8,8,8   in PRJ_BLB_SLICES_D_ALC for Project A(prteam prid) and
8,8,8,8,8,0,0,8,8,8,8,8   in PRJ_BLB_SLICES_D_ALC for Project B.Is my understanding is correct?.Can any one jump and answer in detail so that it will be clear for me who has always unclear when it is related to Slices.(Hate Niku concept of slices
and CA still carrying that............).Why they used slices Job(Blob Crack)?.Is it because instead of storing all the Data in the Master tables,it will easy to fetch from Temp Tables (Say slices)
so that data retrival will be faster?    Regards,
Sundar

Rather than using the system slices I prefer to use the user configurable slices, because they are user configurable (eventhough Dave revealed a nearly "legal" way to configure them, they are better docuemented, I ha ve experienced less problems with them and have got even support with prolems related to them while problems related to the system slices are unresolved. For similar reasons I use the daily slices not monthly.But everone can make their own choice on this.  If I assign myself to two projects, hard allocate and book I can see myself allocated in both projects my daily available hours according to my calendar as epected    Select prj_blb_slices.slice_DATE, prj_blb_slices.slice, srm_resources.full_name Resource, inv_investments.name Project
from prj_blb_slices,prj_blb_slicerequests, prteam, srm_resources,
inv_investments
Where
prteam.prid = prj_blb_slices.prj_object_id
and prj_blb_slicerequests.request_name = 'DAILYRESOURCEALLOCCURVE'
and prj_blb_slicerequests.id = prj_blb_slices.slice_request_id
and prteam.prresourceid = srm_resources.id
and prteam.prprojectid = inv_investments.id
and srm_resources.unique_name = 'kinnunenpp'
order by slice_date, projectThe way I have perceived it is when Hoskyns came out with PMW it was file base and everything was proprietary blobs and other. ABT adde more products to Results Management Suite and made it database base for multiple users, shared data and security. The blobs were in binary fields. The option would have been to have a table or tables to accomodate the timescaled data for the thousand projects, thousand resources allocated and assigned to the thousand tasks for every day that is for this year, next year, the year after and also for last year and the year before and so on. That is for ETC, actuals, baselines, allocations and so on.For reporting on timescaled data there was   Reports Download which could convert timeentries   and something else into reportable format. With Niku 5 that was Niku Reports Download. Additionally there was something nice actually NICE Niku Curve Etractor which could be used for more customized reporting and data transfers to other databses than just Access. When Niku 6 cae the first word I got was that the blobs were gone. However when it came the blobs were still there but they were cracked into slices a couple of times every minute to simulate the real time access to time scaled data PMW had provided. The word again was the the blob cracker had be recoded in java with instructions in Dutch. Niku EMEA was in the Netherlands at the time, Later timeslicing was made a regular job scheduled once a minute out of the box.    I consider scheduling it any other way compromizing the integrity of the data, because you get different results depending where you look at the data.  I once commented that I was told that the blobs are gonna go, but the person who said that is no longer with Clarity. You have to have better connection than me to know for sure.  Martti K.

another_martink wrote:
Rather than using the system slices I prefer to use the user configurable slices
I agree - and offer further comment: System slices are designed for system purposes and not always sutiable for all reporting/analysis. The Monthly slices (referred to earlier in this thread) are mostly aimed at capacity planning for instance.   The need for slices arises from the use of "curves" and whether we like ‘blobs, ‘blobcrack’, ‘timeslices’ or not; all are important parts of the application.   Word documents can be represented in XML, and therefore in theory we could decompose each document into “structured dataâ€? suitable for storage and enquiry by SQL â€" but typically we don’t. We store the entire document as a blob â€" mostly for performance. In essence of the use of blobs to store “curvesâ€? and “calendarsâ€? is similar, the approach used by ABT then Niku now CA is fundamentally for performance because a “curveâ€? can describe a complex timescaled pattern in a few bytes without needing a range of joins into potentially large tables. Arguably also; the use of blobs for curves/calendars is an example of “object orientationâ€?.   Anyway, I thought I might point you to a few relevant KB articles regarding timeslices, and then go on to make a suggestion or two:   TEC435563 Clarity Timeslice Terminology Explanation  https://comm.support.ca.com/?legacyid=TEC435563=   From Date : This is the date that you want to start reporting from.    Slice Period : You can set the periods to daily, weekly, monthly.   Number of Periods : Specify the number of periods you would like reported.   Expiration Date and Rollover Interval : [my intepretation is:] This is date when this type of slice data will be rebuilt.    Last Run Date : Indicates the last time Timeslices were run.     TEC435572 How should I configure my timeslices in Clarity  https://comm.support.ca.com/?legacyid=TEC435572=   This article is recommended reading, some extracted points of interest   There is no need to slice estimates in the past … … Also set the Number of Periods to 400. … If you are not [ really ] maintaining allocation at the Project level company wide, you may have no need to maintain slice data for allocation. … If you are truly using this allocation data it should also be in the same range as Baseline and Availability. … … method of using the "Allocate from Estimates" button for resource with zero ETC is provided as a quick and easy method to zero out [ unwanted/redundant ] allocation.   [ emphasis added ] TEC439078 How do I completely check the status of the timeslice job? How long will it take my timeslice job to complete (Niku KB ID: 7515) https://comm.support.ca.com/?legacyid=TEC439078=     There is no one query that will give you the complete status of the timeslice job. … but here's how      TEC439075 How do I read data from the Standard Daily Timeslices (Niku KB ID: 7509) https://comm.support.ca.com/?legacyid=TEC439075=        e.g.  --slice records for availability
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 'RESOURCE UNIQUE NAME' and
slice_request_id = 1

      TEC444399 Where are time slices for investments stored (Niku KB ID: 9081) https://comm.support.ca.com/?legacyid=TEC444399=        Answer: Not all time slices are stored in the prj_blb_slices table   The DAILY_INVESTMENT_ACTUALS are stored in the ODF_SL_OPLACT_D table o find the table where each slice data is stored check the table_name column of the slice request.   Run the following query to find out the table name where the data is stored for the defined slice request:   select id, request_name, table_name from prj_blb_slicerequests         When we use SQL to determine items such as %allocation we need to access the relevant timesliced data - exactly what is "relevant" will depend on what the purpose of the query is, how you use allocation within your organization, and the availability of sliced data (system or custom slices, the timeframes defined for these and the units it is stored in). For example If I want %Allocation for me tomorrow "relevant" data is daily because I want to know about "tomorrow", but if I was asked for %Allocation of archiects over the next 3 years just how relevant is daily data (or any allocation data) where I may have little faith that estimated allocations are accurate beyond (say) 3 months ahead?    Please take particular note of the comments made regarding Allocation in TEC435572 How should I configure my timeslices in Clarity  So this brings me to my suggstion:   Timeslices need careful planning, and I like to use MS Project for this (it's also possible with Workbench but I'm assuming MSP here). I suggest you create a grid portlet of timeslice requests, then use Export to Excel, then copy/paste into MSP (attached zip file   has Oracle based query, a portlet and an MSP file to paste the data into). The image file attached attempts to displaywhat this looks like.     Once I have this data in MSP I can slide the bars, or extend/contract the bars, so that I arrive at an overall arrangment of timeslices that will meet my reporting needs.   Some additional points:   'Actuals' are only required in the past 'Estimates' are usually only relevant in the future 'Baseline' covers both past and future 'Allocation' is only useful IF the organization reall maintains ths data - and even then usually only reasonably accurate for a limited horizon.   When setting the number of time units, use things like 400 days (instead of 365) or 14 Months (instead of 12). Doing this allows you to be more certain then the data you need will be available, furthermore you can also help minimize the number of slices that need to be rebuilt at any one time by combining longer slices periods with a mix of rollover intervals. However don't forget that asking for more than you absolultely need (e.g. 400 days) will have a storage impact. So "be reasonable".     Apologies for the long reply.    

The timeslice portlet and export to MSP (OWB) is neat. Thank you for the education.  Regarding the allocation slices wouldn't you want to know planned vs. actual and therefroe extend them to the reportable period in the past?  Which slices should you use? What is the ground rule?E.g. the ones you can see in the Admin tool plus the user created ones?    To the references you might want to add (again, for this was alreary in another thread)   TEC439086[left] Tech Document[left] Title: Error Received: [The time periods requested do not exist. Review the column's Time Scale settings.] after changing timescaled view configurations[left]
Description: Error received: [The time periods requested do not exist. Review the column's Time Scale settings.] after changing timescaled view configurations within views/portlets such as the Resource Utilization page under the Task Tab or the Team Utilization portlet used in the Dashboard Solution: There are limits to what can be display within the TimeScaled field within views/portlets such as the Resource Utilization page under the Task Tab or the Team Utilization portlet used in the Dashboard.These are internal timeslices that can not be configured.
Internal timeslice range is as follows:28 Daily - One week in the past, the current week and 3 weeks in the future
16 Weekly - 2 weeks in the past, the current week and 13 weeks in the future
36 Monthly - 4 quarters in the past, the current quarter and 7 quarters in the future
Year, Quarter and Monthly periods all use monthly slices
Weekly period uses weekly slices .Daily period uses daily slicesRESOLUTION
==================Follow these guidelines when setting up Timescaled view period information.The numbers of periods available are limited in the following manner:Year: may set 3 periods - viewable data will be available for the following range:
1 year in the past to 2 years in the futureNOTE: 1 year in the past is only available if the current date is in the first quarter of the year, once the slices roll over only 3 quarters in the past will be available, as this is less than a year you will get the error if you are requesting dates from a year in the past. Quarter: may set 12 periods - viewable data will be available for the following range:
4 quarters in the past and 8 quarters from current period to future Monthly: may set 36 periods - viewable data will be available for the following range:
12 months in the past to 24 months from current period to future Weekly: may set 16 periods - viewable data will be available for the following range:
2 weeks in the past to 14 weeks from current period to future Daily: may set 28 periods - viewable data will be available for the following range:
7 days in the past to 21 days from current period to futureThe start date that you choose - be it rolling or specific must fall within the date ranges listed above. The date which determines the past/future line is the current system date.These data ranges may not be changed by end users, nor through customizations to the system. Even if you have timeslice data that is outside those ranges, the system still limits the view to the ranges specified above.Therefore, even though you may have weekly and daily data within your requested range for example as far back as January 1 2005 to October 25, 2005, because that date range does not fall within with the limits for Weekly and Daily you will see the error telling you to check your timescale configuration.MORE INFORMATION
==================The Timescaled views setting are dependent upon the system timeslices which are not customizable by the end user.The Monthly, Quarterly, Yearly slices rollover quarterly. So 22 months, 7 quarters and 1 year will only be available during some months.Enhancement has been submitted to allow end users to change what the timescaled views are dependent upon.  Martti K.[left][left][left][left][left][left]   Message Edited by another_martink on 08-24-2009 04:00 AM [left]

another_martink wrote:
The timeslice portlet and export to MSP (OWB) is neat. Thank you for the education.  Regarding the allocation slices wouldn't you want to know planned vs. actual and therefroe extend them to the reportable period in the past?  Which slices should you use? What is the ground rule?E.g. the ones you can see in the Admin tool plus the user created ones? V.pleased the query/portlet works for you - note the query is for  Oracle but easily switched to MSSQL syntax.Also note you can add a virtual column to the portlet to represent the timeslices  as a Clarity gantt - and you might want to add the table_name from the slice requests too. (If you do these things you need to modify what columns you paste into MSP.)   "allocation slices wouldn't you want to know planned vs. actual " > Actually no, I wouldn't - but some  will disagree - and that's the point: Each client will have slightly different needs and timeslices should be designed to meet those needs. I believe allocation data is fundamentally about the future - it reflects what we think is going to happen.  Once that data is historic I do not believe it has great value, and this is particularly true if users have reset allocations from the resource plan (nb: a recommendation from the support reference above).    "therefore extend them to the reportable period in the past?" > I might actually do this,  but it would not alter my view as to the relevance of historic allocation data  . But I stress this is just  my opinion.   "Which slices should you use?" >  There is no absolute rule; it "depends" on what you are trying to achieve. Each requirement needs to be assessed on its own merits.   "What is the ground rule?" > lol, see above    here's some suggestions:   keep daily data to approx one year (e.g. 400 days) or less if possible weekly/monthly data may be for longer periods  
don't extend  slices for actuals  too far beyond  "now" - there's really very  little point. estimates (ETC) should be into the future,  and not historic ("estimate TO complete" in the past is an oxymoron :-) baselines should span the range of actual and estimates allocation data (see the support notes) is optional - if an organization does not REALLY/TRULY/ABSOLUTELY use allocations then minimize these. If the organization REALLY/TRULY/ABSOLUTELY use allocations then it will most likely be treated like baseline, but I believe it should be treated like estimates.
use number of units like 400 days, 14 months, 56 weeks to arrange the slice periods so that  you are unlikely to "run out" of needed timescale all active slices  should cover "now" (or, alternatively put, arrange slices relative to "now")   Also  take into consideration how large the site is and consider table size/query performance as constraints on your timeslice design.  Keep in mind that Clarity (not 7x) also has custom TSV's (time scaled vectors).  I can't predict the purposes these will be used for.    

Re:Which slices should you use? What is the ground rule?E.g. the ones you can see in the Admin tool plus the user created ones?  Actually I was not thinking of how to define slices, but which slices to use eg. PRJ_BLB_SLICES or PRJ_BLB_SLICES_D_AVL and others like that.My view is not to use anything other than PRJ_BLB_SLICES for the reasons I given earlier.  Martti K.    

There is a slight technical advantage (SOMETHIMES) on using the (eg) D_ETC table rather than the base PRJ_BLB_SLICES table...  This is that the D_ETC table is SYNCHRONOUS   (refer to the IS_SYNCHRONOUS column on the PRJ_BLB_SLICE_REQUESTS table) - this means that when the relevant underlying record (eg PRASSIGNMENT) is amended in the Clarity GUI, then the data in the D_ETC table is updated immediately, the PRJ_BLB_SLICES data is not updated "in real time", you have to wait until the next "Time Slice" job has processed the PRASSIGNMENT record.  This might be useful?   This might not!   You decide!  (There are functional downsides of the D_ETC table, though; as has been pointed out earlier the "time range" is probably MUCH less that can be configured for the PRJ_BLB_SLICES tables, and note that if the data is changed through the scheduler then you still have to wait for "Time Slice" to process the data (i.e. after you save back from OWB to Clarity, then the D_ETC updates are NOT synchronous).  I have wondered what would happen if we changed the IS_SYCHRONOUS value against one of the "normal" slices....?   Would the slice be updated OK in real time (possibly!)?   Would this KILL the performance of the application (I suspect DEFINATELY).   I have not been "brave" enough to try this, and I certainly DON'T recommend it!  Dave.

Thanks again for the education.Where would you learn this kind of tricks?  Martti K.

:-)  Usually from the customer asking me awkward questions.... "but why does it work like that?"... "can't we do this?"... "why doesn't that work like this?"...  I then have to experiment with the system, raise support questions, look in CAForums (old and new) in order to give them an answer that is technically correct, rather than just "I don't know" or "thats just how it works".  A lot of the bespoke anaylsis I do against the Clarity database involves looking at the slices too so I understood the data model behind Clarity probably before I really "got" the Clarity functionality.  I have also spent some time looking at the Clarity code behind slicing too (the stuff in the META-INF\blobcrack directories on the app server), thats a fun read for sure ( not to be recommended!)!!  Dave.    

Hi,  It seems  it is ending as an annoying  experience for me - regarding % Allocation Field.  When business asked me can we show this % Allocation column in the Extracts / Reports,i started digging this and found as it is not possible(?).i found in KB article tooand posted in Linked in.Seems it is not possible.Then reverted back to business that it is not possible and they decided to put an custom attribute(they know it is not an  clarity system field and will not beshown in the application any where)  and now it is shown in 2 Portlets & reports.The only advantage is it is not used intensively as we are planning for an upgrade believing thatResource & Demand Management is enhanced there.  Now i need  to go back to business and tell that it is possible to show in the reports and do some manual work.(Migrating from the virtual field to the system one).   I am happy that got an answer in this Great Forum but disappointed with CA KB Article.   Document ID:       TEC439131
Tech Document
Title:   Where is the resource Default % Allocation field stored in the database (Niku KB ID: 7594)SYMPTOMS
==========
Where is the resource Default % Allocation field stored in the database?  CAUSE
======WORKAROUND
=============  STATUS/RESOLUTION
==================MORE INFORMATION
==================
Default Allocation - Default   % Allocation is prallocdefault -   which is stored in the pralloccurve field of the PRTeam table.
 
This makes Default Allocation a virtual value which is derived from the allocation curve.   It cannot be pulled from the database.
Keywords
========
percent allocation, prallocdefault  regards,sundar

Sundar,Did you take a look at the cust_utilcast.rod report? The are using %allocation in the report.  I don't think it can be done in an portlet... I am trying to do this as well...    

Keri,  i think it is doa-able in n-sql portlet as far as we can achieve the result in  a sql.i will test and revert back to you soon...  regards,sundar

Sundar,I have been working on it from the posts in this thread...however, the %allocation is not correct in all resources...    

cust_utilcast.rod pulls the data from NBI_RESOURCE_TIME_FACTS  Martti K. Message Edited by another_martink on 08-27-2009 05:30 PM [left]

Hi Martti,Yes, you are correct. :-)  It looks as though, the Project Team page is using the weekly avail and alloc slices as well as prallocsum from prteam table.     CASE WHEN SRM_RESOURCES.RESOURCE_TYPE     My requirement is to create a allocation portlet much like the project team page (detail) to have the allocation spread (timescale) as well as include ETC's.   Message Edited by furrelkt on 08-28-2009 08:29 AM [left]

    My requirement is to create a allocation portlet much like the project team page (detail) to have the allocation spread (timescale) as well as include ETC's.       Keri - we have built similar portlets in the past - all just driven off the timeslices though.  Its a real frustration to me, as I know (and have explained to the users many many times) that they can get the information they are after just by reconfiguring the existing Clarity pages* (or via an OWB view if they like), but they just will not listen to me and insist that we build them portlets that "look" exactly what they want them to look like.   It gets a real pain to keep changing the portlet code to incorporate their new requirements, when Clarity delivers those requirements "out of the box".  So my advice is; DON'T DO IT....   get the users to use the system instead.   (But I have failed so bad at that!)  :-(  Dave    (* - i.e. Team -> Detail -> Time Scaled Value, add in the column you want - bingo!)  

Dave,Yes I agree. It seems like before (a year ago), we had numerous reports, all doing the same thing but in different flavors... We actually had 7 reports all doing forecasting, but i managed to group them all up and created 1 report able to run the 7 different ways the users asked for.  The custom portlets are now being created in the same fashion. Creating them and looking exactly how they want them to look...   that they can get the information they are after just by reconfiguring the existing Clarity pages* (or via an OWB view if they like) We also have some custom views for the users in OWB. However, I am thinking that there are going to be switching over to Project...since our clients like the views they can get from MSP.  I agree that the reconfiguring of the existing clarity pages would seem to be a better solution especially since there are so many different columns you can add to the existing pages. But again...might not be "exactly" the view our users want...        

Kerry, I created an function using the sql in my development environment(Planning to include this sql by means of a Join in Pdn Instance) to get    the % Allocation Field in Portlet.i tested this function for few resources and it   seems correct and one more great advantage  it shows the values dynamically. Then included this function in portlet and seems to be working-->Great.     Actually i have created 2 Portlets for Resource Allocation.     One portlet(source-->Team object) used by PM's for editing the allocation start,end date,open for time entry.Iam planning to show default allocation(Dummy one) and provide a link to that field that will take them directly to    Project Staff Member Properties: Staff Member Properties Page where they can edit the % Allocation.Here    the object level   portlet advantage is they can edit the attributes for any project they wish in a single Go.  You can bring the etc,actuals,allocation as they are all available in the Team Object.PM's are happy for one reason,no need top click several links to go to the Staff Member Properties Page for each project.It's a short cut.     As dave rightly pointed out the users themselves can do that in the Team tab by configuring but no one is doing that.They are happy with the portlet.     Another portlet using n-sql where we are users are using it for Reporting.i have now added this function in that n-sql and it's fine.we need to have records in the slice(Only when availability change then the values will not be Live).Any way i will check for another round and let u know.     CREATE OR REPLACE FUNCTION Z_GET_RES_ALLOC_PERCENT  (nRESOURCEID IN NUMBER,nTEAMID IN NUMBER)  RETURN VARCHAR2  IS  PER_ALLOC VARCHAR2(24);     BEGIN  SELECT CASE WHEN RES_AVAIL 0 THEN (SLICE/RES_AVAIL) * 100 END INTO PER_ALLOC  FROM PRTEAM TEAM,SRM_RESOURCES SRM,  (  SELECT PRJ_OBJECT_ID, MIN(SLICE_DATE) ALLOC_START_DATE,  MAX(SLICE_DATE) ALLOC_END_DATE, MAX(SLICE) SLICE  FROM PRJ_BLB_SLICES_D_ALC  GROUP BY PRJ_OBJECT_ID, SLICE  HAVING SLICE 0  )ALLOCATION,  (  SELECT PRJ_OBJECT_ID, MAX(SLICE) RES_AVAIL  FROM PRJ_BLB_SLICES_D_AVL  GROUP BY PRJ_OBJECT_ID  ) AVAIL  WHERE  TEAM.PRRESOURCEID = SRM.ID  AND TEAM.PRID = ALLOCATION.PRJ_OBJECT_ID  AND SRM.ID = AVAIL.PRJ_OBJECT_ID  AND SRM.ID = nRESOURCEID  AND TEAM.PRID=nTEAMID;     IF PER_ALLOC IS NOT NULL THEN  RETURN PER_ALLOC;  END IF; EXCEPTION  WHEN NO_DATA_FOUND THEN RETURN 'N/A';  WHEN OTHERS THEN RETURN NULL; END;  /

Yeah, that function is "guessing" :-) at the "allocation" (I have used something similar myself by grabbing the average of the allocations from the slices over a period) - but its not really the DEFAULT allocation (like what you can just see on the allocation screen).   It does guess at a couple of interesting numbers though for sure!  If you can convince your users that this is a useful "guess" (or even convince them its an honest value!) then you are OK .  Dave.

Sundar, looks great, and yes dave it's a guess as it's not showing the actual % allocation from the clarity page.  Again, another reason to get the users to use the clarity pages. However, we are all in agreement that users just plain want what they want and are bound by what our users want to see. :-) So, we give them a custom portlet...  thanks for sharing your accomplishments with us!! :-)  

another reason to get the users to use the clarity pages you say.The reason why I suggested using the standard slices was because the clarity pages simply would not display the data required, because they were hard coded for the time range and secondly they did not have the data they were supposed. So the data the weekly detail and Resource workload displayed was just ****. That was in 7.5.2 or 7.5.3. Another desired feature is that if you have a range of 3 months or weeks to the past and to the future you don't want to display ETC or actuals for the whole range because there should be no actuals in the future nor ETC in the past, but the users want to know where we have come to and where we are going to ie in the same view actuals in the past and ETC for the future. Yes again. That no big deal if OWB is used and that is the choice to use.  I guess I have failed, too.  Martti K.

Paul & Martti,  Thanks for making me understanding(Still going through) a difficult Topic (For me) in a clear & straight way.we have to live with slice / blob as it was an nucleus for the Reporting and simplicity.    Taken from  Paul Reply  -  When we use SQL to determine items such as %allocation we need to access the relevant timesliced data - exactly what is "relevant" will depend on what the purpose of the query is, how you use allocation within your organization, and the availability of sliced data (system or custom slices, the timeframes defined for these and the units it is stored in). For example If I want %Allocation for me tomorrow "relevant" data is daily because I want to know about "tomorrow", but if I was asked for %Allocation of archiects over the next 3 years just how relevant is daily data (or any allocation data) where I may have little faith that estimated allocations are accurate beyond (say) 3 months ahead?   In our case we have started using the resources like DBA (Tagged to Seperate OBS) who will be assigned   in Multiple Projects.we are looking the % allocated only for 3 Months level.PM's who can go and allocate the DBA resources and enter the Allocation start and end date and also enter the % allocation should be meaningful.If an DBA is already allocated to an project,he should not allocate 100% in the same time.As in clarity an resource can be overallocated to any hours in a single Manday for multiple projects, there is no control via system.When adding an resource to an Team (It will throw an pop up that the resource is already allocated he should be only overallocated).So our requirement to build an portlet / report that for an resource showing the allocation % and allocation dates.My question is1.can we this Query to show the % allocation.2.can we create an custom slice for getting the data the same stored in this Table PRJ_BLB_SLICES_D_AVL.  SELECT PRJ.UNIQUE_NAME "Project ID", PRJ.NAME "Project Name", SRM.UNIQUE_NAME "Resource ID",SRM.FULL_NAME "Resource Name", ALLOC_START_DATE "Allocation Start Date", ALLOC_END_DATE "Allocation End Date",SLICE "Allocated Hrs", RES_AVAIL "Available Hrs",CASE WHEN RES_AVAIL 0 THEN (SLICE/RES_AVAIL) * 100 END "% Allocation"FROM PRTEAM TEAM, SRM_PROJECTS PRJ, SRM_RESOURCES SRM,(SELECT PRJ_OBJECT_ID, MIN(SLICE_DATE) ALLOC_START_DATE,MAX(SLICE_DATE) ALLOC_END_DATE, MAX(SLICE) SLICEFROM PRJ_BLB_SLICES_D_ALCGROUP BY PRJ_OBJECT_ID, SLICEHAVING SLICE 0)ALLOCATION,(SELECT PRJ_OBJECT_ID, MAX(SLICE) RES_AVAILFROM PRJ_BLB_SLICES_D_AVLGROUP BY PRJ_OBJECT_ID) AVAILWHERETEAM.PRPROJECTID = PRJ.IDAND TEAM.PRRESOURCEID = SRM.IDAND TEAM.PRID = ALLOCATION.PRJ_OBJECT_IDAND SRM.ID = AVAIL.PRJ_OBJECT_IDAND SRM.UNIQUE_NAME = '1172507'ORDER BY PRJ.ID, SRM.ID, ALLOC_START_DATE  regards,sundar

Yes you can use that query if you want to.Though why don't you want to use the PRJ_BLB_SLICES table?That would be much easier to verify for the time range it covers from the GUI.Have you verified that   PRJ_BLB_SLICES_D_ALC and PRJ_BLB_SLICES_D_AVL cover the timeframe you need?Though the benefit with PRJ_BLB_SLICES_D_ALC is that it already has the investment_id and resource_id.  Yes again in the GUI you can create a custom slice which contains the same timeframe and availability or allocation, but the data will go into the PRJ_BLB_SLICES table   and I expect it   not to   have investment_id or resource_id.  Martti K.