Attributes can be used not only to label and explain numbers but also to filter results, for example, you may want to report only on sales from one year, or one product category, or items shipped to a specific country. In order to restrict the data that a query returns we use a particular type of object called filter. Filters are then inserted into reports or in metrics to obtain the desired results.
- Open Public Objects folder, and click on the Filters folder.
- On the right pane, right-click and select New | Filter, leave Empty Filter selected and click on OK.
- We're now in the Filter Editor, which is similar to other editors we saw already. Click on the yellow Attribute shortcut in My Shortcuts panel, you'll see a list of available attributes.
- Double-click on Year and look at the right part of the window, here we have the Filter definition area.
- By default, Attribute Qualification is already set to restrict on a list of Year elements, we only need to choose which elements we want to use.
- Click on the button named Add to select from a list of years. In the shopping cart, move
2007and2008to the right with the arrow button, and confirm by clicking on OK. - Click on OK again in the Attribute Qualification pane, and then click on Save and Close.
- Name the filter
Years 2007 and 2008 only, and click on Save. - Now go to My Personal Objects | My Reports.
- Right-click on the report named 02 Calendar, and select Edit from the context menu.
- In the Report Editor, click on Public Object shortcut in My Shortcuts panel, and double-click on Filters in the list that appears.
- You'll see
Years 2007 and 2008 only, the filter we just created, right-click on it and select Add to Report Filter, now select the View | Grid View menu to run the report. - Look at the results; verify that now only dates in years 2007 and 2008 appear on the grid.
- You can close this window without saving the report.
When running the report, MicroStrategy adds a WHERE clause to the SQL sentence sent to the data warehouse.
MicroStrategy filters are very flexible and allow restricting data based on IDs or any other form defined in the Attribute Editor.
Filters can be applied to attributes as well as metrics. When applied to metrics, they generate a HAVING clause. More on this later.
Create a filter to restrict Products Categories to Bikes and Accessories, save it as Categories Bikes and Accessories only.
Create another filter: Greater Paris only to include all 14 elements of City that belong to Paris.
- The Creating a simple counter fact and metric recipe in Chapter 4, Objects – Facts and Metrics