Showing prices of what we sold for..

yuppicide

Navision 3.10:

I am trying to pull up a list of all items we sold last year to Sears and at what prices we sold them to them for. Anyone know how I can do this?! Here's what I have figured out currently:

Goto Inventory -> Item Reports -> Item Sales by Customer

Then click Item Ledger Entry and under Source No. put SEARS001

Problem is, it'll generate 15 pages and for example it'll say Invoice Quantity 2310.00 Amount 20790.00 for one item. If I divide that I get $9 per item we sold it to for.

This is a lot of work doing all that division. We're pretty sure there's a faster way and that it was done in the past. We just can't remember how. We'd like it to just show us the price they paid for each item without having to do that division.

Alex_Chow

Why don't you just program that into the report?

Savatage

or
without building a new report

how about view the Sales Invoice Line table
filter on Sell-To Cust #

Hide all the unnecessary fields copy & paste in excel
then you can go to town.

If this is something you're goin to use alot I would do the division in the form as stated above.

UnitPrice := "Invoiced Quantity" / ValueEntry.Amount;

or something like that.

themave

This is not help to you, but in 4.0 the customer order status form, has all this information, you can filter anyway you want, item, time period, ect.
show qty, cost, sell price and profit.

pretty nice, to just quickly lookup a customer, rather then printing a report.

But in your case, edit the report like it is suggested above.

yuppicide

Well, in the Sales Invoice Line Table it won't show the actual items we've sold them. Only the dollar amount for that particular transaction.

It's okay though, I'll live without. I don't have access to modify forms.

Savatage wrote:

or
without building a new report

how about view the Sales Invoice Line table
filter on Sell-To Cust #

Hide all the unnecessary fields copy & paste in excel
then you can go to town.

If this is something you're goin to use alot I would do the division in the form as stated above.

UnitPrice := "Invoiced Quantity" / ValueEntry.Amount;

or something like that.

themave

The item ledger entry table will show the info,

the source code will be the customer number, it will have the sell price and the cost posted.

Savatage

yuppicide wrote:

Well, in the Sales Invoice Line Table it won't show the actual items we've sold them. Only the dollar amount for that particular transaction.

:-k

Alex_Chow

yuppicide wrote:

Well, in the Sales Invoice Line Table it won't show the actual items we've sold them. Only the dollar amount for that particular transaction.

Isn't the "No." field on the sales invoice line display the items you sold them?

yuppicide

I can now access the Report Designer, yay, but got confused in there.

I went to Tools -> Object Designer -> Reports -> New.

In there I want to create a report that I can filter Sell-To-Customer between a certain date and show every Item we sold them and for what price.

So Example: I want to see every item we sold Sears between 01/01/2005 to 01/01/2006 and what prices.

Any help is greatly appreciated. This is a great forum and as soon as money permits I'll be sure to make a donation.

themave

You could use the built in report

Customer/Item Statistics report 10048

it does just exactly what you are asking for.

If you want to design your own report, start with this one.

Savatage

I'm not sure that report is the one - it combines all the sales for each item into 1.

if an item is sold 3 times thru-out the year all at diff prices do you want to see

item abc123 - Price $1.00
item abc123 - Price $1.05
item abc133 - Price $1.10

item abc999 - Price $2.00
item abc999 - Price $2.05
item abc999 - Price $2.10

that's what you want (for example) right????

--now if you're revisiting this can you revisit why you think the sales invoice line doesn't show what you sold them?

deadlizard wrote:

yuppicide wrote:

Well, in the Sales Invoice Line Table it won't show the actual items we've sold them. Only the dollar amount for that particular transaction.

Isn't the "No." field on the sales invoice line display the items you sold them?

it seems like if you just list all the sales lines for that customer, sort it by item no - you will have what you are looking for? no?

themave

Problem is, it'll generate 15 pages and for example it'll say Invoice Quantity 2310.00 Amount 20790.00 for one item. If I divide that I get $9 per item we sold it to for.

this was from his original request above, it sounded like he wanted exactly what the customer item statistics report gives.

each item, total sales and the total sale amount, so he can modify the report and add one field, average sale amount = total sales amount / total sales.

yuppicide

Yes, 10048 is just what I am looking for except it doesn't show the price of the items. It shows us for example:

Quantity
6160.00

and in another column shows:

Amount
55440.00

.. which I have to divide and it comes out to $9 each.

themave wrote:

You could use the built in report

Customer/Item Statistics report 10048

it does just exactly what you are asking for.

If you want to design your own report, start with this one.

Savatage

yuppicide wrote:

I am trying to pull up a list of all items we sold last year to Sears and at what prices we sold them to them for.

Ok this is what I did in a munite and it works.

Report is 10136 - Item Transaction Detail

Use the Item Datefilter for your dates
on the item ledger entry tab - your "Source No." with be the customer no.

Now all that report is missing is the "Amount" Field. Add it to the Item Ledger Entry Body(4)
Now you need a Item Price too so in Globals add a variable called ItemPrice type Decimal.
On the Item ledger Entry dataitem (F9)
add to the trigger
OnAfterGetRecord()
IF Amount<> 0 THEN
ItemPrice := Amount / Quantity
Else
ItemPrice=0;

Now go back to the sections and add a text box and change the SourceExp to ItemPrice add it next to the Amount Field you added before.

exit and run the report - you will get every item for that customer and it's price. (At this point you can clean up this report) and remove fields you don't need to see and add othes you may want to see - then save it with a new # 50081 for example.

I just dropped two fields into get your results but afterwards that's what I ment by "Clean up"

----edit----
seems I posted a sec too late - are you sure you wanted the Average price you sold the item for? I thought you wanted the exact price.

Kowa

In case you want the price in every single sale :

If you use a report based on value entries, this will be confusing , because there can always be more than one value entry for a sale. You will need a report based on item ledger enties, filtered on sales.

You need a dataitem "Customer" on top, an indented dataitem for the item table ( with no link to the data item above!), a below this an indented dataitem for the item ledger entries. This is linked by the "Item No." to the "No." in the item table, set the key here accordingly. The date filter can be set in the customer table, this filter has to be copied to the item ledger entries data item.
The flowfields ( Sales Amount (Actual) etc.) here have to be calculated, and the total fields you need have to be defined.

themave

Ok, since you only have a short time to do this, this is a quick and dirty way, I am sure there are better. this is for the Customer/Item Statistics report.

Add a new global variable

SellPrice type decimal
add a field to the report on the Value entry group footer (1)
Select Property of the new field and set the
source expression SellPrice
format the decimal places to be 2:2

now hit the F9 key to get in the cal/code editor

on the section CalculateProfit%()

add the following code to the bottom of the existing cal code

IF "Value Entry"."Sales Amount (Actual)" <> 0 THEN
"SellPrice" :="Value Entry"."Sales Amount (Actual)"/-"Value Entry"."Invoiced Quantity"
ELSE
"SellPrice" := 0;

Now run the report, I have tried this and it works fine.

Savatage

I like my way 8)

---

Actually most of my reports I do In Crystal

themave

Savatage wrote:

I like my way 8)

---

Actually most of my reports I do In Crystal

I like your way also. In fact after seeing what you have done in Crystal reports in other post, I have purchased it and am now starting to us it.

but I don't think he can get it done in the short time he has a license for the report writer.

Savatage

Not to be evil - BUT! :evil:
since you have super user rights - you can change the rights on your own user login name to SUPERUSER
Muuuahahahahahhaha!

---

But really - as far as Crystal one of the features (which really isn't a feature) is the unlimited amount of reports that can be created without worring about, "Did I use that #?".
Plus 1000 other reasons - IMHO O:)