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.
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:
Save the attribute as Promotion
and update the schema.
Touring-1000 Promotion only
.Sum SalesAmount from FactResellerSales (Touring-1000 Promotion)
. Save and close the editor window.SalesAmount
, while the second is filtered and showing only the SalesAmount
during Touring-1000 Promotion.14 Reseller SalesAmount during Touring-1000 Promotion
.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.
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.
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?