Specification of the cost price of an item on a sales order line

(0) Share

Report

Posted on by Lars Hartmann

With information from Business Central, I am looking for an opportunity to make a breakdown of an item's total cost price, so that the item's original purchase price as well as the value of each assigned Charge can be displayed.

For example, it could be the following:

Direct item price - (Item)
Freight - Charge (Item)
Customs - Charge (Item)
Royalty - Charge (Item)

I want to make the breakdown in Power BI and I have thought about using the information in the Value Entry table (5802). But here I have a problem, as the information about the individual Charge (Item) is only on the value entry record for the purchase invoice line in the fields Item_Charge_No and Description, while nothing about this is stated on the value entry record for the sales invoice line.

For each Charge used, there is also no unique connection between the purchase invoice line's and the sales invoice line's value entry records, and I therefore do not have the opportunity to retrieve the information about used Charge on the purchase invoice line's value entry record from the value entry record for the sales invoice line.

I hope there is something I have overlooked so you can help me make a breakdown of the item's total cost price.

I have the same question (0)

All responses (10)

Answers (1)

Suggested answer

MahGah 15,529 on at

Like (0)

Report

Hi

Can you try to publish Page 139 [Posted Purch. Invoice Subform (139, ListPart)].

Thanks

Was this reply helpful? Yes No
Lars Hartmann 5 on at

Like (0)

Report

Thanks for the tip and your help.

Unfortunately, I do not see any possibility to link the information from this table with the Sales Invoice Line records in the ValueEntries table.

I want to be able to make a specification like the following:

Here, the ITEM inventory is the original cost price of 1,250.00 while FREIGHT of 150.00 and PHYTO of 50.00 are Charge Items.

Was this reply helpful? Yes No
Suggested answer

Community Member on at

Like (0)

Report

Lars,

You might consider using Jet Reports or Power BI to pull the data. I have not created the report image you are looking at but Item Profitability samples are on the website (Jet) you can use.

Hope this helps.

Thanks,

Steve

Was this reply helpful? Yes No
MahGah 15,529 on at

Like (0)

Report

Hi Lars

Sorry I missed your last point. in BC do you see that relationship between PO and SO for that specific item?

if yes can you please tell us how BC establish that relationship?

As Steve mentioned Jet Report has the report for item profitability but that report uses item cost in SO Line. I do not know if what you need or not

Thanks

Was this reply helpful? Yes No
Lars Hartmann 5 on at

Like (0)

Report

Hi again

I use Power BI and in the model I have linked sales invoice lines with the corresponding lines in Value Entries, which have the same sales invoice number. and line no.

This gives me a good enough opportunity to calculate the cost price - see previously shown table, which includes direct cost for the stock item as well as the costs that have been allocated with Item Charge from other purchase orders. Unfortunately, these items in Value Entries, which are formed on the basis of Item Charges, do not contain information about the Item Charge Code that is the basis for the item.

When I want to show a breakdown of the cost price from a sales invoice line, it is not possible to show what type of cost (purchase freight, customs, license, etc.) it is that the cost relates to. The field on the entry in the Value Entries (Item Charge Code) is blank.

I want to be able to display the Item Charge Codes as shown in my previous example, but the displayed information "Freight" and "Phyto" do not exist today. It's just information I've put in to show what it is I want.

Below is an overview of the tables in Power BI:

PS: I have renamed Sales Invoice Header (Salgfaklin) and Sales Invoice Lines (Salgfaklin_NA).

Was this reply helpful? Yes No
Verified answer

Community Member on at

Like (0)

Report

Hello,

You are correct that when a Sales Shipment posts the related Value Entries, for the Charge(Item) Direct Costs are not listed under Item Charge No. field. Only Purchase Invoice posting reflect this.

I have never looked into this but possibly your Partners developer can see how hard it would be to denote the Item Charge No., when a Sales Invoice/Shipment posts, in the Description field.

Hope this helps.

Thanks,

Steve

Was this reply helpful? Yes No
Lars Hartmann 5 on at

Like (0)

Report

Hi Steven,

That's also how I have ended this challenge for now asking our Partner to make an extension which can solve this :-)

Thank you for your interest.

Regards

Lars

Was this reply helpful? Yes No
MahGah 15,529 on at

Like (0)

Report

Hi Lars

I have also tried to duplicate your scenario and had the same issue. Seems like Steve's recommendation is the only option.

Thanks

Was this reply helpful? Yes No
Zizanie 5 on at

Like (0)

Report

Hi Lars,

Did you find an answer to this ? I'm also trying to solve it, and I'm running out of ideas ...

Many thanks,

Was this reply helpful? Yes No
Suggested answer

MahGah 15,529 on at

Like (0)

Report

Hi

Looks like they asked their partner to develop the following solution per Steve suggestion.

I have never looked into this but possibly your Partners developer can see how hard it would be to denote the Item Charge No., when a Sales Invoice/Shipment posts, in the Description field.

Was this reply helpful? Yes No