Could someone help me with the attached report (Book5.xlsx). I am writing it using the NL(Table) function and it is based on multiple tables including one custom table. I am using the Item table as the base table so that I am getting all transactions. I am trying to get the qty. to ship off the sales line table based on the order date from the sales header table. I believe I have the NL(Link) and NL(InclusiveLink) correct but for some reason the Order Date Filter is not working appropriately. I tried to also do a very simple report, eliminating all other tables - see Book14.xlsx, and the order date filter from the sales header is not filtering the sales line data for this report either. What am I doing incorrectly?
Also, I would like to create a pivot table off of the table report but every time I refresh the report, the Excel named range changes to another table name. How can I keep this static so my pivot table refreshes as well?
Thanks,
Joy
Date
Votes
2 comments
-
Jet Reports Historic Posts Hi,
So I looked at your simple report and the problem is that you are using InclusiveLink= instead of Link=. The point of InclusiveLink= is that it doesn't filter the base table by the linked table. So filters you set on the linked table will not affect the base table if you use InclusiveLink=. Just change InclusiveLink= to Link= and the filter should apply to the base table. This option is available in the Table Builder tool when you add a linked table. It says at the bottom "Include xxxx rows:" and the options are "Always" which will do InclusiveLink= and "Only when xxx found" which will do a Link=.
In terms of the pivot table losing the name, you need a "TableName=" filter which you have in your simple report (Book14) but not in your actual report (Book5). Just add the "TableName=","Some name" filter to the NL(Table) function in Book5 and then the named range will always be the name you specify so the pivot table will continue to work.
Does this work for you?
Regards,
Hughes -
Jet Reports Historic Posts Hi Hughes,
Thanks for the reply. Your suggested change to change to Link= works for the simple report but does not work for the complex report. I have done an example that is summing the qty. to ship lines from the sales line table based on the item table which should be filtering by the order date from the sales header (using link=) and I am getting incorrect results. The same as my complex report. Do you have any suggestions?
Please sign in to leave a comment.