I will try to explain this as best as possible…
I built a report that starts with an =NL("Sheets","Salesperson/Purchaser","Code","Code",$C$5) "$C$5 is the =NL("EVAL","=option!$F$8")"
from there added an =NL("rows=6","Customer",,"No.",$C$3,"Salesperson Code",$C$8)
from there added an =NL("Rows=3","Sales Invoice Header",,"Bill-to Customer No.","@@"&$I10,"Order Date",$C$2)
final was an =NL("Rows=2","Sales Invoice Line",,"Document No.","@@"&$D11,"No.","<>23100","Gen. Prod. Posting Group",$C$6) (if i dont use the @@ i error)
it produces this
~~~~~~~~~~~~~~~~~~~
Start Date 4/1/2009
End Date 4/30/2009
CSR No Name
BDL 140030 STEVE'S TRUCKING Cost Price Margin
$37.12 $50.00 25.76%
BDL 140139 BOB' TRUCKING Cost Price Margin
$202.01 $334.71 39.65%
BDL 405348 JIM'S TRUCKING Cost Price Margin
$43.92 $62.00 29.16%
Totals for BDL $283.05 $446.71 36.64%
~~~~~~~~~~~~~~~~~~~~~~~
final report shows each saleperson by sheet… each saleperson tab will show all the customers by name that purchased a product line, cost , price and margin….
I got everything to function the way i want it by hiding lines where customer that belongs to this saleperson that did not purchase this product line is hidden.
But when the xls is complete it is about a 40 meg file, being there are 40,000 lines for some of these salespeople… which about 39,800 are just hidden…
if customer "Bob's Trucking" does not have a purchase for that product line.. How do I tell Jet not to return a line with the Customer information….and only populate the customers that have bought this item or line?
~~~~~~~
Date
Votes
3 comments
-
Jet Reports Historic Posts You can try using the Link= function.
For example,
=NL("rows=6","Customer",,"No.",$C$3,"Salesperson Code",$C$8,"Link=","Sales Invoice Header","Bill-to Customer No.","=No.","Order Date",$C$2)
This will cause this formula to only return customers who have a "Sales Invoice Header" within the "Order Date" specified in cell C2. -
Jet Reports Historic Posts Thanks… that fixed it…
the Link= tag must be basic function… I wonder how many other "Basic" functions I am trying to work around…
Thanks again -
Jet Reports Historic Posts I wouldn't call the Link= a basic function, and in fact, we rarely teach this function in our level 1 training programs.
FYI: from the support site
http://support.jetreports.com/index.php?_m=knowledgebase&_a=viewarticle&kbarticleid=41
Using "Link="
"Link=" is another mechanism that can be used to filter data in one table based on data in a related table. Specifically, "Link=" should be used when the primary table is smaller than the secondary table. For example, lets say you would like to create a list of invoice numbers that contain item sales. You can list the invoice numbers from the Sales Invoice Header table, but need to use the Sales Invoice Line table to ensure that each invoice contains an item sale. Since all you would like to do is create a list of invoice numbers, you do not need a complete list of Sales Lines for each invoice. Rather, all you want to know is whether an entry containing an item sale exists. To do this, your formula would look something like the following:=NL("Rows","Sales Invoice Header","No.","Posting Date","7/1/05..7/31/05","Link=","Sales Invoice Line","Document No.","=No.","Type","Item")
Note that the first argument after the "Link=" is the name of the secondary table. The successive arguments thereafter represent FilterField/Filter pairs that will be applied to this table (i.e. "Document No." and "Type" are fields in the Sales Invoice Line table, not the Sales Invoice Header). Filter values with an equals sign ("=") pre-pended to them represent the linking fields from the primary table. In the above example, the NL function will examine the value of the No. field for each record in the Sales Invoice Header table and will only include the record if a corresponding record exists in the Sales Invoice Line table where the "Type" field is "Item" and the "Document No." field is equivalent. Note that there can be multiple linking fields.
Please sign in to leave a comment.