Top Item Sales Report by Universal Base Item Number

emulsified
emulsified Member Posts: 139
edited 2011-04-13 in NAV/Navision Classic Client
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?
Half-empy or half-full how do you view your database?

Thanks.

Comments

  • Savatage
    Savatage Member Posts: 7,142
    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.
    Harry Ruiz
    http://www.BiloBeauty.com
    http://www.autismspeaks.org
  • emulsified
    emulsified Member Posts: 139
    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?
    Half-empy or half-full how do you view your database?

    Thanks.

Categories