Top 10 Customer orders

Business Central/NAV Developers Forum
1

Hi All,

I have to create a report that lists the top 10 customers based on their orders…So if we have 130 customers i need to display the top 10 customers that have the most amount of orders. I need to display the customer along with the total amount for that customer orders. i tried using report 111 (Customer - Top 10 List) as a starting point but this report doesnt help me much as it is based on the customer table and not on the sales of the customer.

Thanks,

2

Hi,

Although report 111 builds the information from the customer dataitem, it is using the “customer amount” table as a temporary table and then uses the integer dataitem to print this. The same design should work for you.

3

Hi

Create a key in table customer(18) with following field Amount

Now design your report and place the below coding at DataItemTableView property

SORTING(Amount) ORDER(Descending)

Now create a integer variable for example CustomerCount pleace the below code at respective triggers

Customer - OnPreDataItem()

CustomerCount:=0

Customer - OnAfterGetRecord()

CustomerCount:=CustomerCount + 1;

Customer, Body (2) - OnPostSection()

IF CustomerCount = 11 THEN

CurrReport.QUIT;

Run your report thats all

Thanks

Jerome Marshal.J

4

are you sure?

5

Hi David

yes i’m sure David i have checked.If any mistake please Direct me.Check my sample report

4466.Decending.bmp (378 KB)

Thanks

Jerome Marshal.J

6

Hi Jerome,

Where did the amount field get populated?

7

Hi Dadve

Its populating from customer table. (Table Customer and field Amount)

Thanks

Jerome Marshal.J

8

I cant use the Amount field in the customer table…i have to use the Amout field in the Sales Header table because i have to filter on the field “Document type” = order.

I dont want to see the customer’s total amount i want to see the customers total amount based on their orders. Thats why this report seems so difficult for me. but yes the output should be the top 10 customers

9

I cant use the Amount field in the customer table…i have to use the Amout field in the Sales Header table because i have to filter on the field “Document type” = order.

I dont want to see the customer’s total amount. i want to see the customers total amount based on their orders. Thats why this report seems so difficult for me. but yes the output should be the top 10 customers

10

Never use Document tables for reporting! It can be used to print out documents later. but REPORTING --analytical, summarizing, etc, etc-- must be based on Ledger tables (+Detailed), as only there FULL pricing & costing information sits. Document tables are NOT updated with real COGS info, what was grabbed from ItemCard at the moment of creation, that sits there forever. Then Pmt.Discounts further change actual sales amounts, too.

So your approach using Doc table(s) is wrong from the very beginning.

added: Besides - Doc tables can be easily cleaned up from posted documents to eliminate DB size, in contrary to Ledger tables, which never loose data, even if “compressed” for closed periods. One more reason NOT to report from Doc tables.

11

Hi Jerome,

What is updating this field?

12

Hi Forum

I would like to Apologize for my wrong solution.

And i would like to thank Dave and Singleton they have guided me in correct path

Thanks

Marshal.J

13

No need to apologize, just clarifying that it wont work is great, because it doesn’t send the next member on a wild goose chase trying to make this work. I am glad you worked out yourself that it wont work.