It’s pretty easy to report on something like the number of products that a customer purchased using a customer orders table. But what if you want to report on exceptions instead…for example, what if you want to list out the products that a customer has never purchased and show some measures related to those products? This blog post will hopefully help you with this task.

One method of reporting on products that a customer has never purchased is to look at the data for actual purchases, then roll up the product ID’s in a string running total and pass this string running total to a Subreport record selection. Then the Subreport record selection filter would be based on returning product ID’s not in that string.

The method we’re going to look at now is different in that we’re going to use a Command object in the Subreport using SQL syntax to return the exceptions.

A Simple Example

1) First off we will look at a simple statement to return all products purchased by all customers from a customer order fact table. Please note that the syntax below and for the rest of this post will be SAP HANA syntax…your syntax may vary, so please consult online help for your database type.

SELECT DISTINCT CUSTOMERID, PRODUCTID

FROM STS.FCTCUSTOMERORDER

Untitled.jpg

2) Now we will create a derived table using the above syntax. The syntax below uses the WITH directive to create a derived table, PP, that is referenced in a SELECT statement below. The final SELECT statement returns every product ID that is in PP (products purchased) which is derived from the customer orders fact table. In the case of the data that I am using, 63 unique product ID’s are returned. The last line of the syntax is therefore returning a list of all products (ID’s) that have been sold at one time or another to any customer.

WITH PP AS
(
SELECT DISTINCT CUSTOMERID, PRODUCTID
FROM STS.FCTCUSTOMERORDER
)

SELECT DISTINCT PRODUCTID FROM PP

We don’t have to use the WITH directive, but it makes things easier especially when you start adding or manipulating multiple derived tables. In the more complex example later on the reason to use the WITH directive will become more apparent.

3) If we wanted to see the products purchased by a specific customer we would use syntax similar to below. In this case there are 57 products purchased by customer #6.

WITH PP AS
(
SELECT DISTINCT CUSTOMERID, PRODUCTID
FROM STS.FCTCUSTOMERORDER
)

SELECT DISTINCT PRODUCTID FROM PP

WHERE CUSTOMERID = 6

4) Here’s where we bring back the exceptions, using the aptly named EXCEPT set operator. This set operator syntax returns all unique records from a select statement after removing any duplicate records that are returned by a second select statement. Essentially, EXCEPT will remove any matching or intersecting records.

In this example, we are combining the final SELECT statements for step (2) which is all products and step (3) which is only customer #6. Any records for customer #6 will be filtered out of the complete product purchased records described in step (2) above as we are using the EXCEPT set operator.

WITH PP AS
(
SELECT DISTINCT CUSTOMERID, PRODUCTID
FROM STS.FCTCUSTOMERORDER
)

SELECT DISTINCT PRODUCTID FROM PP

EXCEPT

SELECT DISTINCT PRODUCTID FROM PP

WHERE CUSTOMERID = 6

Untitled.jpg

The number of records returned with this final query is as expected, 6 records, representing the products never purchased by this customer.

A More Complex Example

In a more complex example, we’ll first display the syntax for the query and it will be explained in some detail below.

WITH
PP AS
(
SELECT COMPANYNAME, PRODUCTNAME,SUM(UNITSALES) AS SALES
FROM (STS.DIMCUSTOMER C
INNER JOIN STS.FCTCUSTOMERORDER CO ON C.CUSTOMERID=CO.CUSTOMERID)
INNER JOIN STS.DIMPRODUCT P ON CO.PRODUCTID=P.PRODUCTID
WHERE PRODUCTNAME <> ‘NONE’
GROUP BY COMPANYNAME, PRODUCTNAME
),
PNP AS
(
SELECT PRODUCTNAME FROM PP
EXCEPT
SELECT PRODUCTNAME FROM PP
WHERE COMPANYNAME = ‘XMaker inc.’
)

SELECT PNP.PRODUCTNAME,
SUM(PP.SALES) AS TOTALSALES,
COUNT(DISTINCT COMPANYNAME) AS COUNTCUST,
TO_INT(SUM(PP.SALES)/COUNT(DISTINCT COMPANYNAME)) AS AVGSALES
FROM PNP,PP
WHERE PNP.PRODUCTNAME = PP.PRODUCTNAME
GROUP BY PNP.PRODUCTNAME
ORDER BY PNP.PRODUCTNAME

The syntax above is using a similar concept to the previous example where the first derived record table, PP, is used to return all customers with all products purchased and this time with the unit sales of the products.

In this example, we are using a second derived record table (PNP for ‘products not purchased’), which references the first derived table (PP) twice, to return just the product names that the company XMaker has never purchased. This second derived table uses the EXCEPT operator to create this record set. This methodology is identical to what we did in the simple example except we’re bringing back the product names instead of the ID’s.

Our final block of SQL syntax references columns from the first derived table (PP) as well as the second derived table (PNP) to show not only the names of the products that XMaker has never purchased, but also the number / DISTINCT COUNT of other customers that have purchased these products along with the average sales per customer of the products.

Untitled.jpg

Next Steps

Hopefully you can find some value in using the EXCEPT set operator and the WITH directive to produce ‘exception’ based data to in your Crystal Reports. Now if you wanted to add this type of exception reporting into an existing report it would be fairly easy to do.

You can Insert a Subreport to your existing report and instead of adding tables from your database, press the Add Command button. Syntax to the above examples would remain the same except for one change in that you would want to Create a Parameter in the Command and substitute the customer ID or customer name with the new parameter. The Subreport would later be linked from your main report (ID or name field) to the existing parameter in the Subreport’s Command object.

Again, the above SQL syntax is for SAP HANA so please consult your online database help when constructing syntax for your reports. If you want to learn more about SAP HANA please visit the SAP HANA Academy where you can get free video based training on many different HANA topics, including HANA SQL.

To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply