0

Cobra First Period or Last Period Spread Application Using the Cobra SQL Command Utility

Cobra version 8 comes with some good out-of-the-box integration features for Primavera P6, but you will need to get creative if you want to integrate P6 resource assignment curves with Cobra. As it turns out, the standard P6 Integration Wizard in Cobra does not have functionality to allow for direct import of resource curves from a P6 project.

There is an option for importing resource assignments and spread type via Excel, but I found that option to be a little too time consuming for the simple “Last Period” and “First Period” curves that I most often apply. I was hoping to insert the work package ID, resource name, and spread type into a spreadsheet and then run the import, but there were several more required fields to populate and multiple import wizard dialogues and option boxes to navigate. Strangely, even after assigning a “Last Period” curve using either the Excel import method or the typical curve assignment interface in Cobra, my experience is that you are still likely to see budget falling across two periods (thereby skewing future period EV metrics).

In cases where application of a first or last period curve is needed for all instances/assignments of a specific resource within a project, I found the Cobra SQL command utility to be the fastest and most effective tool for the job. As a word of caution, using the SQL command feature in Cobra can be dangerous if you are not careful with your code. Always make a backup of your database before trying “update” commands in Cobra. Execution without proper filtering can affect all projects in the Cobra database. Double checking your “where” filter statements will ensure that you are only running the statement on the project and assignments that you intend to run it on.

sql blog1

Without further ado, here are some instructions and the code that I use to quickly assign true “Last Period” or “First Period” curves to those resources that require it.

The SQL statement:

update tphase set units = case when tphase.df_date = tp.costloadper then tp.bac else 0 end from tphase inner join (select tphase.cawpid, convert(varchar, tphase.cawpid) + tphase.cecode as wpce, tphase.cecode, df_date, ceunits.bac, (select max(df_date) from tphase as t where t.cawpid = tphase.cawpid) as costloadper from tphase inner join (select costelem.cawpid, costelem.cecode, costelem.bac from costelem where costelem.program = ‘Cobra Test Project‘) as ceunits on (convert(varchar, ceunits.cawpid) + ceunits.cecode) = (convert(varchar, tphase.cawpid) + tphase.cecode) where tphase.program = ‘Cobra Test Project‘) as tp on (convert(varchar, tphase.cawpid) + tphase.cecode) = tp.wpce where (tphase.cecode = ‘Equipment‘) and (tphase.program = ‘Cobra Test Project‘)

sql blog2

Some instruction on using and tweaking the code for your project:
This statement allows for true first-period or last-period loading of all resource assignments within a particular project where the Resource ID matches the Resource ID provided. The bolded text has to be modified to ensure that the code runs only on the desired project and resources assignments. First, change the text that says “Cobra Test Project” to your Cobra Project ID (each subquery is filtered by Project ID to improve update speed). Next, change the text that says “Equipment” to your desired Resource ID. Having filtered for your resource, change the text that says “units” (in the first line) to match your resource’s base Cobra “result” type (usually “units” or “hours” or “direct” depending on your specific resource calculation configuration). Finally, you will edit the text that says “max” to coincide with a first-period or last-period cost load. For a first-period load, change the blue text to “min”. For a last-period cost load, the text should show “max”. Once modified, paste the code into the Cobra SQL command interface and execute.

Note: This statement can be modified to apply to resources that begin with or contain certain text. For instance, to apply the cost spread to all resource assignments with resource IDs that begin with “Equip”, change the text that shows (tphase.cecode = ‘Equipment’) to (left(tphase.cecode, 5) = ‘Equip’)

If this solution works for you or you find other interesting ways to apply portions of this solution, please let us know

 

Share on RedditShare on LinkedInTweet about this on TwitterShare on Google+Email this to someoneShare on FacebookPin on PinterestDigg this

Jared Spradling

Leave a Reply

Your email address will not be published. Required fields are marked *


*