Simple reporting problem with grouping?

katko

I've got a simple report I'm working on. It splits orders based Date, then Ship-To Route Code. But the problem is it then seems to split again on order number.



You'll note at the bottom that it has Ship-To Route TN-EAST twice, for two separate orders.

I want all of the values that have the same Date and Ship-to Route Code, summed all together with a total at the end. And I can't figure out what's causing it to split on the order number.






I've read everything I can find on classic reports but I can't find a similar situation.

Thank you for your time.

[edit]

A couple of things I forgot to mention. The pages of data continue after the one shown. And Ship-To Route Code is located in Sales Header, not Sales Line, which I think is the crux of this problem.

As I understand it now, it's pulling every sales header that meets the filtering requirements, and then iterates through the Sales Lines. But what I should be doing is having it iterate through Sales Lines, and then somehow get the associated Ship-To Route Code for those lines so that it's not splitting every Sales Header entry.

neilgf

Hi

As the report details are coming from the sales line detail which is linked to the sales header via document number you are getting a sort by document number.
You would be better to use the sales line as the primary data item and set the code to be by location as this will be on the sales line. Your only issue is then the date sort but again the sales lines have quite a few dates you could use.

Hope this helps.

Neil

katko

They don't appear to be using "location code" in Sales Line. Is there still a way to reference "Sales Header"."Ship-To Route Code" from within a Sales Line item?

I need to sort by Date, then Ship-To Route code, and total the quantities for each permutation of those. This seems like such a simple problem with such a confusing solution.

I'm very new to NAV. I've been reading everything I can on reports but none of them really touch on this kind of issue.

Is there some sort of C/AL code magic that I can do to populate the field through some sort of reference lookup (ala No.=FIELD(Document No.) ) before the grouping is processed?

Thank you for your time, I really appreciate it.

[edit]

Alternatively, is there a way to move the data from Sales Header, to Sales Line so that the data can then be accessed and used for sorting?

neilgf

Hi!
That is a shame re location code on the line as this would have made the report quite simple. Yes you can use C/AL code to fetch the location from the header but as this does exist on the sales line and only in memory the sorting will prove difficult.
Which version of Nav are you using?
Neil

katko

The client is using 2009 NAV, with only classic reports / sections view.

neilgf

Hi!

Well the obvious route is to add a flowfield (lookup) to the sales line table to lookup the location code from the sales header. Trouble is you cannot use a flowfield in the sort for the report so this would not help. Another option is to collect the data in an array and then sort after data collection. Or collect data and save to a new table (bespoke), make sure the new table has a sort key you need, then run the section layout off the new table.

Or create a routine to update the sales line location code from the sales header and then run the report off sales line. As you are using the report against unposted tables this is doable though you would need to add in code to possibly change sales header status pre change.

My only concern for the latter would be why is the client not using location codes at line level? Is there a reason? If there is you should not write code to update the line.

Neil