Select All Records even if no match

[#1]

I have two files, Order Intake.xls and ARInvoice History.  I am joining them by the Order Number.  The report is to list all the orders for a Salesperson for a time frame, and if the Order has been billed, list the invoice number, date and amount.  If it has not been billed these fields should be blank/0.

I am only getting a list of orders that have been invoice at this point.  I think I should use a SET operator, but unsure how to define it.  Any help would be appreciated.

[swuehl]

Hi jakeh999,

for the salesperson no 970, selected in your attached app, I can't find any order without invoice. Maybe I'm missing something, if so, could you please point me to an order no that should appear here?

If I clear the salesperson selection and keep the selection on bus_YearMonth only, there are some orders without invoices.

I would suggest that you use only those fields for dimension that you really need to group your data. For example, I wouldn't use the order Total as dimension and probably not the invoice number if I am just focus primarily on the orders.

Please see my attached modifications (only moved around the dimensions to expressions).

There are also some options on dimension tab (suppress when value is null / show all values) and presentation tab (suppress zero values), that you can play with, but I think in your case, it's just about placing the fields right into dimensions / expressions.

Hope this helps,

Stefan

[#3]

I have added the spreadsheet of order intake Oct2011 to my original post.  Salesperson 0970 has approx $151k in orders for the month, and it appears that $51k of these orders will billed. 

If I was using MS Access or SQL  I would use a Left Outer Join to link the tables by Order Number.  My objective is to create this join here.

I hope this clarifies things.

[swuehl · Accepted solution]

One thing I noticed: there seems to be some inconsistency between fields Salesperson and SalespersonNo (but I don't know if this is intended) regarding the linked Order Number.

In your attached app, you use field SalespersonNo and if you select #970, you will see only the some $51k.

If you instead select #970 on Salesperson field, you will notice that then you get your 53 orders and the total order amount of about 135k (you get 151k if you include Order 120181 from your excel file, but this is linked to end of September, not October2011).

Maybe this does explain something for you.

Stefan

[#5]

Swuehl,

You are correct, the listbox was not the correct Salesperson field.  I changed it and now it is correct.  Thank you for being a valuable asset on this forum.