I have this formula:=IF($G23="";0;NL("sum";"Prod. Order Capacity Need";"Allocated Time";"Type";"Machine Center";"No.";G23;"Date";=NL("Datefilter";;"..12.08.12");"Status";"Released";"Starting Date-Time";"01.01.1900..";"Allocated Time";"<>0"))G23 = =NF($F23;"No.")F23 = =NL("Rows";"Machine Center";)
The problems is that this gives me prod.ordre routing lines with all statuses(?) . How can I filter out all operations with status "Finished"?
Date
Votes
9 comments
-
Jet Reports Historic Posts Hmmm, are you saying you only want to see the rows from the Machine Center table where the Status on the Prod. Order Capacity Need table is "Released"? If that is the case, you could change your NL(Rows) formula to this:
=NL("Rows";"Machine Center";;"Link=";"Prod. Order Capacity Need";"Machine Center";"=No.";"Status";"Released")
Of course you might want to add the rest of your filters from Prod. Order Capacity Need to that formula as well, depending on what you want. Does that help?
Regards,
Hughes -
Jet Reports Historic Posts Hi and thank you for your quick reply!
I would like to:
List all Machine centers (rows)
On each line I would like to sum "Allocated Time" for all prod.orders with status "Released" but ONLY for the operations with status NOT "Finished".
Understandable? -
Jet Reports Historic Posts Aaah I think I understand you more now. What table is the operation status in? You can probably use Link= from your NL(Sum) formula to link to that table and filter for a status of <>Finished. We just need to know what table that data is in and what field(s) connect that table and the Prod. Order Capacity Need table.
Regards,
Hughes -
Jet Reports Historic Posts All operations are in the table "Prod. Order Routing Line".
Filter: The field "Status" must NOT be "Finished".
"Prod. Order No." AND "Operation No." in table "Prod. Order Routing Line" must match the same fields in "Prod. Order Capacity Need". -
Jet Reports Historic Posts So I think then you could do something like this:
=IF($G23="";0;NL("sum";"Prod. Order Capacity Need";"Allocated Time";"Type";"Machine Center";"No.";G23;"Date";=NL("Datefilter";;"..12.08.12");"Status";"Released";"Starting Date-Time";"01.01.1900..";"Allocated Time";"<>0";"Link=";"Prod. Order Routing Line";"Prod. Order No.";"=Prod. Order No.";"Operation No.";"=Operation No.";"Status";"<>Finished"))
Looks like we just barely fit this into the 10 allowed filters so that is good. If you needed more filters, we could always change the Link= part to be an NL(Link) function, but for now it seems to fit in 10 filters. Does this work for you?
Regards,
Hughes -
Jet Reports Historic Posts Hi!
No, sorry. That didn't to the job. I got the same result as with the old filter. It looks like the Link-part doesn't work.
I've added two attachements.
"Prod. Order Capacity Need" shows all lines both the old and the new function returns. As you can see the line with Prod. Order P06309 and operation 120 is included.
But, if you look at "Prod. Order Routing Line" you will see that that operation has Status = Finished. So, the line should not be included.
Hope you can help me some more!
Regards
Andreas -
Jet Reports Historic Posts Hi Andreas,
So let me try to clarify what's going on. What we've been modifying is an NL(Sum) function. The NL(Sum) function only returns a sum; it is not responsible for creating rows. Therefore, if you have a row in your report that's not supposed to be there, we need to be looking at the NL(Rows) function, not the NL(Sum) function. Are you saying that your sum is wrong and you think it contains this value for Operation No. 120? Or are you saying that you're seeing rows in your report that you don't want to see?
Regards,
Hughes -
Jet Reports Historic Posts Hi again!
Sorry for the confusion.
The function you have been helping me with is a sum-function, and it should be. The attached rows was just to show what info the filters did (worng) and should (correct) show (the function itself would sum the "Allocated Time"-field in these rows.
I hope this clearifies what I want.
Regards
Andreas -
Jet Reports Historic Posts I find that when figuring out these sorts of situations, it's easiest to take your sum function and turn it into an NL(Rows) and add some NF functions. If the sum is already part of an NL(Rows) then just pull it out on its own sheet and hardcode the filters for whatever row you are checking. Then you can see exactly what values are being included in the sum. For the Link you can create an NL(First) formula on the other table and just filter by the fields you pull out of the first table with NF functions. Does this help show you what's going on at all?
Regards,
Hughes
Please sign in to leave a comment.