Filtering data on a report: Filters

A report filter is the part of a MicroStrategy report that screens data in your data source to determine whether the data should be included in or excluded from the calculations of the report results. A brief introduction to filters is provided for report analysts in Answering Questions about Data, in the section Filtering data. Review this information if you are new to creating report filters. It describes how to view a filter's definition for a given report. This can be a useful way to create an effective filter, because you can copy parts of an existing filter's definition when creating a new filter. You can even use an existing filter within a newly created filter.

If you are familiar with SQL syntax, a filter is equivalent to the WHERE clause in a SQL statement.

Filters are helpful in clarifying large quantities of data and only displaying subsets of that data, so reports show users what they really need to see. For example, you want to determine the number of injuries to your delivery personnel in 2005 that may have been due to bad winter weather in the northeastern U.S. You also want to know the time of day when most injuries occurred. You place the Delivery Location and Delivery Time attributes on your report. You also place the Number of Reported Injuries metric on the report. But you only want the report to display injuries in your northeast region during the winter of 2005. Without a filter, you would have to sift through a lot of report data on your own. By creating a filter that includes Northeast Region, January 2005, and February 2005, and using that filter on the report, the data displayed when the report is executed is limited to that geographical region and season.

In another example, consider the following diagram, which shows a table of data filtered by three different filters.

Each filter returns a different result set. Filters ensure that the report results contain only the data that answers the specific business query the report was designed to answer. It is important to design the correct filter to retrieve the desired data.

Filters are most commonly used on reports, to filter all the data the report would otherwise retrieve from the data source and display to users. However, filters can also be used with a specific object on a report, such as on a metric. A filter placed on a metric only filters data related to that metric. When used on a metric, a filter essentially "goes with" the metric so that whatever report the metric is placed on, the filter is always part of that metric. (This type of metric is called a conditional metric. For details on conditional metrics, see the Advanced Reporting Help.)

Filters are an important part of almost all reports. Understanding how to create a filter is an important skill in making the best use of the data in your data source. This section describes the different types of filters available in MicroStrategy and shows you how to create a simple filter of each type.

Basic knowledge of formal logic is useful in understanding report filters and their concepts, but it is not a prerequisite to learn how to create filters.

Many of the reports and filters used as examples in this section already exist in the MicroStrategy Tutorial project. You can follow the steps presented in this chapter to create filters, or you can view the saved samples in the Tutorial project. For information on the Tutorial project and how to access it, see About sample data and the MicroStrategy Tutorial project.

A note on terms: filter, qualification, condition

A filter is composed of one or more qualifications. Qualifications define the conditions that the data must meet to be included in a report, for example, "Region = Northeast" or "Revenue > $1 million". If a filter contains more than one qualification, the qualifications are joined by an arithmetic operator such as AND or NOT. For details on using operators to join filter qualifications, see Joining filter qualifications with operators.