Why Can't I get the correct Value in Crystal Report?


I have a report which is showing two totals that comes from 2 different tables.

1st table is the committed cost (PO_Items.Cost using formula sum({PO_items.cost})
2nd table is the Budget cost (ACCOUNT.Cost_BAC_Approved)  which can also be seen under Project.Cost_BAC_Approved.
the PO_items is being shown using grouped by PO_ID
the Budget is being shown using  Group_ID_A  


Using the attached snapshot of the link, I cannot correct one of the item, if I link the Account to the Purchase Order Tables The PO_items.cost is giving me different amount it multiplies to numerous amount.  but the other the budget amount is show incorrect amount.  If I I take of the link between Account and PO the committed amount is correct and the budget is blank or multiplying as well to numerous amount.

I am attaching the snapshot of my link and the RPT file itself.

Please help me to show the correct value of the PO using PO ID and the Budget Using one of the Group ID (package Number).


romeo BallesterosAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Without more information on the data, it is difficult to determine the cause.

I assume since this report is totally in the report header, that you have additional filtering you are doing to limit it to a single project.
Part of your problem is the relationship between the PO table and the PO_Item table is 1 to many thus the vast increase in one of the items.
 Is the relationship between the project and the attributes table 1 to 1 or 1 to many?
Is the relationship between the Project table and the Account table 1 to 1 or 1 to many?

Are you trying to report by project? by Item?, by Account?

If by project can you try this

Link FROM Project to Attributes, Purchase_Order, and Account. Link from Purchase _Order to PO_Item
See attached

The only summary you should need will be for the PO_Item cost.

Filter to get 1 project

Link from

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
romeo BallesterosAuthor Commented:
Tha nks mlmcc, I am reporting both PO and budget by package number.  The PO is driven by the PO ID were package number is included in the PO_ID structude (ie P26). The budget is re presented by the  field Gtoup ID_A which is the package No.


romeo BallesterosAuthor Commented:
HI mlmcc,

I've got the correct linkage.  Thank you very much.  Can I add additional question?

With regards to Previous Totals, if in case there is no field on the previous total, is there a formula to use in order to have the previous total?

Or, i am thinking of creating additional numeric parameter and title it Previous committed Cost, I am just not sure if parameters can be used in the add formula.  Something like sum ({POTOTAL} + {? Previous Committed Value}

Is it possible to include numeric parameter into a sum formula or add formula?


Inside the report parameters are just objects and can be used as you desire so long as the data types work.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.