I have created a NL (Table) using the Table Builder. Now that it is built I want to add a column within the table using a NL (First) formula to wich would include a filter from a NL (Table) field and a couple other filters. I have attempted to do this but have been unsuccessful to this point. Is this possible with Jet Essentials 2011? If it is possible what might I need to add to the Links, Headers or Fields: columns which were generated by the Table Builder?
Thanks,
Eric
Date
Votes
3 comments
-
Jet Reports Historic Posts Hi Eric,
NL(First) and NL(Table) don't usually mix. You would want to use NL(First) with NL(Rows), not really with NL(Table). Why exactly do you want to use NL(First)? Is this a flow field and you want to add some extra flow filters to it? Is it a field from a different table than the original table?
Regards,
Hughes -
Jet Reports Historic Posts My purpose for creating it as NL(Table) instead of a NL(Rows) is to take advantage of the pivot table function and to speed up the report. Let me explain the purpose of the report and then my difficulties in creating the NL(Table).
I am attempting to analyze how many of each item we have shipped to a range of states. To analyze the most accurate data I want to compare the monthly sales average for the last quarter and the last year. I will also use the max excel function to determine each item's largest quantity on a single sales line. All this information will tell me how much of a given item I need in a location to satisfy our shipping need to the range of states. Since it is a pivot table I can quickly change any of the filters and update the report without refreshing and changing filters.
The problem comes in when I do a NL(LinkSum) for the sales quantity I lose the state filter so it shows all states. My original post was essentially to determine if I could insert a NL(first), NL(last), NL(sum) or other formula into the NL(Table) so I could utilize the dynamic pivot functionality.
If it is not possible to add non-table NL functions into a table, is it possible to utilize the dynamic pivot functionality using NL(Rows)?
I have attached the (work in progress) report to show where I am having problems. Field Q28 is an example of a NL(LinkSum) formula for the Item table which cannot not filter out the Ship-to States. I could have done the same thing using the Sales Line but again there is not a Ship-to State filter available for that table either.
Thanks for the help.
Eric -
Jet Reports Historic Posts Hi Eric,
So the Sales (Qty.) field on the Item table in NAV is a flow field which sums the Invoiced Quantity field from the Value Entry table where the Item Ledger Entry Type is Sale and with various link filters such as Date Filter, etc. I wonder if instead of linking to the Item table from the Sales Invoice Line in your report if you could do a nested link from the Sales Invoice Header (where your state filter is) to the Value Entry and get a LinkSum of the Invoiced Quantity field directly. Then, assuming you are linking the tables correctly I would think you could get the value you want. Does that help?
Regards,
Hughes
Please sign in to leave a comment.