Sorting by a grouped total.

Hi I am creating a report to show the amount of revenue for a particular type of customer. I have done this, so that it shows this on a weekly basis by linking 5 different Sales Invoice Header tables to a Customer table. I have grouped the totals using the grouping property. The problem I have now is that I cannot sort these by the total I have created. I left this until last thinking it would be relatively easy to do. Does anybody know what I can do here. Thanks, Craig

First, why so complicate ? Do you need to see the revenue per day ? The customer already has a field called “Sales (LCY)” which is a flowfield that can be used. This flowfield is based on a flowfilter field called “Date Filter” (beside others). Just run the customer table and set the “Date Filter” for the desired period. Then check the “Sales (LCY)” field for each customer. Next: Check the “Customer - Top 10 List” report. Maybe that one is doing what you want (including the sorting). At least you can get an idea on how to “sort” by a calculated value otherwise.

Thanks for the advice, I’ve been requested to make reports so the weekly figures can be compared, this is the only problem I’m really having otherwise like you said it is quite straight forward.

Craig, what sorting are you trying to achieve. As mentioned, it looks (from what you say) as though you are tring to solve a simple problem with a complex solution. Can you explain what your goal is for this report.

Hi David, I have created a global variable to display the total of a 5 week snapshot for the revenues for different customers. Now, all i want to do is sort the report according to this global variable that i have created. I have joined a customer table to 5 different sales invoice header tables to get the grouped totals filtered by the date on each table to return a figure for each week. I know this is probably the completely wrong way to go about doing this, but I’m trying to make the best of what little knowledge i know about Navision reporting. Cheers, Craig

By which of the five weeks do you want to sort ? First or Last ?

Sorry I am trying to sort by the overall total of the 5 week period, I’ve a created a global variable to calculate this. Thanks, Craig

So your report should look something like this: No. Name Week1 Week2 Week3 Week4 Week5 Total 10000 CustomerName 5,800 3,226 6,678 0 1,256 16,960 10001 Customer10001 358 4,280 837 538 2,330 8,343 10002 Customer10002 100 200 333 788 1,856 3,277 Did I get you right ?

are you trying to create report like Aged Accounts Receivable, i mean the date grouping . if so, you can look the code where that report generate the date. i hope it help

Exactly, that is what I have so far. I just need to sort this. Cheers, Craig Sorry, that is in reply to thomas, but no i haven’t looked at this report yet thank you.

You definitely should have a look at the report mentioned earlier: Customer - Top 10 List There you have the information already and also sorted. Instead of totaling the weekly values to get the total for the line, just add some more code in the INTEGER dataitem OnAfterGetRecord trigger to get the sub values for the weeks between (subs of the total). like: Customer.SETFILTER("Date Filter",Datefilter1); Customer.CALCFIELDS("Sales (LCY)"); SalesWeek1 := Customer."Sales (LCY)"; Customer.SETFILTER("Date Filter",Datefilter2); Customer.CALCFIELDS("Sales (LCY)"); SalesWeek2 := Customer."Sales (LCY)"; ... whereby the DateFilter1 represents a filter like 01.01.07…06.01.07, DateFilter2 represents a filter like 07.01.07…013.01.07,… and SalesWeek1,SalesWeek2, and so on will contain the weekly values. So all you need to do is create a copy of report 111 and change that.

Thanks for this Thomas, I’ve got this working just how I wanted it now.

Always welcome