cancel
Showing results for 
Search instead for 
Did you mean: 

How to build Project Budget report with "IF-then" formula?

Former Member
0 Kudos


HI

I have user requirement to create this report:

Controlling Area Currency CAC Object Currency (OC)Amount to be returned calculationAmount to be used for Budget Return
Budget (CAC)Actual (CAC)Remaining Budget (CAC)Budget (OC)Actual (OC)Remaining Budget (OC)CAC Remaining
  Budget converted to OC using Budget rate (OC)
OC Remaining Budget converted to CAC using Budget rate (CAC)
(1)(2)(3)(4)(5)(6)(7) = (3) * Budget rate(8) = (6) / Budget rateif (8) => (3) then
  (7), else (6)
Project 1
Project 2
Project 3
Project 4
Project 5

My dilemma is wich tool to use to build the report & how.

CJE2 & CJE6  Project Hierarchy Reports

I have managed to create columns 1 - 6 using a copy of forms from SP1 Reporting Group

but I fail to create "IF - then" formula

GRR2 Report Painter

SP1 & SP2 Reports are not available there to copy from, while  SP3 has no Budget data available

GR32 Report Writer

I'm just not sure how to manage it & where to start from? Is that realistinc to do without special training?

Please could you help me to figure out how can get to deliver that user requirement?

What shall better do?

Thank you very much in advance,

Daria

Accepted Solutions (1)

Accepted Solutions (1)

sammar81
Employee
Employee
0 Kudos

Hi Daria,

Better use your technical guys help and develop a Custom report.. I don't think you can achieve it by modifying the standard reports using Report Painter..

You can use RPSCO, COSP, COEP for plan and actual cost values and BPJA/ BPGE for budget related data..

hope it helps..

Regards

Sammar

Former Member
0 Kudos

thank you

sammar81
Employee
Employee
0 Kudos

Hi Daria,

In addition to what Sunil has mentioned , i think you don't need to get into the AFVC table unless you are using network activities under the WBS. Even then I don't see a need for AFVC as your budgeting is at the WBS level.

And  your requirement is to capture Budget, Actual Cost and Remaining Budget and Return as per your formula..  here You should be looking at the below tables:

1. PROJ : For Getting the list of Project.

2. PRPS : for getting the WBS for the Projects..

3. COEP or COSP OR RPSCO.

Though you can get all your values in RPSCO but this is a huge table and you can hit some performance issues.. So you can use COEP or COSP ( if you want to use Line items or totals)..

Rest you can go with the logic given by Sunil.. Just filter the values by the Value type for each project and you should get your desired result..

Regards

Sammar

Answers (1)

Answers (1)

sunil_yadav2
Active Contributor
0 Kudos

Hi Daria,

you should go for table COEP. in this table you will get all currency postings (Object Currency as well as Controlling are currency).

if you want logic than let us know.

thanks

Sunil

Former Member
0 Kudos

Yes, please, I would be grateful

sunil_yadav2
Active Contributor
0 Kudos

please check below logic:

Select * from PROJ

Pass project to PRPS-PSPHI and fetch PRPS-POSID, PRPS-ONJNR

Pass Selected WBS to AFVC-PROJN and fetch AFVC-OBJNR

Pass PRPS-ONJNR & AFVC-OBJNR to COEP-OBJNR and fetch COEP-BELNR, COEP-WTGBTR where COEP-WRTTP=4 and VRGNG NE KOAO

also get below fields from COEP as per requirement:

WTGBTR-Value TranCurr

WOGBTR-Value/Obj. Crcy

WKGBTR-Val/COArea Crcy

thanks

Sunil

Former Member
0 Kudos

thank you will have a look