I need to correlate sales to our manufacturing lines so I can calculate $/min of operation.
The Sales Invoice Line has all the data I need, <i>except</i> the Workcenter Code (which line produced the part). Here's my function:
=NL("Rows","Sales Invoice Line",+"Product Group Code","Product Group Code","<>@@","Salesperson Code",Support!$D$5,"Posting Date",Support!$D$3,"Link=","Sales Invoice Header","No.","=Document No.","Link=","Prod. Order Line","Sales Order No.","=Order No.","Workcenter Code",B9)
Sitting in the sheet, I get a return from Jet of a Product Group Code. When I run the report, though, it chugs forever and finally barfs. Do I need to link the Line Numbers together to get this to work?
Date
Votes
3 comments
-
Jet Reports Historic Posts Hello -
The "LINK=" feature is very powerful… and sometimes complex. Thus, it can take time to accomplish what you are after.
I put together a simplified example for my sample data.=NL("Rows","113 Sales Invoice Line","5712 Product Group Code","+5712 Product Group Code","<>@@","Link=","112 Sales Invoice Header","3 No.","=3 Document No.","Link=","5406 Prod. Order Line","2 Prod. Order No.","=44 Order No.","23 Bin Code","*")
I don't have your "Workcenter Code" nor a "Sales Order No." field in my "Prod. Order Line" table (so, I just used NAV's standard "Prod. Order No.").
Even with small data set (the SIL only contains 55K records while the SIH has 4K), the database has to work through a fair number of calculations to do the matching.
You didn't mention if you got any kind of error message. Are there any?
Another option you might want to consider (depending upon the amount of data you are working with) would be to just return the data from your Sales Invoice Line:=NL("Rows","Sales Invoice Line",,"+Product Group Code","<>@@","Salesperson Code",Support!$D$5,"Posting Date",Support!$D$3)
{note: I would copy the D3 and D5 cells from the "Support" sheet to the sheet containing your NL(Rows) function… Excel performs on-sheet references *much* faster than it does off-sheet references}
with NF() functions to retrieve the specific fields you want. And then use an NL("First") function to return get the Workcenter code from 'Prod. Order Line' table. Then you could use conditional hide to only show those records with a Workcenter code you want to deal with. While this option would return more records, it *might* be your faster option (again… it depends on the amount of data you are working with).
Just food for thought. -
Jet Reports Historic Posts Hi, HP.
Thanks for looking at it. When I run the report, it will eventually replicate (outer replicator) for each workcenter, but the product group cell shows #VALUE. When I click Debug in the Jet ribbon, nothing happens.
Thanks for the heads up on the options. I'll start putting them on the main report page.
I'm not sure what you're suggesting will work. We're a manufacturer that produces make-to-order and make-to-stock items. I need to run a report on the $/min produced by our production lines.
In order to do that, I need to filter out the stock production. I figure the most efficient way to do that is compare what was invoiced vs what was produced and only report on that production. The Sales Invoice Line knows total weight, total footage (both metrics we use) and $ amount. The production line doesn't know any of that, but it <i>does</i> know which workcenter did the job.
There aren't any shared fields between the two lines that are specific enough for my analysis, or I'd just use an NL("Filter") to reference the Prod. Order Line I need. I believe (maybe I'm wrong) that I need to nail down this "Link=" function, because I'll need to use it when I sum total weight, total footage, and total $ amount for the report. -
Jet Reports Historic Posts If you are getting #VALUE! messages, that would be a prime situation for contacting Jet Reports Technical Support (https://jetsupport.jetreports.com)
Using Essentials Debug feature should provide more information about any Essentials function displaying #VALUE!
Since that feature isn't working for you, that hints at the issue lying in an Excel calculation. Technical Support would be able to tell.
Please sign in to leave a comment.