What products are customers not buying?

[ERKcool]

CR v8
I have 2 tables
Billing.Detail (invoice #, customer number, product code, description, order quantity, sale price, etc…)

Products (products code, description, text2, amount1, amount2, etc…)

Tables are linked by product code.

I can get it to tell my what products a given customer orders, (run a report that groups prodicts, than customer with the detail band hidden). Now how do I get to tell me the products that that they don’t order? Products that don’t match products pulled by the last report?

 

[lbass]

You need to clarify what you're looking for. The following two things are different:

tell me the products that that they don’t order? Products that don’t match products pulled by the last report?

You should also clarify whether you mean you want to see which products are not ordered by a particular customer versus by all customers.

-LB

 

[ERKcool]

Products that don’t match products pulled by the last report?" (The one that told me the products they do order)

Tell me the products that a specific customer doesn’t order?

-Erik

 

[lbass]

I guess you thought I was being critical, but I only meant to get clarification. "...tell me the products that a specific customer doesn't order?" was extracted from your own first statement. The issue is that you cannot write a new report using an old report as a datasource, so how was the old report defined? Do you mean to compare two different time periods, i.e., products that were bought by a specific customer last month, but not this month? And as I mentioned earlier,you should also clarify whether you mean you want to see which products are not ordered by a particular customer versus by all customers.

-LB

 

[lbass]

I guess we are miscommunicating here--I guess you are saying you want to look at this according to specific cusotmers. Let's say you want to look at sales for the last full month, and that you want to see only products that were purchased in the month previous to the lastfullmonth, but were not purchased last month.

First create a record selection formula that includes both months:

{invoice.date} in dateadd("m",-1, minimum(lastfullmonth)) to maximum(lastfullmonth)

If you want this per customer, then insert a group on the customer field, and make this your group #1. Then insert a group on {product.code}, and place the product fields in the detail section. Then go to report->selection formula->GROUP and enter:

maximum({invoice.date},{product.code}) < minimum(lastfullmonth)

That will show you the products purchased two months ago, but not purchased in the last month.

-LB

 

[ERKcool]

Ok there is some miscommunication. - The old report is one that looks at the customers billing detail, finds all products that they have ordered and prints that product with a blank spaces to write in so customers can use them as reorder sheets. What I am after is the inverse of that. What products has a specific customer never purchased? Date range is over the life of the account.

The never purchased is where I am running into my problems.

Thank you,
Erik

 

[Turkbear]

Hi,
With that data structure it may be impossible...There is no obvious way ( from what you posted) to link customers to the Product table except through the Billing Detail..If no orders, no customer in billing..






To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[ERKcool]

Any idea of how I could get the data I seek?

 

[Turkbear]

Hi,
Is there a customer table ( with at least the customer#)?

If so, a link can be probably be constructed to show unordered products or some other means to do it..but, without such a table, I see no way to do it..






To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[ERKcool]

Yes, their is a customer table with a customer number.

 

[Turkbear]

Hi,
As I think about it, it becomes clearer that it may not be possible even with that info..

The problem is that the only intersection between Customer # and Product ID is the billing table, so it will be impossible to find out, for specific customers , which products are NOT in any billing record..

You can find which products no customer ordered and which customers have not ordered any products but there seems to be no way to link a particular customer to any non-ordered product IDs








To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[ERKcool]

I thank all for the help provided. If I find a solution I’ll post it.

 

[lbass]

You could use a left join FROM the product table to the billing table, and then insert a crosstab in the report header or report footer where you use {product.productcode} as the row field, {billing.customerID} as the column, and count of {billing.productcode} or something like that for your summary field. This would give you a visual summary of what products each customer is not ordering.

You could also look at customers one by one by making use of the database->show SQL query. First you would create a report that used one field of the same datatype as the product code field. Let's say that field is {Customer.Customer Number}. Place the field in the detail section. Then go to database->show SQL query. It will show something like:

SELECT
Customer.`Customer Number`
FROM
`Customer` Customer

Then add the following to the SQL query:

where 1 = 0
union all
Select Products.`Products Code`
From `Products`Products
Where Products.`Products Code` not in
(
select Products.`Products Code` from (`Products`Products inner join `BillingDetail` BillingDetail on Products.`Products Code` = BillingDetail.`Products Code`) where BillingDetail.`Customer Number` = 1
)

The {Customer.Customer Number} field will now hold all product IDs never ordered by customer #1. You would have to manually change the number and refresh the report for each customer you were interested in.

The "1 = 0" basically causes the first part of the query to return no records. Since CR 8.0 doesn't allow you to alter the select part of the query, this allows you to use a union all to write a query from scratch.

-LB

 

[jsw1229]

Use the same report, but add a conditional formula where the "quantity ordered" is < 1?

 

[ERKcool]

First you would create a report that used one field of the same datatype as the product code field."

The field is the same data type but the size is different.

CR gives me the error
"ODBC error: [Pervasive][ODBC Client Interface][LNA][Pervasive][ODBC Engine Interface]Incompatible type in predicate"

The Query I used was

SELECT
Customers."CVNo"
FROM
"DEBC Lead Masters"."Customers" Customers

where 1 = 0
union all
Select Products."Code"
From "DEBC Lead Masters"."Products" Products
Where Products."Code" not in
(
select Products."Code" from ("DEBC Lead Masters"."Products" Products inner join "DEBC Lead Masters"."BillingDetail" BillingDetail on Products."Code" = BillingDetail."DetailNo") where BillingDetail."CVNo" = 909195
)

 

[lbass]

On second thought, I don't think you have to use a union. Just add the products.code field to the detail field, which will generate:

Select Products."Code"
From "DEBC Lead Masters"."Products" Products

Then go to "Show SQL Query" and add the following where clause:

Where Products."Code" not in
(
select Products."Code" from ("DEBC Lead Masters"."Products" Products inner join "DEBC Lead Masters"."BillingDetail" BillingDetail on Products."Code" = BillingDetail."DetailNo") where BillingDetail."CVNo" = 909195
)

-LB

 

[ERKcool]

CR gives me the error
"ODBC error: [Pervasive][ODBC Client Interface][LNA][Pervasive][ODBC Engine Interface]Incompatible type in predicate

 

[lbass]

I'm sorry, but I don't know what is causing the message. I tested the above with the Xtreme database (Access-based) and it worked, but I'm unfamiliar with Pervasive.

The message sounds like either Products."Code" and billingDetail."DetailNo" are not of the same datatype or that BillingDetail."CVNo" is not a number (and therefore the value should be in quotes: '909195')--but that's just a guess.

-LB

 

[ERKcool]

Thank You, Thank You, Thank You

Very good guess. That was just what the problem was. The customer number is text stored as text in a text field. Now all I have to do is wait for the system to run the join on 400,000 records.

Thank You for you help I truly do appreciate it.
Erik

 

[ERKcool]

The query that finaly worked is

SELECT
Products."Code", Products."Desc", Products."SalePrice"
FROM
"DEBC Lead Masters"."Products" Products
WHERE
"Products".Code not in (select distinct("BillingDetail".DetailNo) from "DEBC"."BillingDetail"BillingDetail where "BillingDetail".CVNo='619383')

Thanks all for the help
Erik