Report - History of selling price of an item

Business Central/NAV User Forum
1

Need to develop a report that will contain:

Part Number

Part Description

Customer

Salesperson

Posting Date

Item Selling Price (per unit)

The report needs to show all the posted invoicing for a particular part. Can someone get me started in the right direction? My experience level consists of developing reports before but I am having a little difficulty on this particular report.

Thanks!

2

HI,

You can get all this information from the item ledger entry Table.

Just select Item Ledger Entry as you dataitem and put the filter for Entry Type Sale.

Then Pritn the information in The body section. you can apply the item no , Posting Date filters while running teh report.

hope it helps!!
MS

3

As Maggie says, the starting point would be Item Ledger Entry, connected to the Item Table:

Item

Item Ledger Entry

Link Item ledger Entry:Item No to Item.No
Sort by - Entry Type,Item No.,Variant Code,Source Type,Source No.,Posting Date
Filter Entry Type = Sale.

The Sales person you are safest getting from the customer table.

Get the basics done, then post more questions here to get through the further issues.

Also decide up front what sortign and filtering you will want. Because if for example you want to sort or filter by Sales Person, then it will be more complex.

Good luck.

4

Thanks for the replies. This will get me started very well. The primary sort will be on the item number.

If/when I need a little more info, I’ll post a question.

tThank you!

5

Great. Please keep us informed, and don;t forget to come back and mark the topic as resolved, and give it a start rating once the report is working.

6

Appreciate the replies. The table has a lot of info except a couple fields necessary for the report. The history that the salesforce needs is history of the item by selling price per item. The table has the total line amount (quantity x unit selling price), but the salesmen need the selling price based on the unit selling price.

Any thoughts?

Thanks…

7

If Quantity = 0 then
SalesUnitPrice = 0
else
salesunitprice := round(Amount / quantity);

8

Thanks, David, for the code!

9

Looks like I am getting some results now. By substituting “Selling Price (Actual)” for salesunitprice, the formula is now returning unit selling price. I need to check the results to verify. The document number is the shipper, but I prefer posted sales invoice.

I’ll start on these areas once I know the results are what I am looking for in the report.

Thanks again!!

10

Forgive me for interfeering, but isn’t it best to get your values from the Value Entry table instead the Item Ledger Entry?

The actual posting of the invoiced items and the proper values go to the value entry table, because sometimes an item gets shipped with a price but invoiced at a later time with a different price…

It is just a thought…

11

Correct, all the amounts will eventually need to be derived from the Vlaue entries tables. But the issue at hand is how to create the base report and especially to get the sorting correct. If you use Value entries, then the report becomes much more complex to write, sinc for one actual Item transaction there will be multiple Value entries.

In fact the number used here will be “Sales Amount” fromt he Item ledger entry, and that value is actually a flow field directly derived from the Value Entry. So you in fact the numbers are coming from the Values entries, I should have clarified this int he begining. Thanks for pointing it out.

12

Don’t forget to get the other tables records using the GET command.
Item
Customer
etc.