Item Listing GROUPED by UnivItemNoBase & SORTED by Sales ($)

emulsified

I'm stuck.

I need to create a report that will do the following:

List all items Sales ($) GROUPED by a custom field we have in the Item table named UnivItemNoBase
SORTED by Sales ($)
FILTERED by Posting Date
FILTERED by Item Category Code

UnivItemNoBase is a field in our Item table and is a KEY.

Some sample data from our Item table:

No. = 4567XL
UnivItemNoBase = 4567
We sold 100.

No. = 4567L
UnivItemNoBase = 4567
We sold 200.

No. = 4567M
UnivItemNoBase = 4567
We sold 300.

No. = 4567S
UnivItemNoBase = 4567
We sold 20.

No. = 2508RED
UnivItemNoBase = 2508
We sold 5.

No. = 2508BLUE
UnivItemNoBase = 2508
We sold 10.

No. = 2508GREEN
UnivItemNoBase = 2508
We sold 0.

No. = 3801N
UnivItemNoBase = (BLANK)
We sold 6.

No. = 3801Q
UnivItemNoBase = (BLANK)
We sold 40.

I was able to create a report with the wizard that did give me individual Sales ($) and also GROUP footers with TOTALS, but how do I get it to SORT by the GROUP footer Sales ($) ?

With the above data in mind my report output should look like this:

Grouped by UnivItemNoBase and sorted by Sales ($):

UnivItemNoBase Sales (Qty) Sales ($)
2508 15 9,050.00
4567 620 6,258.00
3801Q 40 85.00
3801N 6 12.00


Grouped by UnivItemNoBase and sorted by Sales (Qty):

UnivItemNoBase Sales (Qty) Sales ($)
4567 620 6,258.00
3801Q 40 85.00
2508 15 9,050.00
3801N 6 12.00


In the examples above 3801Q is actually the "No." because somebody left UnivItemNoBase field in the Item card blank so it was unable to be grouped.
In the examples above 3801N is actually the "No." because somebody left UnivItemNoBase field in the Item card blank so it was unable to be grouped.

I hope I've explained my dilemma clearly. Can anyone help out here? Please? Much thanks for any assistance.

mstallmann

You won't be able to sort by Sales ($) in a simple manner as it is a flow field. Flow fields can't be indexed for sorting. What I would do is create two data items. The first one would be your Item table. The second one would be a temporary table.
In the first data item, populate your temporary table in the OnAfterGetRecord trigger.

Set your sorting on the second temporary table data item as you want and use that for displaying your totals.

(NOTE: If you don't want to use the table, the report columns are fairly simple and you can predetermine the boudn of the array up front, another option is to use an array to store the values from the item table and an integer data type as teh second data item.)

Let me know if that helps,

Mike