We left the previous recipe with a doubt: where have all the numbers gone? We added the Year attribute to the report and the overall reseller sales dropped from 80 million to 32 million. No mystery, it's the standard way of joining metrics. The metrics on a grid are INNER joined. What does that mean? And most important, who told MicroStrategy to do so? That means that we have a first metric with values for 2005, 2006, 2007, and 2008, then we have a second metric—filtered on a promotion—that only has data for the year 2007, because that promotion was limited in time during 2007. During the creation of the grid the two metrics are joined on the Year attribute, resulting in a single-row report. Let's see how to avoid this inaccurate and dangerous result.
Re-open the 14 Reseller SalesAmount during Touring-1000 Promotion report, and save it with a new name, for example, 15 Reseller SalesAmount during Touring-1000 Promotion (OUTER).
From the Grid View of this report:
- Click on the Data menu, and select Report Data Options.
- In the Report Data Options dialog, click on Calculations | Metric Join Type in the Categories list.
- On the right in the grid that appears open the Join Type combobox and select Outer for both metrics.
- Click on OK and then Yes to re-execute the report.
- Now the grid shows the correct numbers for
SalesAmount.
By default the numbers are shown on a grid when there is data for all the metrics involved in the report. To override this, we need to set the metric Join Type option as Outer.
There are cases when the metrics should always be OUTER joined, like in this scenario. There is a way to specify the default join behavior on a metric-by-metric basis. Try it yourself:
- Go to Public Objects | Metrics and edit Sum SalesAmount from FactResellerSales (Touring-1000 Promotion).
- In the Metric Editor, click on the Tools | Metric Join Type menu.
- Uncheck Use default inherited value, click on Outer and then on OK.
- Click on Save and Close. Now this metric will always be OUTER joined.