Hi all,
I trying to carry out a lookup on the G/L entry table. Basically it is looking at vendors with a few filters applied and totalling the amount field. What I would like to do is sort it in descending order on the amount sum.
What I've got at the moment (which works in design but fails at runtime):=NL("Rows","G/L Entry","Source No.",0,$B$3,"Shortcut Dimension 3 Code",$C$3,"Posting Date",$D$3,"Gen. Posting Type","Purchase","Source Type","Vendor","-=NL(""Sum"",""G/L Entry"",""Amount"",0,$B$3,""Shortcut Dimension 3 Code"",$C$3,""Posting Date"",$D$3,""Gen. Posting Type"",""Purchase"",""Source Type"",""Vendor"",""Source No."",NF(""Source No.""))","*")
I would be grateful if anybody could let me know where I'm going wrong!
Regards,
Q
Date
Votes
5 comments
-
Jet Reports Historic Posts Official comment Q,
Aaah, that's the issue. You must be using Jet Reports v7 or earlier. You can solve this a couple different ways. I would recommend you upgrade Jet Reports to the latest version. This would make the problem go away because Jet Reports v7 and earlier have a limit to the number of characters in the replicator formula that was lifted in Jet Reports 2009.
The other way you could solve the problem, assuming you can't upgrade, is to pull the sort by sum formula out and put it into its own cell. So basically you'll have a cell that looks like this:="-=NL(""Sum"",""G/L Entry"",""Amount"",0,$B$3,""Shortcut Dimension 3 Code"",$C$3,""Posting Date"",$D$3,""Gen. Posting Type"",""Purchase"",""Source Type"",""Vendor"",""Source No."",NF(""Source No.""))"
Then you can reference that cell in your replicator formula. In Jet Reports v7 and earlier, replicator formulas have to be 255 characters or less. Does one of these solutions work for you?
Regards,
Hughes -
Jet Reports Historic Posts Hi,
It looks to me like your nested NF formula is missing a comma. It should be:
NF(,""Source No."")
Notice the comma after the open parenthesis. Does this fix your problem?
Regards,
Hughes -
Jet Reports Historic Posts Hi Hughes,
Thanks for your reply.
I'm afraid it's still throwing up an error.
Regards,
Q -
Jet Reports Historic Posts Hughes,
Brilliant - does the job nicely! Many thanks.
I'm looking to upgrade to Jet 2010 R2. We run it off Citrix so I've passed it on to one of our guys to check out.
Thanks again for your help - much appreciated.
Regards,
Neil -
Jet Reports Historic Posts Neil,
Hey that's great! Glad it is working for you. If you're thinking about upgrading, you might as well upgrade to version 2011 since that's the latest. ;-)
Regards,
Hughes
Please sign in to leave a comment.