Embedding filters inside metrics
Filters can be used in reports to restrict the result of the entire grid, or can be embedded into a metric to restrict only one particular number. For example, when you want to compare sales during holiday season against the whole year or the margin of one category of product as compared to another category.
If you put a holiday season filter on the report, all the metrics values would be restricted by the WHERE clause; whereas if you put the filter inside one single metric, only those specific values would be filtered, while the rest of the numbers won't be affected.
Getting ready
We need to add a new dimension table and create the Promotion attribute. From the Warehouse Catalog window, add the table DimPromotion, click on Save and Close, and create a new attribute with these columns:
- ID: The PromotionKey column in the tables DimPromotion (lookup) and FactResellerSales
- DESC: The EnglishPromotionName column in the table DimPromotion (lookup)
Save the attribute as Promotion and update the schema.
How to do it...
Next we create a filter:
- Go to Public Objects | Filters and create a new empty filter.
- In the Filter Editor, from Attributes double-click on Promotion, when the Attribute Qualification panel appears, click on the Add button next to Element List.
- In the shopping cart move Touring-1000 Promotion to the right and click on OK.
- Click on OK again and on Save and Close. Name the filter
Touring-1000 Promotion only. - Now go to the Metrics folder and double-click on Sum SalesAmount from FactResellerSales to open the Metric Editor.
- In the upper-right pane, click on a line that says Condition = (nothing).
- The editor automatically shows the available filters, double-click on Touring-1000 Promotion, see that it gets added to the right Selected condition pane.
- Click on File | Save As and give a different name:
Sum SalesAmount from FactResellerSales (Touring-1000 Promotion). Save and close the editor window. - In My Reports folder, create a new blank report. From the Public Objects | Metric folder, double-click on Sum SalesAmount from FactResellerSales and Sum SalesAmount from FactResellerSales (Touring-1000 Promotion), adding the two metrics on the grid.
- Go to Grid View and look at the result: the first metric is the total
SalesAmount, while the second is filtered and showing only theSalesAmountduring Touring-1000 Promotion. - Close the report and save it as
14 Reseller SalesAmount during Touring-1000 Promotion.
How it works...
If you look at the SQL view, you'll notice two SELECT statements, both retrieve sum(a11.SalesAmount), the first with no restriction, and the second with a WHERE a11.PromotionKey in (14). The two numbers are then displayed on the grid. There is no GROUP BY clause because we do not have any attribute on the report.
There's more...
The last SELECT statement is a CROSS JOIN between the two temporary tables. It's OK; they both have one row so the result is correct.
Exercise 21
Add the Year attribute to this last report, to have the SalesAmount aggregated by year: what happens to the numbers? Can you spot the error?