Hi there everybody.
I'm writing a product ranking report, showing the top 30 products based on sales quantity. The catch is I need to filter on a field that is only available in the Sales Invoice/Cr.Memo Header, and the report needs to be sorted from highest seller to lowest.
I was originally able to do this by using the Item table, filtering on Sales (Qty.) with a "-". This worked great, however did not allow me to filter on the invoice.crmemo header.
Any ideas how I might do this?
Date
Votes
11 comments
-
Jet Reports Historic Posts Hi Mark,
Your question made me think of a nice example that comes with the installation of Jet Reports. I've attached it to my posting - it's a great example on how to sort Table A by a Sum from Table B. -
Jet Reports Historic Posts Ah, that does the job nicely. Thank you very much.
I presume it would add some overhead to the report, or does Jet cache the figure given its essentially the same formula? -
Jet Reports Historic Posts Hello,
I am also doing a sort using the nl function. I am looking at the report that you posted and am not sure on the formula. In the report that you posted there was a 17 in front of the G/L. What does this represent? This is the formula in the cell that I would like to sort.
-NL("Sum","G/L Entry","Amount","Name","@@"&$D10,"Global Dimension 1 Code","110","G/L Account No.","40000..49999","company=","PES Civil Services LP","Posting Date",$E$5))
So in the Row's cell what all do I add in? Do I go
=NL("Rows","G/L Entry","Name","company=","PES Civil Services LP","Global Dimension 1 Code","110","-NL("Sum","G/L Entry","Amount","Name","@@"&$D10,"Global Dimension 1 Code","110","G/L Account No.","40000..49999","company=","PES Civil Services LP","Posting Date",$E$5))","*")
Help!!
Thanks, Kitrina -
Jet Reports Historic Posts To answer your initial question, the 17 in front is, I believe, an alternative way of specifying a table. You will find that 17 is the Object ID for the G/L Entry table.
From what I remember, the sort is basically the same formula as the sum formula but double quotes to escape them within the existing NL. -
Jet Reports Historic Posts I tried the formula and I keep getting an input error. This is my formula below.
=NL("Rows","G/L Entry","Name","company=","PES Civil Services LP","Global Dimension 1 Code","110","-NL(""Sum"",""17 G/L Entry"",""Amount"",""Name"",""@""&$D10,""Global Dimension 1 Code"",""110"",""G/L Account No."",""40000..49999"",""company="",""PES Civil Services LP"",""Posting Date"",$E$5))","*")
I tried it with and without the 17. In the report it also uses the NF function in regards to the G/L account. Would i have to do that here somewhere?
Thanks,
kitrina -
Jet Reports Historic Posts I was able to get your formula to work by placing an equals sign between the '-' and 'nl'. i.e.
"-=NL("…
instead of"-NL("…
Can you give some more details re your second question? -
Jet Reports Historic Posts In regards to NF Function it is in relation to the jet report that was posted earlier on the Post. The formula there was:
=NL("rows","G/L Entry","G/L Account No.","-=NL(""sum"",""17 G/L Entry"",""Amount"",""G/L Account No."",NF(,""G/L Account No.""),""Posting Date"","""&$D$6&""")","*","Posting Date",$D$6)
I did enter the = in my formula and it gave me an error message that says:
The formula can not be used with NL("Rows"), NL("Columns"), or NL("Sheets"). Possible problems include that the formula may be too long or it may contain both nested functions and named ranges (you can't have both).
So I changed my formula so that they all contain named ranges and I am still getting this message. So I am thinking that the formula is too long and I will have to go a different route (just not sure which route yet). This is the updated formula:
=NL("Rows","G/L Entry","Name","company=",$C$4,"Global Dimension 1 Code",$C$2,"-=IF($D12="","",-NL(""Sum"",""17 G/L Entry"",""Amount"",""Name"",""@@""&$D12,""Global Dimension 1 Code"",$C$2,""G/L Account No."",$C$3,""company="",$C$4,""Posting Date"",$E$7))","*")
The error message could also be because of the IF statement (not sure). I did take the IF statement out and I received the same error message. -
Jet Reports Historic Posts I finally have been able to get the coding short enough that it will except it with no errors. However the formula is not working properly. I get all my values, it just isn't sorted. I have tried the formula with and without the IF statement function and both formulas return the same values (correct) just not sorted. This is my formula:
=NL("Rows","Customer","Name","-=IF($D12="","",-NL(""Sum"",""G/L Entry"",""Amount"",""Name"",""@@""&$D12,""Global Dimension 1 Code"",$C$2,""G/L Account No."",$C$3,""company="",$C$4,""Posting Date"",$E$7))","*")
Any help would be great. Thanks. -
Jet Reports Historic Posts Hi there. I see what you mean about not sorting. I've finally got back to the one I was working on, and its not sorting so well (at all) either.
-
Jet Reports Historic Posts Ok, try this.
Around the posting date, put 3 " characters with &:
From:""Posting Date"",$E$7)
to""Posting Date"","""&$E$7&""")
That worked for me. -
Jet Reports Historic Posts Ok, here is the formula that we finally used.
=NL("Rows","Customer","Name","-=NL(""Sum"",""17 GLE"",""Amount"",""Source Type"",""Customer"",""Source No."",NF(,""No.""),""23 GD1"","""&$C$2&""",""3 GLAccNo"","""&$C$3&""",""company="","""&$C$4&""",""Posting Date"","""&$E$7&""")","*","Company=",$C$4)
In Jet reports, when working with Navision, you can specify a table or field by it's number instead of it's name. This became necessary in this formula because the formula was too long (256 MAX). In Jet Reports, if it finds a number at the beginning of a table or field name, then it uses that number and ignores everything after it. "17 GLE" and "17" and "17 G/L Entry" all mean the same thing to Jet Reports.
Please sign in to leave a comment.