Project and Portfolio Management Practitioners Forum
cancel

How to set up rules to add together several fields and then subtract

Highlighted
Victoria Warndo
Valued Contributor.

How to set up rules to add together several fields and then subtract

How do I set up the Rules in a request type in PPM workbench to add together several columns and then subtract the value of several other columns that have been added togher. For example I have several columns of estimated cost information and then I have those same columns for actual cost information. I then want to subtract the estimated cost columns from the actual cost columns. I'm not certain how to set up the sql in the Rules with the tokens to do the calculation. I have attachments.
3 REPLIES
Andrea Loi
Super Contributor.

Re: How to set up rules to add together several fields and then subtract

SELECT
[TOKEN1] + [TOKEN2] - [TOKEN3],
[TOKEN1] + [TOKEN2] - [TOKEN3]
FROM DUAL

Consider DUAL as a table with one row.
YOu find the exact syntax for tokens in "Dependencies" section of "Rules Window", because all fields used in SQL logic must be added in "Dependencies" section.

If the logic is complex or you have to repeat it a lot of times, you can create a DB function. In this case the query becomes

SELECT
my_func ([TOKEN1], [TOKEN2], [TOKEN3]),
my_func ([TOKEN1], [TOKEN2], [TOKEN3])
FROM DUAL

In addition, if you don't want to execute the function twice, you can use

SELECT
a_subquery.a_result,
a_subquery.a_result
FROM (
SELECT
my_func ([TOKEN1], [TOKEN2], [TOKEN3]) a_result
FROM DUAL
) a_subquery

Victoria Warndo
Valued Contributor.

Re: How to set up rules to add together several fields and then subtract

I've never actually set up my own function, any help on doing that would be appreciated.
This is what I am trying to do... As you can see there are alot of fields...

All of the fields below are summed together and put in the result field Total Base Cash Projec t Cost
select nvl ('[REQD.P.CVG_BASE_CVG_GTS_LABOR_OFFSHOR]',0)
+ nvl ('[REQD.P.CVG_BASE_CVG_GTS_LABOR_ONSHORE]',0)
+ nvl ('[REQD.P.CVG_BASE_CVG_NONGTSLABORONSHO]',0)
+ nvl ('[REQD.P.CVG_BASE_CVG_NONGTSLABOROFFSHO]',0)
+ nvl ('[REQD.P.CVG_BASE_EXTERNAL_CONSULTING]',0)
+ nvl ('[REQD.P.CVG_BASE_TE]',0)
+ nvl ('[REQD.P.CVG_BASE_OPEX_HARDWARE]',0)
+ nvl ('[REQD.P.CVG_BASE_OPEX_SOFTWARE]',0)
+ nvl ('[REQD.P.CVG_BASE_OTHER_PROJECT_OPEX]',0)
+ nvl ('[REQD.P.CVG_BASE_CAPEX_HARDWARE]',0)
+ nvl ('[REQD.P.CVG_BASE_CAPEX_SOFTWARE]',0)
+ nvl ('[REQD.P.CVG_BASE_TELECOMM]',0)
+ nvl ('[REQD.P.CVG_BASE_FACILITIES]',0)
+ nvl ('[REQD.P.CVG_BASE_OTHER_PROJ_CAPEX]',0),
nvl ('[REQD.P.CVG_BASE_CVG_GTS_LABOR_OFFSHOR]',0)
+ nvl ('[REQD.P.CVG_BASE_CVG_GTS_LABOR_ONSHORE]',0)
+ nvl ('[REQD.P.CVG_BASE_CVG_NONGTSLABORONSHO]',0)
+ nvl ('[REQD.P.CVG_BASE_CVG_NONGTSLABOROFFSHO]',0)
+ nvl ('[REQD.P.CVG_BASE_EXTERNAL_CONSULTING]',0)
+ nvl ('[REQD.P.CVG_BASE_TE]',0)
+ nvl ('[REQD.P.CVG_BASE_OPEX_HARDWARE]',0)
+ nvl ('[REQD.P.CVG_BASE_OPEX_SOFTWARE]',0)
+ nvl ('[REQD.P.CVG_BASE_OTHER_PROJECT_OPEX]',0)
+ nvl ('[REQD.P.CVG_BASE_CAPEX_HARDWARE]',0)
+ nvl ('[REQD.P.CVG_BASE_CAPEX_SOFTWARE]',0)
+ nvl ('[REQD.P.CVG_BASE_TELECOMM]',0)
+ nvl ('[REQD.P.CVG_BASE_FACILITIES]',0)
+ nvl ('[REQD.P.CVG_BASE_OTHER_PROJ_CAPEX]',0)
from dual

THEN - All of the fields below are summed together and put in the result field Total Actual Cash Project Cost
select nvl ('[REQD.P.CVG_ACT_CVG_GTS_LABOR_OFFSHOR]',0)
+ nvl ('[REQD.P.CVG_ACT_CVG_GTS_LABOR_ONSHORE]',0)
+ nvl ('[REQD.P.CVG_ACT_CVG_NONGTSLABORONSHO]',0)
+ nvl ('[REQD.P.CVG_ACT_CVG_NONGTSLABOROFFSHO]',0)
+ nvl ('[REQD.P.CVG_ACT_EXTERNAL_CONSULTING]',0)
+ nvl ('[REQD.P.CVG_ACT_TE]',0)
+ nvl ('[REQD.P.CVG_ACT_OPEX_HARDWARE]',0)
+ nvl ('[REQD.P.CVG_ACT_OPEX_SOFTWARE]',0)
+ nvl ('[REQD.P.CVG_ACT_OTHER_PROJECT_OPEX]',0)
+ nvl ('[REQD.P.CVG_ACTUAL_CAPEX_HARDWARE]',0)
+ nvl ('[REQD.P.CVG_ACTUAL_CAPEX_SOFTWARE]',0)
+ nvl ('[REQD.P.CVG_ACTUAL_TELECOMM]',0)
+ nvl ('[REQD.P.CVG_ACTUAL_FACILITIES]',0)
+ nvl ('[REQD.P.CVG_ACTUAL_OTHER_PROJ_CAPEX]',0),
nvl ('[REQD.P.CVG_ACT_CVG_GTS_LABOR_OFFSHOR]',0)
+ nvl ('[REQD.P.CVG_ACT_CVG_GTS_LABOR_ONSHORE]',0)
+ nvl ('[REQD.P.CVG_ACT_CVG_NONGTSLABORONSHO]',0)
+ nvl ('[REQD.P.CVG_ACT_CVG_NONGTSLABOROFFSHO]',0)
+ nvl ('[REQD.P.CVG_ACT_EXTERNAL_CONSULTING]',0)
+ nvl ('[REQD.P.CVG_ACT_TE]',0)
+ nvl ('[REQD.P.CVG_ACT_OPEX_HARDWARE]',0)
+ nvl ('[REQD.P.CVG_ACT_OPEX_SOFTWARE]',0)
+ nvl ('[REQD.P.CVG_ACT_OTHER_PROJECT_OPEX]',0)
+ nvl ('[REQD.P.CVG_ACTUAL_CAPEX_HARDWARE]',0)
+ nvl ('[REQD.P.CVG_ACTUAL_CAPEX_SOFTWARE]',0)
+ nvl ('[REQD.P.CVG_ACTUAL_TELECOMM]',0)
+ nvl ('[REQD.P.CVG_ACTUAL_FACILITIES]',0)
+ nvl ('[REQD.P.CVG_ACTUAL_OTHER_PROJ_CAPEX]',0)
from dual

Question:
What I need to know is how to format another rule that ultimately calculates the difference between
Total Actual Cash Project Cost - Total Base Cash Project Cost = Cost Variance.
Andrea Loi
Super Contributor.

Re: How to set up rules to add together several fields and then subtract

Hi Victoria,

here you can find an example of "create function" statement: http://www.techonthenet.com/oracle/functions.php (I found it by using Google, you can find a lot of different examples).

If the second rule is based on updates of the same fields, you can use the same rule (2 results for one field, 4 results for two fields, ...).

As you have a lot of fields, I suggest you to use some functions.
You can prepare four different functions or prepare four functions in one package (search for ORACLE CREATE PACKAGE and ORACLE CREATE PACKAGE BODY).

I know that it's possible to prepare one function that gives different results, but I never experienced it.
If I find additional information, I let you know.

Have a good day
Andrea