To Join or Not to Join

[Ron McIntyre]

I have a report I need to do that would show a side by side comparison of Last Years Sales by Sales Representative by Customer by Ship To by Season versus this year's Open Orders.

Basically it is a tool for my Sales Reps to know if last year's customers have reordered or not.

2 Queries work fine.

Query 1 is on Open Order Lines
Query 2 is on Sales Invoice Lines

As separate queries and lists, they work fine.

How can I join them to get them side by side?

(Text for my current report can be found here Here.)

I tried to do a JOIN by creating a data item in each and making the expression "1".  Gave really wonky values.

The issue is there may be records for new customers in Query 1 (Current Open Orders) that the same customer is not present in Query 2 (Past Sales) and vice-versa.

Any help would be appreciated.

Thanks in advance.

Ron
(Currently running 8.4)

[cognostechie]

Couple of ways ..

1> Create a Page Set and Group the Page by Customer No. That way all Open Orders and Past Invoices for the same customer would show on the same page. Data for other customers would not show on that page.

2> Create a prompt to select only ONE customer and set a filter on both the queries for that Customer

3> Create Outer joins in FM instead of RS.

[blom0344]

Use a union on the 2 queries ; add a constant to both and build a crosstab with customer as the row node and the constant as the column node. Use the sales rep as section / pageset..

[Ron McIntyre]

Currently I'm working on using JOIN (with an outer join).

I created a "Link ID" constant in each query that would be unique between the 2 files.  I'm doing an outer join on this data item (0..n).

It works ... sorta.

Issue 1: Some records seem to get "dropped" from the filtered child query records.  They seem to be records where all info is the same (in this case, orders or invoices where there are 2 lines with the same item/quantity/price (it happens in our world).

Issue 2: Some records seem to drop basic column data ... ie it brings in the customer number but not the customer name.

I'll keep y'all posted.

[Tober]

Since it appears that you would like to always have the previous year's customers but not the current year's customers here is how I would approach the issue. There are two different ways that I can think of off the top of my head.

1) Create a query that has last years customer list and any other detail that you need (i.e. GM, NI, etc.). Create a second query that shows this year's results for all sales with whatever detail you need. Create a left outer join from previous year to current year. That way only the previous years customers will show up in the report regardless of whether or not there were sales this year.

2) The second way is to take both of the queries and create a master detail relationship on the report its self. You can then change the report to list "No sales in current year" or some kind of statement when no detail exists for the report.

Hope this helps.

Jason
Hope this helps.