By Peter Beck, CBIP

In the Sherlock Holmes story “Silver Blaze”, Holmes has the following famous exchange with a policeman:

Policeman: “Is there any other point to which you would wish to draw my attention?”
Holmes: “To the curious incident of the dog in the night-time.”
Policeman: “The dog did nothing in the night-time.”
Holmes: “That was the curious incident.”

A common requirement in reporting is a report that shows not only what happened, but what *didn’t* happen. As an example, consider sales of several different products in a number of territories to a number of customers. A specification for a report may require it to show entries for sales to customers in a particular territory, for *all* products – even for products for which there were no sales.

To end users this is the most normal request in the world – as a sales manager, I want to see a line on a report that shows “0” for the products I didn’t sell.

From a reporting perspective however this can sometimes be a little tricky, especially in a non-OLAP, relational reporting environment. As an extreme example, I was once asked to create a report of just this type, in an organization that in theory had over 250,000 SKUs. Obviously the customer didn’t want a report that included the 1000 products we normally sold and “zero” entries for 240,000 SKUs, but it took a lot of back and forth to figure out just what the customer did expect to see.

Even a seemingly simple example can have unexpected difficulties however. Consider an example where we have 3 customers, each in a different Province, and each capable of buying 3 different products:

In Framework Manager our dimensions are joined to our fact table using Outer Joins. That way we should return columns in out reports for customers or products that had no sales, right?

Here is a snippet of a Report Studio report for sales for these products, from a query called Sales:

It looks like Cust01, Smith Company, bought nothing, and no-one bought any Oranges.

We have sales in Alberta (AB) for CUST02, but all they bought are Prod03 – Pears. We’ve been directed to produce a report filtered on AB that shows ‘0’ entries for the products that weren’t sold.

Since we’ve used outer joins in our model, we should be able to filter the query Sales on AB:

… except that we get this result:

The filter is applied to the result set returned from the server, and the entries for Apples, which were sold in QC, and the entries for Oranges, for which there were no sales (and so the ProvCd is null) are filtered out.

One fairly simple way to get around this is to create a local Product Dimension, and Outer Join that on the result set with a local join. This will attach a list of all products to the result set regardless of what filter is applied to it.

First we create a Product query:

Then we join the Product query to our original, filtered result set, joining on the common ProdNo <-> ProdNo field (which is in the original result set query called Sales.) We use an Outer Join here to ensure that ProductDescription is returned regardless of whether or not there is a corresponding sale of the product in Fact_Sales:

Now we can bring ProdNo and ProductDescription from the query Products into the new result query Products_And_Sales (When we do so they are automatically appended with the number “1” to distinguish them from ProdNo and ProductDescription in Sales):

Now we get a result that shows one entry – with null sales – for products for which there were no sales in Alberta – the ProdNo and ProductDescription will come from our Products query:

It is now a simple matter to turn Sales_Value into a calculated column in the Products_And_Sales query, putting in a “0” value in the event it is null, for example (in a cross-tab this can be done automatically in the cross-tab settings.)

This is a very simple example that can get much more complex – it may be useful to create “join columns” filled with a “1” value, for example, and join on those. Pay careful attention to your Facts – it is possible that for some dimension there is no activity at all.

Finally, another approach, not shown here, is to create an *unfiltered* copy of the original result set with “null values” for all the “fact” columns, and use a Union-join to join it to the original result set.