Avoiding missteps – NULL values in facts

We learned that with MicroStrategy, it is very easy to create reports that please the Sales Managers who may say "show me the products we have sold on the Internet". But what about the products we did not sell, those we need to remove from the catalog or try to bundle with something else? To find them we first need to learn how to manage the NULL values. In SQL it would be just an OUTER JOIN between the DimProduct and fact table; how to do this in a report?

Getting ready

Create a report with only the Product attribute and run it. How many products do we have? Now add the Sum SalesAmount from FactInternetSales metric and run it again. How many rows are displayed?

So, we know that we sold 158 out of 606 products in our selection. We need to display the 448 rows of the DimProduct table where the value of the metric is NULL (actually there is no row in the fact table for those products).

How to do it...

This is done by changing a setting in Report Data Options...