Hi
I have been struggling for some time now to get my head around this one….I have a report that I have developed which pulls in warehouse entry lines with various fields along with bin codes and sum of warehouse entry quantity. My issue is this. My report is outputting all lines including those that sum up to '0' as I have no if or sumif statement. What I need to do is output warehouse entry lines with the information based on only those lines which have a sum qty of >'0'. I have tried endlessly using sumif and if statements but fail every time. Below is the syntax I am currently using:
=(NL("Sum","Warehouse Entry","Quantity","Location Code",$C$2,"Item No.","@@"&$D6,"Bin Code","@@"&$H6))
So I need to do something to only show >0 somehow.
Any help would be gratefully appreciated. :roll:
Date
Votes
6 comments
-
Jet Reports Historic Posts The general syntax would be:
=NL("Rows","table_name",,"=NL(""Sum"",""table_name"",""field_name"",""key_field"",NF(,""key_field""))","NUMBER&>0")
I'll assume you are using Dynamics NAV as a data source. Since my NAV database does not have warehouse data, let's look at a customer-based example.
This is similar to the "Sort by Sum" question discussed here –> http://community.jetreports.com/viewtopic.php?f=23&t=2109&p=13237
Let's suppose that we want all the customer numbers from the Cust. Ledger Entry table where the Amounts for all records for that customer add up to at least one million:
=NL("Rows","21 Cust. Ledger Entry","3 Customer No.","=NL(""sum"",""21 Cust. Ledger Entry"",""13 Amount"",""3 Customer No."",NF(,""Customer No.""))","NUMBER&>1000000")
I hope that helps get you pointed in the right direction. -
Jet Reports Historic Posts Hi HPDeskJet
Thank you very much for the prompt reply and suggested syntax.
I tried using the following syntax but recieve no results:
=NL("Rows","Warehouse Entry","Bin Code","Location Code","CHINA","=NL(""Sum"", ""Warehouse Entry"", ""Quantity"",""Bin Code"",NF(,""Bin Code""))","Quantity&>0")
I think that I need to be a bit more concise with my description here so I will try below:
I have in cell $C$2 location=CHINA from Warehouse Entry table
In the report body I have the following in cell D6:
=NL("Rows","Warehouse Entry","Item No.","Location Code",$C$2,"Bin Code","<>SHIP&<>BUNDLE&<>MIA&<>NDI SAMPLE&<>RECPT&<>SCOTTS&<>WEBSITE&<>MICROP&<>TROJAN")
I then have in cell E2:
=NL("Rows","Warehouse Entry","Bin Code","Item No.","@@"&$D6,"Location Code",$C$2,"Quantity",">0","Bin Code","<>SHIP&<>BUNDLE&<>MIA&<>NDI SAMPLE&<>RECPT&<>SCOTTS&<>WEBSITE&<>MICROP&<>TROJAN")
Finally in cell F2 I have the following:
=(NL("Sum","Warehouse Entry","Quantity","Location Code",$C$2,"Item No.","@@"&$D6,"Bin Code","@@"&$H6))
This all works fine but is retrieving all rows including those that sum to '0' from the table
What I need to do is surpress the 0's if this is possible?
The additional issue here is that this location only has one bin. In SQL terms what I am attempting to get to is to show the 'Item No', 'Bin Code',sum of 'Quantity' where sum 'Quatity' > '0' -
Jet Reports Historic Posts Hi Rob -
The syntax of your first function is a little off…
Try this:=NL("Rows","Warehouse Entry","Bin Code","Location Code","CHINA","=NL(""Sum"", ""Warehouse Entry"", ""Quantity"",""Bin Code"",NF(,""Bin Code""))","NUMBER&>0")
The "NUMBER&" syntax, forces the comparison value (in this case, zero) to be treated as numeric rather than text. Since we are dealing with zero, you could try leaving it out entirely:=NL("Rows","Warehouse Entry","Bin Code","Location Code","CHINA","=NL(""Sum"", ""Warehouse Entry"", ""Quantity"",""Bin Code"",NF(,""Bin Code""))",">0") -
Reinder Wolting I am trying to get this to work on a datawarehouse aggregated table in Jet Enterprise, but it keeps telling me that "=NL(""Sum"", ""Warehouse Entry"", ""Quantity"",""Bin Code"",NF(,""Bin Code""))" is not a valid field name.
Is this only possible on NAV databases?
-
Harry Lewis Hello -
You are correct, that technique (the same as applied in a "Sort by Sum" situation) is for use with Dynamics NAV databases.
You should be able to apply the Sort by Sum technique for non-NAV databases.
-
Reinder Wolting OK, thanks for the link.
I will try it from there.
Please sign in to leave a comment.