Can you post your report? It doesn't sound that difficult, but maybe I'm missing something?
Date
Votes
16 comments
-
Jet Reports Historic Posts Hi all,
Nav 2009 R2 & Jet Essentials
I am looing at a report to show expected routing capacity against a Sales Order or a Sales Quote that does not already have a Production Order raised, as when they are raised you can use Prod. Order Capacity Need Table.
So, I have used Sales Lines linked to Sales Header for the Line Item details & customer details.
Then I have linked Sales Lines Table to Item Table on Item no & then linked Item Table to Routing Line Table on Routing No.
The reason for this, is an item could have a generic routing no. as when I did not use the Item table, if a Sales Line Item does not have a routing No. the same, it does not report.
The problem I have, is in the routing No. it may have 4 lines of operations, but the report only picks the first operation so is not a true total time
So, first of all, is this the best way of trying to find this information, correct tables etc
If so, how do I get the report to show all the routing lines so that I can total them up per Sales Line item?
Thanks in advance. -
Jet Reports Historic Posts No problem, here you go, to note I have used Table builder as that is the part of Essentials that I know best, although may not be the best for this report?
-
Jet Reports Historic Posts Hi,
Thanks, although I'm afraid I've never used the table builder!
If I was doing it, I'd have a cell with the formula of something like NL("Sum","Routing Line","=NP(,""Setup Time"")+NP(,""Wait Time"")+NP(,""Routing Time"")+NP(,""Move Time"")","Item No.",CellWithItemNo.)
Note that isn't tested as I don't have any manufacturing data on my system. -
Jet Reports Historic Posts Ok thanks,
It is not the actual calculation that is the problem, it is more the table joins I believe, as I see it, the sales Line will have an Item, this Item then will have a routing no. code & finally in the routing Line Table, this routing no. actually has 4 lines for different Work Centres (1-4), but so far, my report only picks up the first line (work centre with its times), not all the lines from all the work centres, which is the join I believe, as I want all the lines from the routing Line table, where the Routing no. code = routing no. code of the Item that equals the Item no of the sales line.
Does this explain better, or does your code take this into account before I start looking at it?
Thanks -
Jet Reports Historic Posts Hi,
Assuming your data in te routing line table looks something like:
RoutingLine1 item1 WorkCentre1 WaitTime MoveTIme
RoutingLine2 Item1 WorkCentre2 WaitTime MoveTime
RoutingLine3 Item1 WorkCentre3 WaitTime MoveTime
RoutingLine4 Item1 WorkCentre4 WaitTime MoveTime
RoutingLine5 item2 WorkCentre1 WaitTime MoveTIme
RoutingLine6 Item2 WorkCentre2 WaitTime MoveTime
RoutingLine7 Item3 WorkCentre3 WaitTime MoveTime
RoutingLine8 Item4 WorkCentre4 WaitTime MoveTime
Then the code suggested will add up all the various wait times for all the lines with the item no. on it, regardless of workcentre or anything else.
Someone who knows more about TableBuilder might be able to be more helpful to encorporate the code into your existing report. -
Jet Reports Historic Posts I don't think the Table Builder would be your best option because of how it works. You are seeing this when you mentioned "The problem I have, is in the routing No. it may have 4 lines of operations, but the report only picks the first operation so is not a true total time"
The Table builder associates one record from one table to one record in another. If you have a 1 to many relationship, the table builder won't understand that and will only grab the first of the many records. You could reverse this by switching it around and starting at the most detailed level (the many) and link to the more refined level (the one). For example, if I were to start at the Sales Invoice Header and link to my Sales Invoice Line to pull line information, each document in the header will be listed once but there may be many lines to that document. In this instance, it would only grab the first line associated with that document.
However, if I start at the Sales Invoice Line and link to the Sales Invoice Header, I could pull each line and then associated Header information regarding it.
See the attached for an example of this. In the header to line tab, you can see that I only have each document number once whereas in the line to header tab, the same document number shows up multiple times, once for each instance of a line on that document.
When using the Table Builder, it is very important to choose the order of table selection as it has very different results. -
Jet Reports Historic Posts I am aware of the one to one & one to many types of relationships as I have spent many years of working with Crystal Reports, with crystal you could easily change the join types, I haven't found this in Jet Essentials & hence my post.
From your example, i agree with your logic, however that has not helped me in this case, to explain.
I have two tables with multiple lines/records that need to be reported on, but no direct link between these two tables.
At this moment I do not have to use the Sales Header Table.
So I want every Record from the Sales Line where Type equals Item that also has a routing in the routing line Table & then every record of the routing from the routing line table to total the times per item.
But the Sales Line Table is not linked directly to the routing Line Table, so I have to use the Item table to link the Sales Line to the Routing Line Tables via the Routing No. field
So if I start with the Sales Line Table, I only get the first record from the Routing Line Table.
If I start from the Routing table, for some reason, I do not get every Sales Line, an example I am looking at, has 4 items with Routings, but the report is only bring through 3 of them (although it is the correct time total values otherwise) so I need to work out why there is a missing Item when starting from the Routing Lin Table.
I also added a Flow Field to Sales Line Table of the Routing No., but in Jet you cant ink tables via a flowfield so this did not help.
As said, there may be an easier way of doing this, but I am not familiar with the other ways or creating a Jet Report at the moment, I usually only use the Table Builder, however I am reviewing the Jet videos to see of they can help me.
I will keep on reviewing for now, but any help appreciated. -
Jet Reports Historic Posts To add, I have done this in Table Builder as that is what I know, if it is better to do this from Scratch entering Functions manually, or using Report Wizard, report builder or Browser then please advise.
Thanks -
Jet Reports Historic Posts Hi,
This would not be a difficult report to write by entering functions manually. As a guide, I would use:
NL("Rows") to get all the relevant sales lines.I would use NF to get the item no. from that sales line.
I would then use the NL sum function I already gave to add up the routing lines for that item.
If you need more specific help, please feel free to PM me. -
Jet Reports Historic Posts Thanks TeresaRoberts, i need to learn how to manually link tables & use the functionality by the looks of it, are there any specific guides or resources for this to help me in this topic?
Or an example to show me how to link the Sales Line to the Item to the Routing Line tables?
I'll try to learn this as i go as i am starting to understand what i need to do. -
Jet Reports Historic Posts http://help.jetreports.com/11.0/Essentials/
I find it really helpful, and there are tutorials to go with it.
To start with what you want is an NL("Rows","Sales Line",,Field1,Filter1 etc.)
Then NF(CellWithNL,"item No."), usually one cell to the right, to give you the item no. from that line. You'll probably want a whole bunch of those NF ones to give you custoemr information etc.
Then to get your routing times, something like
NL("Sum","Routing Line","=NP(,""Setup Time"")+NP(,""Wait Time"")+NP(,""Routing Time"")+NP(,""Move Time"")","Item No.",CellWithItemNo) -
Jet Reports Historic Posts I have the first part working as expected:-
=NL("Rows=3","Sales Line",,"Type","item","Document Type","Quote|Order")
I also have the fields from the Sale Line table that I require using the formulas you mentioned i.e.
=NF($C4,"Document No.")
But how do I get the Routing Line Table information in to the report as it does not link directly from Routing Line Table to Sales Line Table, you have to use Item Table as a middle link even though there are no fields from the Item table that I need to report on.
This is where the report falls over for me, as I would expect that I have to tell the report to link Sales Line Table to Item Table on no. = no.
Then I would have to link Item Table to Routing Line Table on Routing No. = Routing No.
So how to you manually add these lines?
Would I also add a =NL(Rows line for each Table? as per the Sales Line?
Then in the =NL("Sum","Routing Line","=NP(,""Setup Time"")+NP(,""Wait Time"")+NP(,""Routing Time"")+NP(,""Move Time"")","Item No.",$f4)
I cant be far away, it is just the linking of the tables bit that is not right. -
Jet Reports Historic Posts You're pretty much there, do an NL("Rows") (or "First" if there should only be one) to link each table together. You can hide the columns you don't need by putting hide in row 1 above the columns you don't want to see (put fit in the ones you want to automatically adjust). Have a bash at it as you know your data, and you can PM me the file to check if it doesn't work for you.
-
Jet Reports Historic Posts Just like to publically thank TeresaRoberts for her patient help with this, i managed to get what I wanted after some very useful training.
Thanks -
Jet Reports Historic Posts Just a thought…What about the Planning Routing Line table? I believe it is the same as the Prod. Order Routing Line table only for planned production orders. This might result in a more realistic estimate for completing the work since MRP is considering / scheduling the operations while considering other orders as well.
Thanks, -
Jet Reports Historic Posts Hi davecroll, I do not know enough about the Planning Routing Line table, but as you said it seems to be for the order planning worksheet which we don't use currently, what I want was for all orders which includes unplanned Sales Orders & Quotes to get an idea of what capacity we might require. I have done another report based on the Prod. Order Capacity Need table as that brings over planned information.
Thanks
Please sign in to leave a comment.