Tutorial 13-7. Performing a SQL Distinct
Just to make sure that you have truly mastered the art of creating custom cross-tab functions, let’s add one more level of difficulty to the previous tutorial. In the last tutorial, we counted the total number of orders for the country USA. The formula returned a ‘1′ for each order so that each one would be added to the total. But, let’s make this more challenging by saying that we only want to count the number of customers who placed orders and not the orders themselves. This presents a challenge because we want the cross-tab summary function to count a customer once, but then skip it afterwards. Since the cross-tab object calls the formula separately for each row in the database, how does it keep track of the first time a customer appears but not the other times? Pretty tricky stuff!
To do this, we keep the same basic formula, but we have to add additional logic to track which customers have been included in the summary and which ones haven’t. If you are familiar with databases and the SQL language, this effectively emulates the SQL DISTINCT operator. If you like, stop here and think about how you would do it before continuing. Once you are ready, read on to see how I do it.
To trick the cross-tab object into only counting each customer one time, I build a string that lists each customer that has been counted. As each new customer gets counted, I add it to the end of the list. If a customer is already in the list, then I don’t count it.
The general idea is that if the customer is not in the list, return a ‘1′ so that it gets counted. If the customer is already in the list, return a ‘0′.
Unfortunately, this is a pretty simplistic interpretation of the problem and the first time I built this formula it didn’t return the correct results. Let’s look at the working version of the formula and I’ll discuss the places where I went wrong and what I did to fix it.
The first step is to define the string variables used. The CustomerList is a global string. This is where we keep track of all the customers already counted. The key is declaring it as Global so that it doesn’t lose its value between records. The CustomerName variable is where we store the current customer name.
Within the If statement, if the country is USA, we assign a value to the CustomerName variable. As you can see, we do more than just assign the customer name to the string. We add extra data that is associated with each customer. This is a bit complicated, so for a moment let’s skip this detail so that we first understand the overall logic.
The next If statement checks to see if the CustomerName value is not included in the CustomerList string. The Instr() function tests to see if one string is already in another string. It returns zero if the new string is not already in the existing string. If the InStr() function says that CustomerName is not in the CustomerList string, then we return a 1 value. This tells the cross-tab to count it in the summary function. If the InStr() function says that CustomerName is already in the list, then return 0. This prevents it from being counted in the summary function more than once.
When the cross-tab sub-total is finished doing all its calculations, each customer will have only been included in the sub-total one time. Duplicates do not get counted.
Now that you see how the overall logic works, let’s look at the CustomerName variable in more detail. The first time I built this formula, all I did was assign the Customer Name field to this variable. But this produced two problems, as we’ll see next.
The first problem is that some customer names are subsets of another customer name. For example, there is a customer called “Spokes” and another customer called “Spokes For Folks”. If the customer “Spokes For Folks” is already in the list, when we test to see if “Spokes” is in the list, the Instr() function will say that it is. This is because “Spokes” is the first part of “Spokes for Folks”. The Instr() function isn’t smart enough to realize that they are actually two different customers. To fix this problem you have to put delimiters around the customer name so that you know the exact beginning and end of the string. This formula puts a “~” at the beginning and end of the string to fix this problem.
The second problem is that when I ran the report, all the sub-totals were the same for every year. I doubted that the same number of customers placed orders in each year, so I researched further. I realized that as the cross-tab object builds the string of customer names, it is using the same string for each column (the Year) in the cross-tab object. There is no way to differentiate in which year the customer placed an order. Thus, each year has the same sub-total. To fix this, I used the Year() function to concatenate the order date’s year value into the CustomerName string. This gives each customer a separate entry in the CustomerList string for each year. If you are going to use a similar formula in your report, you would replace the Year() function with a string that identifies the values in the cross-tab column.
By placing delimiters around the customer name and including the year, this lets the cross-tab object calculate correct results based on the full customer name and only include it for the years that orders were placed.
If you take the report that we built in Tutorial 13-6 and substitute this new formula and preview it, you’ll see that the USA row shows the number of customers who had sales for each year.
