ISTech Support Forum

ISTech Support Forum
http://www.istechforum.com/YaBB.pl
Crystal Reports, ODBC & Access >> Crystal Reports General Issues >> Cost of Goods/Profit/Margin
http://www.istechforum.com/YaBB.pl?num=1114724717

Message started by kristinfitz on 04/28/05 at 13:45:17

Title: Cost of Goods/Profit/Margin
Post by kristinfitz on 04/28/05 at 13:45:17

I am trying to create a report that shows the following fields:

Customer code
SO No.
Estimate Ship Date
Total order $
CGS
Profit (I will create a formula for this)

I have tried using the indexes BKARHINV, BKARHIVL, BKARINVL but I cannot seem to get what I need. I would like to run this report for each upcoming month to figure out what are sales, cgs and profit are going to be. Any help would be greatly appreciated. Thanks. ;D

Title: Re: Cost of Goods/Profit/Margin
Post by aricon on 04/28/05 at 15:05:03

Well, if I understand correctly you want to create a projection, right? But you are using the invoice tables to gets COGS (which makes some sense as it it calculated there) but that is after the fact.

What you would need to do is use the live Sales Order data (BKARINV and INVL) and do the calculation within your report. You would probably need to also bring in the BOMS of your top-level sales order items and then the actual cost fields from the items in the those BOMS in order to calculate it and then populate your report.

Sounds like a BIG undertaking to me, especially in Crystal.... :-/

Title: Re: Cost of Goods/Profit/Margin
Post by Danimal on 04/29/05 at 14:00:38

Kristin - what you are trying to do is actually not that difficult. You will need to link the sales order line item table (BKARINVL) to both the sales order header table (BKARINV) by order number to get the customer code, and to the accounting inventory master table (BKICMSTR) by part to get the item cost. Then, using a little magic with some formulas, determine the item cost and profit/margin.

I have generated such a report using Crystal Reports 7 for Pervasive, send me a message with your email address if you would like me to provide you with a copy.

Best Regards! ;)

Title: Re: Cost of Goods/Profit/Margin
Post by David Waldmann on 05/02/05 at 05:23:52

One of the most fascinating aspects of forums like this one is the broadening of horizons. I never would have imagined the possible use of such a report as described above, seeing that I'll be shipping stuff today that I don't even have orders for this morning. It's just interesting to contemplate not only the many different kinds of business, but the many different ways people do business.

Carry on!