0

Multi-level grouping where records may be missing

I'm setting up a report with groupings in this order
Blanket Order Header (from Purchase Header, where Document Type = Blanket Order)
- Blanket Order Line (from Purchase Line, where Document Type = Blanket Order)
- Order Line (from Purchase Line, where Blanket Order No = the one from the header above and Blanket Order Line No = the one from the line above)
- Receipt Line (from Purch. Rcpt. Line where Order No & Order Line No = the ones from the order line above)
- Return Line (from Return Receipt Line where Document No & Line No = the ones from the order line above)

It then repeats for non-Blanket Orders (Order Header group in place of the 2 Blanket Order groups).

In some cases there is no Order Line (where the Blanket Order doesn't have any "child" orders).
In some cases there is no Receipt Line (where nothing has been received yet).
In many cases there is no Return Line (where nothing has been returned).

For some reason in my template, even when there isn't a record, I still get lines for Order Line, Receipt Line and Return Line - at least one for each Blanket Order Line.
Plus, for Receipt & Return lines, I often get errors about @@ being an invalid filter - this happens when there is no Order Line to link back to.
I suspect it's (at least partly) to do with the Rows= parameter of one of the statements, but I can't figure out a way to make this dynamic.
I see #VALUE! errors in the NL functions for Order, Receipt & Return lines when there are no Blanket Orders in the data source… which I think should just be returning nothing. I think I have the @@ stuff set up OK for blanks, but it appears not to be working.
What's wrong?

I'm attaching a version of the report for you to look at - all feedback appreciated :)
A couple of notes on it:
- Unfortunately Cronus doesn't have any Blanket Orders in it at all, so the attached won't show the exact symptoms described unless you've added one or more - although you still get the invalid filter error - at the Order Line level.
- I've got filters in there, but I've disabled them for the time being as I thought this might be part of the issue.

I'm using Jet 2012 R2 (12.5.13099.0) and NAV 2013 in case that affects things.

0 comments

Please sign in to leave a comment.