Hello all,
I've attached a report that gives me terrible headaches…
For short, what this report should do is:
- list all the items
- recover the total quantity and average sale price from the last version of Sales Order Archive (it was a real challenge to sum up this values)
- sort the list by descending SUM(Amount) / item - I have no idea how to sort this… I tried to apply the sorting by Amount, but I don't know how to limit it by last version no. (at the moment I think it takes into consideration all the document from Sales Order Archive, no matter the version).
Any idea would be a great help.
Thanks,
Elena
P.S. - I work with NAV 2009 R2 and JetReports 2010 R2
Date
Votes
6 comments
-
Jet Reports Historic Posts This is a tough one…
I managed to get the rows from every last version from SalesLineArchive within a single formula:
=NL("Rows";"Sales Line Archive";;"Sell-to Customer No.";$F$18;"Document No.";NL("Filter";"Sales Header Archive";"No.";"Sell-to Customer No.";$F$18;"Version No.";"1");"Version No.";NL("Last";"Sales Header Archive";"Version No.";"Sell-to Customer No.";$F$18;"No.";NL("Filter";"Sales Header Archive";"No.";"Sell-to Customer No.";$F$18;"Version No.";"1");+"Version No.";"*";"Order Date";$F$9;"Currency Code";$F$10);"Type";$F$22;"No.";$F$13)
…and it works great.
Now, with the item lines (I changed the structure by adding only the item from the pricing list for each customer) I tried to put that formula as a sorting detail, but I guess I'm doing something wrong, since it won't evaluate…
=NL("Rows";"Sales Price";;"Sales Code";$F$18;"-=NL(""SUM"";""Sales Line Archive"";""Quantity"";""No."";NF(;""Item No."");""Sell-to Customer No."";"""&$F$18&""";""Currency Code"";"""&$F$10&""";""Document No."";NL(""Filter"";""Sales Header Archive"";""No."";""Sell-to Customer No."";"""&$F$18&""";""Version No."";"""&$F$8&""");""Version No."";NL(""Last"";""Sales Header Archive"";""Version No."";""Sell-to Customer No."";"""&$F$18&""";""No."";NL(""Filter"";""Sales Header Archive"";""No."";""Sell-to Customer No."";"""&$F$18&""";""Version No."";"""&$F$8&""");+""Version No."";"""&$F$20&""";""Order Date"";"""&$F$9&""";""Currency Code"";"""&$F$10&""");""Type"";"""&$F$22&""";""No."";"""&$F$13&""")";"*")
F10 = <>''
F8 = 1
F20 = *
F22 = Item
F13 = *
Any ideas, please?
Elena -
Jet Reports Historic Posts Well, I think (hope) I've made a step forward - I separated that huge formula into several shorter formulas (that I also tested individually and they work great) - I attached the updated report.
But when I call the cells for the sorting code, they just don't do their job… and it's frustrating because I don't now how to try it any more…
Thanks,
Elena -
Jet Reports Historic Posts Hi Elena,
It might help you to know what it is you're actually sorting by. I added a column to your report (column E) and took your sort by sum formula and converted it to a regular NL(Sum) formula. These are the values that your replicator should be sorted by in descending order. If the sort is not happening at all, then it could be that the syntax of the formula itself is wrong, but I suspect the problem will turn out to be that the sum formula is not precisely returning the values you want to sort by. I don't know how to modify the sum formula to make it correct, but the way I have it you can at least play around with the Sum formula until it looks like it is returning the values you want to sort by and then quote it and reference it from your NL(Rows) function. Does this help?
Regards,
Hughes -
Jet Reports Historic Posts Hi Hughes,
Tanks for your reply.
I already did that (converted the SUM formula on a regulat NL function) - column D and it works great (I compared the information with data from the database - I even added the information within the report for a quick control - the information is hidden within the report starting AF24 cell).
For short, the formula sums the quantities by item from the last version of a SalesArchive, for a certain customer.
I still hope that is a syntax problem that I just can't see…
Elena -
Jet Reports Historic Posts Still with the problem…
So I have
D46 = NL("SUM";"Sales Line Archive";"Quantity";"No.";H46;"Sell-to Customer No.";$K$18;"Currency Code";$K$10;"Type";$K$22;"Document No.";NL("Filter";"Sales Header Archive";"No.";"Sell-to Customer No.";$K$18;"Version No.";$K$8);"Version No.";NL("Last";"Sales Header Archive";"Version No.";"Sell-to Customer No.";$K$18;"No.";NL("Filter";"Sales Header Archive";"No.";"Sell-to Customer No.";$K$18;"Version No.";$K$8);+"Version No.";$K$20;"Order Date";$K$9;"Currency Code";$K$10))
This is a valid formula that returns the correct amount.
This is the amount that I have to do my sorting - so I tried to slip this intro my NL(Rows) formula… like this:
G46=NL("Rows";"Sales Price";;"Sales Code";$I$18;"-=NL(""SUM"";""Sales Line Archive"";""Quantity"";""No."";NF(;""Item No."");""Sell-to Customer No."";"""&$I$18&""";""Currency Code"";"""&$I$10&""";""Type"";"""&$I$22&""";""Document No."";NL(""Filter"";""Sales Header Archive"";""No."";""Sell-to Customer No."";"""&$I$18&""";""Version No."";"""&$I$8&""");""Version No."";NL(""Last"";""Sales Header Archive"";""Version No."";""Sell-to Customer No."";"""&$I$18&""";""No."";NL(""Filter"";""Sales Header Archive"";""No."";""Sell-to Customer No."";"""&$I$18&""";""Version No."";"""&$I$8&""");+""Version No."";"""&$I$20&""";""Order Date"";"""&$I$9&""";""Currency Code"";"""&$I$10&"""))";"*")
When I try to debug G46 I get the attached message…
Any ideas?
Thanks,
Elena -
Jet Reports Historic Posts SOLVED IT!!! :D
If you need a similar entangled situation, please find the right answer with the report attached.
Best regards,
Elena
Please sign in to leave a comment.