How to create report which order the Total Fields?

selece28

Hi Nav masters,
Currently i'm having a problems, My client wants me to create a report which display Total Item sold per Item.

I create a report with Sales Invoice Line, group by Item No, and Total the Quantity. I display the Item No, Invoice No., and Quantity per line.
The at footer i sum the Qty into TotalQty.

Then the problem is, my customer want me to order from the highest TotalQty. Can Nav report do this?

Please help me,

Thanks

Sunset

Yes you can, but you have to do it in 2 loops. First loop you calculate all the data and sort it, second loop you print it according to sorting.

For hints on how to do it you can look at the top 10 reports. 111 Customer, 311 Vendor and 711 Inventory.

Savatage

Personally I would use the Item legder Table not the Sales Invoice Line.

Sales Invoices can be deleted.
It will be faster.
Plus you can take into account Sales & Credits.

*EDIT*
Sorry I didn't see where you said you needed the inv#
also, when we do reports about items, Sale Inv# is rarely something necessary. Plus when you 1000's of invoice with that item - your report will be huge.

themave

If you create a key based on sales qty in the item table

you can make the item, the first data item in your report and the sales invoice line as the second data item

you won't have to loop since you can sort by the key. It seems as if the item is what is important to you, so by basing the report on the item table you can display more item informaiton, and then give the detail lines for the invoice info.

selece28

themave wrote:

If you create a key based on sales qty in the item table

you can make the item, the first data item in your report and the sales invoice line as the second data item

you won't have to loop since you can sort by the key. It seems as if the item is what is important to you, so by basing the report on the item table you can display more item informaiton, and then give the detail lines for the invoice info.

I think i cannot do this, because i want to order from the highest TotalQty, and my item card have thousands of items, this will make my report run slower. Afterall not all item is exist in the Sales Invoice Line table.

Savatage wrote:

Personally I would use the Item legder Table not the Sales Invoice Line.

Sales Invoices can be deleted.
It will be faster.
Plus you can take into account Sales & Credits.

*EDIT*
Sorry I didn't see where you said you needed the inv#
also, when we do reports about items, Sale Inv# is rarely something necessary. Plus when you 1000's of invoice with that item - your report will be huge.

I've think bout item ledger table also. But my client need the invoice number. They want this report so they can see which item sold the most.
Is there any other way?

Sunset wrote:

Yes you can, but you have to do it in 2 loops. First loop you calculate all the data and sort it, second loop you print it according to sorting.

For hints on how to do it you can look at the top 10 reports. 111 Customer, 311 Vendor and 711 Inventory.

i've look at this report, it loops all table, like ex: top 10 customer reports,
it loops all customer records. But my reports can't loop all item records, because there's so many item in item card, it slow just to search, i think it will be lots slower in the reports if i have to loop one item at a time and check if the item exist in Sales Invoice Line.

Is there any better way?

ow, and Thanks for all the reply

Sunset

When your customer wants it sorted based on postings there is no way it can be as fast as if it was sorted by a key on the table.

I'm pretty sure the fasted way is to loop through the item table, calculate "Sales (Qty.)" and sort it into a temporary table. Then loop through your temporary table and display the corresponding invoices.

themave

Never mind, go with the two loops

Savatage

selece28 wrote:

I've think bout item ledger table also. But my client need the invoice number. They want this report so they can see which item sold the most.

What does totaling the sales of an item so see which sold the most have anything to do with invoice numbers? Or do you want to see what invoice has the largest amount?

Odd but it's their report they can have it anyway they want :-s

ps item ledger Entries have the Document No. (inv#)

themave

Savatage wrote:

selece28 wrote:

I've think bout item ledger table also. But my client need the invoice number. They want this report so they can see which item sold the most.

What does totaling the sales of an item so see which sold the most have anything to do with invoice numbers? Or do you want to see what invoice has the largest amount?

Odd but it's their report they can have it anyway they want :-s

ps item ledger Entries have the Document No. (inv#)

I believe the report they want looks like this

Item xys sales 120
inv+12346 Customer name bla bla qty 105
inv+12347 customer name bla bla qty 15

item uoljlj sales 110
inv+123647 customer name bla bla qty 75
inv+123478 customer name bla bla qty 35

and so on

I believe they would be better off starting with report 10162 Top_inventory items, and adding the invoice detail to that report.

themave

Or an easy way to get this report

Item xys sales 120
inv+12346 Customer # name bla bla qty 105
inv+12347 customer # name bla bla qty 15

item uoljlj sales 110
inv+123647 customer name bla bla qty 75
inv+123478 customer name bla bla qty 35

would be to use the item table as the first data item and the ltem ledger entry as the second data item, link the ledger entry to type sales, so it only pulls the sales entries, build your report sections from item and the ledger entries

use the proper filter on the request form for Sales (Qty) <>0 set the date filter