Hi there.
I've managed to build myself an increasingly complicated report. Its a sales report based on Inv & Cred totals.
I've got an error about an 'Invalid Filter', and I've not been able to figure a workaround. I'm going to abstract around some of the detail to keep it simple
Firstly, to calculate sales:
J9: Nl("Sum","Sales Invoice Line","Amount",….,"Gen. Prod. Posting Group",C10,"Document No.",I10)
I10: Nl("Filter","Sales Invoice Header","No.",….,"Sell-to Customer No.", B10)
B10: NL("Filter","Customer","No.","Club","Platinum"…"Link=","Sales Invoice Header","Sell-to Customer No.","="No.",","Posting Date","..01/01/08"….)
C:10: Nl("Rows","{List of gen. prod. posting groups}")
Now you'll say it looks way overcomplicated - which it is. However the English logic is:
Sum of Sales by Year and by Gen. Prod. Posting Group, where the Customer has had a sales before 2008, and that Customer is currently a member of the Platinum 'Club'.
First question: Do I need to do something different with the filters? Double quotes, something like that?
Secondly, any ideas for re-gigging the filtering?
thanks, Mark
Date
Votes
3 comments
-
Jet Reports Historic Posts Hi Mark,
Let me see… this is about sales invoice documents rigth? First, I think I would use the Value entry table instead of SI Header and SI Line. The VE table seems to hold almost all the fields you use in your functions. As I'm doing this by heart (I'm supposed to have holiday) you may need to adjust a few little things – I’m not sure which amount field in VE you need to use.
NL("Sum","Value entry","Amount","Gen Prod Posting Group",C10,"Source type","Customer","Source No.",NL("Filter","Customer","No.","Club","Platinum","Date filter","..12/31/2008","Sales (LCY)","<>0"))
Source type and Source no refer to either vendor or customer.
The Sales in local currency field is a flow field which is affected by the date filter.
Does this make sense? And even more important: does it work for you? -
Jet Reports Historic Posts Thanks for the English logic as well. I believe that what you want to do could be accomplished as follows:
B11: =NL("Rows","Value Entry","Gen. Prod. Posting Group")
C11: =NL("Sum","Value Entry","Sales Amount (Actual)","Item Ledger Entry Type","Sale","Posting Date",C$8,"Gen. Prod. Posting Group",$B11,"Link=","Customer","Club","Platinum","No.","=Source No.")
Han's solution will probably work too and it's pretty darn good for being on holiday (do you never take vacation dude? :) ) but this one might be a litte more efficient since it doesn't need to rebuild the filter on customers each time it calculates. Attached is a little sample which uses customer posting group (so the sample will run in Cronus, but change that out and you should probably have what you need or at least a start. -
Jet Reports Historic Posts Thanks to both of you for your suggestions. I'll give them a try today and let you know.
cheers, Mark
Please sign in to leave a comment.