how to get quantity by group? (Using count)

hai all

if we want to count by group part no. (in report)

how is it?

part no A quantity 3

SN1

SN2

SN3

part no B quantity 1

ABC

how to get that quantity?

For your information, i only using 1 table

Hi, Stan.

Unfortunately, such thing isn’t possible in NAV (4.0, I don’t know about 5 and 6). What you would like to do is something like COUNT…GROUP BY… SQL expression and it’s not possible, like many other usefull SQL functions. The only way to do it is to use some key to group the records the way you want and to pass through all of them while checking if the group changed. You could use the following in case you do not need or can’t use the standard grouping functionality in reports, meaning using group headers/footers, TOTALSCAUSEDBY function, etc.

Let’s say you need to count how many different Items apear in Item Ledger Entry table. Let’s take this simple key for example - “Item No.”,“Location Code”. Next you need are variables “PreviousItemNo.” and NumberOfItems, which you must set to null at the beggining of the report or OnPreDataItem triger. Code (in the OnAfterGetRecord triger of an “Item Ledger Entry” DataItem ) would be something like this:

if “PreviousItemNo.” <> “Item Ledger Entry”.“Item No.” then begin

NumberOfItems += 1;

“PreviousItemNo.” := “Item Ledger Entry”.“Item No.”;

end;

I hope this helps, bye…

Option 2:

Create a SQL view that sums the records in the manner you want. Then attach the view to a Navision table object (see ADG) and use this linked table in your report.

Option 3 - create a FlowField.

Option 4 - use SQL