- Business Intelligence with MicroStrategy Cookbook
- Davide Moraschi
- 522字
- 2021-07-23 15:32:07
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?
- Pentaho Business Analytics Cookbook
- 新中國審計制度變遷
- 汪博士詳解PMP?模擬題（第4版）
- 中國特色社會主義國家審計制度研究
- Business Intelligence with MicroStrategy Cookbook
- Microsoft System Center Data Protection Manager 2012 SP1
- Team Foundation Server 2013 Customization
- 中國政府統計問題研究
- Microsoft Dynamics NAV
- IBM SPSS Modeler Cookbook
- Business Intelligence Cookbook：A Project Lifecycle Approach Using Oracle Technology
- Oracle E-Business Suite Financials R12：A Functionality Guide
- Building Dashboards with Microsoft Dynamics GP 2016（Second Edition）
- 統計原理與實務
- 中國重點經濟領域統計分析