0

Sum of Sales Line and Historical Sales Line data

Jet Reports Historic Posts

March 31, 2011 22:31

None

Follow

Hello,

I am attempting to create a report that sums the sales qty per month per salesperson.

I have an rolling 18 month date window across the top columns (Jan, Feb, March, etc.)

Then I have rows of salespeople.

I need to find the sum of the sales qty per month for each salesperson.

The problem i have is, I need current data for items that have not shipped, which I can take from the sales line table, and historical data for the items that have shipped, which I can get from the sales line history table.

My first thought is something like:

$G15 = Salesperson Code
I$10 = Date Filter

=NL("Sum","Sales Line","Qty. to Ship","PfsSalesperson Code",$G15,"Link=","Sales Header","No.","=Document No.","Order Date",I$10)+NL("Sum","PfsSales Line History","PfsOrder Quantity","PfsSalesperson Code",$G15,"Link=","PfsSales Header History","No.","=Document No.","Order Date",I$10)
Problem here is that it takes forever. Does anyone have any suggestion for a more efficient way to get the result I am looking for?

Thanks,

Tyler

• Facebook
• Twitter
• LinkedIn

2 comments

• 0
  
  Jet Reports Historic Posts April 02, 2011 03:43

  Usually, we use "link" if the main table is smaller than the linked table.
  Since the sales line table is always bigger than the sales header table, why don't you try using "filter" instead.
  
  =NL("Sum","Sales Line","Qty. to Ship","PfsSalesperson Code",$G15,"Document No.",NL("filter","Sales Header","No.","Order Date",I$10)+NL("Sum","PfsSales Line History","PfsOrder Quantity","PfsSalesperson Code",$G15,"Document no.","filter",PfsSales Header History","No.","Order Date",I$10)

• 0
  
  Jet Reports Historic Posts April 04, 2011 14:40

  I'm guessing the Sales Header table is big enough that it's still going to be faster to use Link=, but test it and see. Also, I believe the link fields between the Sales Header and Sales Line are Document No. = No. (as you have) and also Document Type = Document Type. You should probably add the Document Type filter to your Link= statement. This could speed it up a bit, but it also may keep you from getting data you don't want. I don't know if your historical PfsSales Line History table and its header have the same linking fields, but it seems quite possible.
  
  Regards,
  Hughes