Top Item Sales Report by Universal Base Item Number

emulsified

We have a custom field in our Item table named UnivBaseItemNo (TEXT). On the item card for each "No." that we have in inventory we have what we call a Universal Base Item Number field UnivBaseItemNo (TEXT) in the item table.

For example:

"No." = ZZT261
UnivBaseItemNo = ZZT

"No." = ZZT775
UnivBaseItemNo = ZZT

"No." = ZZT1901
UnivBaseItemNo = ZZT

There are several reports in Navision like Top __ Items Sales Report but they only do by "No.".

The goal is to create a report that will group UnivBaseItemNo items sales and create output like this for a given sales (posting date or other) date range:

ZZT 2,400
BBQ 950

Also, if possible have the ability to SHOW/HIDE details:

ZZT
ZZT261 1,000
ZZT775 900
ZZT1901 500
Totals................................................... 2,400

BBQ
BBQ189 900
BBQ201 50
Totals................................................... 950

I am currently using a modified Item Sales Statistics report in Navision that I have writing to a text CSV file all sales for a date range. I then import it in MySQL and manipulate it from there currently to get what I need.

I would like to just do it all in Navision by UnivBaseItemNo sorted by quantity of sales from highest to lowest. My field UnivBaseItemNo is not a KEY. I was thinking of doing something like outputting the report data into a temporary table, sorting it, and then presenting that as the Navision report. I don't quite get how to use temporary tables in reports and how to have them sort the output.

Anybody?

Savatage

First thing I would try is to use the report wizard.
Table Item.
For Fields : Item No & Sales ($)
When it asks for grouping use : UnivBaseNo
When it asks for calc any totals: Sales ($)

Pretty basic just to see if it works as you desire before adding additional fields

You can use a date filter when running the report to get the range you want.
You can show & hide output on the Item Body section to get your "Details" or not.

The only issue at this point is getting it in a sort order from top to bottom.
But at that point I would probably turn this report into an export to excel for the final sorting..but only the item transfooter where just the UnivBaseNo & Sales total up.

emulsified

I tried using the Wizard but no matter which report style I choose it only asks me about sorting and my custom field isn't one of them because it is NOT A KEY.

So I created the report and changed the Grouping and CALC properties for table Item as follows:

Grouping = UnivBaseNo

CALC = Sales(LCY)
Sales(Qty)

I run the report and no grouping takes place. Any other suggestions?