Like dynamic aggregation, view filters are part of MicroStrategy OLAP Services; they are used to reduce the amount of data displayed on the grid without having to re-execute the query on the data warehouse. This means that the filtering is executed in the Intelligence Server memory and it is faster than filtering on the RDBMS side.
When dealing with a large volume of data it may sometimes be tempting to retrieve a full dataset in the first place and then play with view filters on the client side. Please see the Intelligent Cube feature on the OLAP Services guide in this case. In Chapter 12, In-Memory Cubes and Visual Insight, we'll see some examples.
Report filters differ from view filters in the fact that they apply a WHERE clause to the SELECT statement and re-execute the report every time a filtering condition is changed. View filters do not.
You should have already created the report 19 Products Sales on Internet, if not, please go back to the Avoiding missteps – NULL values in facts recipe and do it. In that report we wanted to list the products that we did not sell on the Internet; in fact we listed all of them. Now we will complete our analysis by applying a view filter on it.
Run the report and follow these steps:
- The View filter pane should appear right above the grid of the report; if you don't see it, you can activate it from the View | View Filter menu.
- Click on New in the gray area, now there are three blue links saying Field Operator Value.
- Use the Field link to select the Sum SalesAmount from FactInternetSales metric.
- From the Operator link, select Is Null and then click on Apply in the gray area.
- The filter is applied and only those products with null sales appear on the grid (total row number should be 448).
- Now in order to reverse the filter and display only those products with sales, we need to click on the Is Null red link and change it to Is not Null.
- Remember to click on Apply every time you modify the condition.
The view filter condition is similar to a threshold, and can be used to restrict data based on attributes or metrics. You may also add more than one condition and create very complex view filters by joining them with operators: AND, OR, and more.
If you check the box Auto-Apply changes, there will be no need to click on the Apply button every time the condition is modified. This comes handy in case of quick tests and small datasets, so that you see the immediate effect on results.
Modify the view filter so that only products with no sales or with sales under 5,000 are shown.
- The Displaying both grid and graph in the same view recipe in Chapter 6, Data Analysis and Visualization – Graphs