In attached report are two sheets that should look alike after reporting…
But apparently they don't :cry:
What I am trying to do here is to get the total amount of resources used, specified by unit, for invoices that are filtered by certain criteria (4 in total).
Because those criteria are not available in the res. ledger entry table, I am looking into the sales invoice header to generate a list of document no.'s that can be used as a filter for the res. ledger entry.
The sheet called 'No Link=' first gets all the sales invoices that match the criteria and at the bottom of every list I generate a total based on the range of document no.'s. The results are what I expect them to be.
The other sheet ('Link=') will try to do the same, but wants to avoid getting all the invoices into excel before generating a total from the res. ledger enty.
So I used the Link= option. But I must do something wrong, because the results are wrong.
I would have been satisfied with the first implementation, but the amount of lines will be growing rapidly over the year, so I expect the report to become terribly slow over time.
Besides that, I want to share this through mail and therefore I need the report to be as small as possible.
What am I doing wrong when using the link= option?
Or maybe it was not designed to be working this way at all?
Any suggestion will be greatly appreciated.
This attachment is made on the standard Cronus database, so it should be usable for anyone.
I am using Jet 9.1.10018.0 and Excel 2007
rmw
PS Just tried build 9.2.10041.0, but that doesn't make any difference
Date
Votes
3 comments
-
Jet Reports Historic Posts Official comment Hughes,
In this formula you have your filter fields and filters in the wrong places because you have an extra "No." specified after the "=Document No.".
With so many parameters it is easy to overlook the obvious :oops:The second problem I see is that after your "Link=" filter you are using "@@" before the cell reference to the NL(Link) formula
After removing the "@@" Jet tells me that it doesn't know the field of the second filter.
By putting the Link= in the last filter, it all [size=150]works like a charm![/size]
Is it mandatory to put the link= set last in the list of filters?
Thanks again for solving this!
rmw -
Jet Reports Historic Posts Hi rmw,
I can see several things wrong with your link= sheet. First, the NL(Link) formulas all look something like this:
=NL("Link","Sales Invoice Header",,"No.","=Document No.","No.",$D$6,$D7,$E$6,$E7,$F$6,$F7)
In this formula you have your filter fields and filters in the wrong places because you have an extra "No." specified after the "=Document No.". It should look like this:
=NL("Link","Sales Invoice Header",,"No.","=Document No.",$D$6,$D7,$E$6,$E7,$F$6,$F7)
It might be helpful to you to use the Jfx to view these formulas since you can then easily see that you have your filter field/filter parameters misaligned.
The second problem I see is that after your "Link=" filter you are using "@@" before the cell reference to the NL(Link) formula. This will not work since the purpose of @@ is to make the filter a literal filter and the filter created by the NL(Link) formula will not be interpreted correctly this way.
I believe that if you fix these 2 problems, your Link= sheet should work. Is this the case?
Regards,
Hughes -
Jet Reports Historic Posts rmw,
Ah I missed that detail. You are correct, Link= needs to be last in your list of filters because any filter that comes after a Link= is applied to the table you are linking to rather than the main table of the function. I'm glad all is well now!
Regards,
Hughes
Please sign in to leave a comment.