registration
Microstrategy

Microstrategy

This course contains the basics of Microstrategy

Course introduction
Interview Questions
Pragnya Meter Exam

Microstrategy

FILTERS
Introduction

A filter specifies the conditions that the data must meet to be included in the report results. It is an easy way to answer both simple and complicated business questions.

An example of a simple business question is the sales for all stores in the Northeast. To build this report, place the attribute Store and the metric Dollar Sales on your report and filter on Northeast. The filter allows you to view the results for only those stores in the Northeast.

You are interested in reviewing the sales for only those stores that have at least one category with a margin greater than 20% and total sales of more than $100,000 for the year. This is a more complicated question, but it can also be answered using a filter.

This reviews the categories of filter functionality and the types of filtering techniques used, to help you achieve the answers to your simple and complex business questions.

Remember, all that a filter really does is help you answer “show me X where Y is true.”

Types of filters

In the reporting environment, when you design a report, it queries the database against all the data stored in the data warehouse. By applying a filter, you can narrow the data to consider only the information that is relevant to answer your business question. The previous, Reports, discussed the following three ways to restrict data to generate reports:

  • A report filter that is a criterion used to select the data to calculate the metrics in the report.
  • A report limit that specifies a set of criteria used to restrict the data returned in the report data set after the report metrics are calculated. Because it is based on the report’s metric values, a limit is applied after all metrics are calculated.
  • A view filter that is an additional filter applied in memory to the report data set. It affects only the view definition. This focuses on report filters and expands on their advanced capabilities.

Report filter options

The following options are available while creating filters:

  • Attribute qualification allows you to filter on an attribute’s form (ID, description, and so on) or on elements of the attribute.
  • Set qualification allows you to create a set based on one of the following:
    • a metric (also known as a metric qualification)
    • a relationship filter
  • Shortcut to a report, which is also referred to as Report as filter, uses an existing report as a filter.
  • Shortcut to a filter uses an existing filter as a base to which you can add more conditions to further refine the filter.
  • Advanced qualification lets you create one of the following:
    • a custom expression, including a relationship filter
    • a joint element list, which allows you to join attribute elements and then filter on that result set
  • You can also create filters that prompt you for answers when you run the report. These prompted filters allow the report to have dynamic report definitions, which can change with each query by altering the information in the prompt dialog box. All of these options are available in the Filter Editor, and are discussed in detail in this chapter.

Attribute qualification

Attribute qualifiers enable you to specify conditions that attribute elements must satisfy to be included in the filter definition. For example, you can create a qualification on the attribute Month so that the result set returns only months beginning with the letter “J.”

Attribute element list qualification

Attribute element list qualifications allow you to qualify on a list of attribute elements. For example, in a report, you can use an attribute element list qualification on the attribute Customer, to return data only for those customers that you specify in your list.

Attribute element list qualification example

This example refers to filters and reports saved in the MicroStrategy Tutorial.The directory path within Desktop is Public Objects Reports Technical Reports Reports by Feature Advanced Reporting Examples.You can follow the steps to interact with the filters and reports, or you can view the samples without creating your own. Remember to save any objects that you create under a different name, so that you do not overwrite the samples in the MicroStrategy Tutorial.

A report includes the revenue, cost, and profit for all employees. However, certain months are not representative of the normal business cycle, so they should be excluded from the report calculations. To do that, create a filter that excludes the months of April, May, and December. This filter is saved as Month in the Supporting Objects subdirectory. For step-by-step directions on creating a filter, see the online help.

Open the Basic Report. Note that Leanne Sawyer’scontribution to revenue is $316,786. Now switch to Design View and add the Month filter. When you re-execute the report, it looks like the following.

If you do not want to create it yourself, this report is saved as Filter - Month Filter in the Tutorial.

Notice that the metrics have different values than in the Basic Report. Sawyer’s contribution to revenue is now $198,976. In the Basic Report, all data for all months was retrieved from the data warehouse. The Revenue metric was calculated using all months. In this filtered report, April, May, and December amounts are not retrieved from the data warehouse, so the metric cannot include them in its calculations.

Attribute form qualification

Attribute form qualifications allow you to qualify on an attribute form. For example, to return data only for those customers whose last names start with the letter H, you can use an attribute form qualification for the form Customer Last Name in a report.

Attribute form qualification example

This example refers to filters and reports saved in the MicroStrategy Tutorial. The directory path within Desktop is Public Objects  Reports Technical ReportsReports by Feature Advanced Reporting Examples. You can follow the steps to interact with the filters and reports, or you can view the samples without creating your own. Remember to save any objects you create under a different name, so that you do not overwrite the samples in the MicroStrategy Tutorial.

A report includes the revenue, cost, and profit for all employees. You want to view the data of only those employees whose last name begins with the alphabet ‘B’. To do this, create a filter that qualifies on the Last Name of the attribute Employee. Choose the Operator as Begins With and Value as B. Save this filter. For step-by-step directions on creating a filter, see the online help.

Open the Basic Report. Now switch to Design View and add this filter. When you re-execute the report, it looks like the following.

Notice that the report displays the revenue of only those employees whose last name begins with the alphabet ‘B’.

Dynamic dates

When you qualify on a date attribute form with the date data type, you can select dynamic dates, which are fixed offsets of the current date. They can be either a fixed set of dates or different date ranges that change through time. For example, a dynamic date can be used in a report that examines sales in the previous two months. This would be represented as "today" with an offset of two months. You can express Dynamic date qualifications in several ways, as shown in the following examples:

  • an offset of four years, three months, two weeks, and one day from today
  • Monday of this week
  • Monday of this week with an offset of two days
  • the fourth of this month
  • the fourth Wednesday of this month
  • May fourth of next year
  • the third Wednesday in May of this year

    While evaluating a dynamic date such as “first of this month minus seven days,” the order in which these two parts are calculated is significant. The addition or subtraction of days, weeks, months, or years is always done first, before “first of this month,” “this week,” “this year,” and so on is calculated.For example:

  • If today is February 13th, then “today minus seven days” is February sixth, and “the first of the month of today minus seven days” is February first.
  • However, if today is February second, then “today minus seven days” is January 26th, and “the first of the month of today minus seven days” is January first.

Imported filter

You can import filter elements into the Filter Editor from sources other than MicroStrategy, if you choose the attribute qualifying operator as In list or Not in list. To import elements into a filter, the elements should be stored in an Excel file or a text file.

The import filter elements option adds more flexibility to the Filter Editor by allowing lists of data from pre-existing files to be imported into the filter definition. Existing filter definitions can also be exported to a file.

You can use a prompt to allow you to select the file to import when you run the report.

Importing elements from a text file or a Microsoft Excel file can be quicker and more efficient than selecting each individual element to be included in the filter. For example,you have an Excel spreadsheet that lists the products on sale this month. You need to review last week's revenue for just these items. Rather than selecting them in the Filter Editor,you can simply import the file. Likewise, you can export existing filter definitions to a file.

The following rules apply to the formatting of files:

Excel-Data can be stored in rows, columns, or both.

  1. If the data in a cell has double quotes in the first and last position, it is imported as it is, with the quotes.
  2. If the data in a cell has single quotes in the first and last position, it is imported as is, with the quotes.
  3. If the data in a cell does not satisfy conditions 1 or 2, it is checked to see if it is a number. If the data is a number, it is imported as it is.
  4. If the data in a cell does not does not satisfy conditions 1 or 2, it is checked to see if it is a date. If it is a date, it is imported by adding single quotes at the beginning and at the end to comply with the date format.
  5. If the data in a cell does not satisfy any of the above conditions, it is considered as text data and is imported by adding double quotes at the beginning and end to comply with the text format.

Text-Data in a text file must be one of the following:

  • Tab-delimited
  • List-delimited as specified in the regional settings
  • Return-delimited

Attribute-to-attribute qualification

Attribute-to-attribute qualifications allow you to create reports that compare two attributes through attribute forms. For example, using attribute-to-attribute qualifications, by comparing order date with ship date, you can create a report that displays the orders that were shipped within a week of their order date.

Attribute-to-attribute qualification example

This example refers to information found in the MicroStrategy Tutorial.

An attribute-to-attribute qualification can be used to create areport that lists orders that were shipped more than 27 days after the order date. Start a new report with Order, Day, Ship Date, Revenue, Cost, and Profit. To limit the amount of data considered for the report, add a filter for December 2003. Finally, create the attribute-to-attribute qualification as outlined below.

To create an attribute-to-attribute qualification

  1. Double-click in the Report Filter pane to create a new qualification.
  2. Select Add an Attribute qualification and click OK. The Attribute Qualification dialog box opens.
  3. Find the attribute Ship Date in the Object Browser (in the Customer hierarchy) and drag it to Attribute in the Attribute Qualification dialog box.
  4. Change Qualify on to ID.
  5. Change the Operator to Greater than.
  6. Select Custom and enter the following:
    (Day@ID  + 27) 

    This adds 27 days to the Day attribute, which is the order date. The Ship Date is compared to this value.

  7. Click OK.

Execute the report, which displays as shown below.

This report is saved as Attribute to Attribute Comparison.

The first order, Order 39025, was ordered on 12/31/2002 and shipped on 1/28/2003. That is a difference of 28 days.

Attribute Qualification Prompt

An attribute qualification prompt allows you to qualify on the values of attribute elements, attribute forms, or operators when you run a report. You can create the following types of attribute qualification prompts:

  • Choose from all attributes in a hierarchy allows you to choose an attribute to qualify on when you run a report. You are, however, restricted to choosing just the attributes from the selected hierarchy. After selecting the attribute, you can qualify on the ID or create an element list filter.
  • Choose from an attribute element list allows you to apply qualifications to an attribute form. You can choose an attribute from a list of attributes and qualify on the elements of the attribute.
  • Value prompt allows you to select a single value on which to qualify, such as a date, a specific number, or a specific text string.
  • Qualify on an attribute allows you to apply qualifications to an attribute form. You can choose an attribute from a list of attributes and qualify on an attribute form.

Set qualification

A set qualification allows you to create a set based on either a metric qualification or a relationship qualification.

Set qualification: metric qualification

Metric qualifiers enable you to restrict metric values based on value, rank, or rank percentage. Metric qualifiers restrict the amount of data used to calculate the metrics on a report. For example, a store manager might want to see sales numbers for products whose current inventory levels fall below a certain level. This report will not necessarily display the inventory figures for those products.

Output level

The output level specifies the level at which the metric is calculated for the qualification. For example, if the metric qualification is Sales > 1000, Sales could mean sales per day, month, year, store, region, and so on. Creating a set qualification with an output level of store is equivalent to having a fixed list of stores, if you knew which ones met the metric qualification, in a simple attribute qualification. However, the list of stores in the qualification is generated dynamically.

The output level can be specified in several ways.

  • An attribute list allows you to specify the exact set of attributes (such as day, month, or year) to use as the output level.
  • Report level means that the output level is defined by the level of the report that contains the metric qualification. For example, if the lowest level of the report is year and the output level is set to report level, the metric is calculated for the year.
  • Metric level means that the output level is defined by the level, or dimensionality, of the metric itself, regardless of the level of the report.
  • The None selected option calculates the results at the report level if any of the following is true:
    • The metric is a compound metric.
    • The metric’s dimensionality is set to report level.
    • The metric’s dimensionality is set to nothing.

Otherwise, the metric's dimensionality is used.

If you do not select an output level, the None selected option is used by default.

Break by

This advanced function of a metric qualification allows you to choose the attribute level at which to restart counting rank or percent values for a metric. This level must be greater than or equal to the level of aggregation for the metric itself, as shown in the following example.

Given the following data:

If you specify “Break by Market,” the ranking counter is reset for each market (in descending order).

If you specify “Break By Region,” the ranking counter is reset for each region (in this example, as there is only one region, the counter is not reset).

Merge attribute qualifications

The Advanced button allows you to specify whether existing attribute qualifications should be merged into the calculation of the metric qualification. By default, this option is selected, combining the qualifications.

A metric qualification is contained in a separate pass of SQL, creating a temporary table or “mini-report.” If the qualifications are merged, attribute qualifications are added to that pass of SQL. If they are not merged, the attribute qualifications are not included in the metric qualification. They instead appear in the main SQL pass.

For example, a report shows revenue by region. The report  2002 and the metric qualification of revenue over $1 million. If the default is kept, the qualifications are merged. Only 2002 revenue is considered when the metric checks for revenue over $1 million. The report results are:

In contrast, if the qualifications are not merged, revenue is calculated for all time before the metric qualification is evaluated. However, only revenue from the year 2002 is displayed on the report. As shown in the following sample,regions are included that do not have $1 million of revenue in 2002, but do have $1 million of revenue across time.

Besides affecting the report results, merging the qualifications reduces the amount of data a calculation must process.

Metric-to-metric comparison

Metric-to-metric comparisons allow you to create reports that dynamically compare the values of two metrics. For example, you can create a report that restricts the data torevenue greater than last quarter’s revenue.

Create a report that displays the revenue for Call centers Atlanta, San Diego, and Miami for each quarter during the year 2002. To do this, create one filter that includes the call centers Atlanta, San Diego and Miami and another filter that includes the year 2002. For step-by-step directions on creating a filter, see the online help.

When you execute the report, it looks like the following:

Now, create a revenue metric that calculates the revenue for the previous quarter and save it as RevenueLastQuartermetric. Create a metric-to-metric comparison filter that uses the Revenue metric. Choose the Function as Metric Value,and Operator as Greater than. Choose the Value as Metric and browse to select the newly created metric Revenue Last Quarter. Save the filter LastQuarter. The report,when re-executed with the LastQuarter filter,now looks like the following:

Note that only those revenues whose values are greater than the revenues of the previous quarter are displayed on the report.

Set qualification: relationship qualification

Relationship qualification allows you to create a link between two attributes and place a filter on that relationship. It allows you to create a set of elements from an attribute based on its relationship with another attribute. For example, relationship filtering allows you to create a report that shows you all the stores selling Nike shoes in the Washington, DCarea or all customers who have checking accounts but no saving accounts.

You can create relationship filters using either Set qualification or Advanced qualification in the Filter Editor. Set qualification provides an interface to guide you through the process, while you must enter commands in Advanced Qualification. The syntax for the Advanced qualification is described in Advanced qualification: relationship filters.

You have the following options while creating a relationship qualification:

  • Output level is the level at which the set is calculated. You can select the attribute(s) for the output level.
  • Filter Qualification defines the input filtering criteria, that is, the relationship on which to qualify. You can select an existing filter or create a new filter.
  • Relate output level and filter qualification by is the relation between the attributes in the output level and filter qualification. The relation can be a fact, a table, or an empty filter. If the relationship is left empty, the schema is used to select the appropriate table.

For example, to create a report that shows all the stores selling Nike shoes in the Washington, DC area, you need to set the output level to Stores, the filter qualification to Nike shoes and Region, and the relation to the fact Sales.

Metric qualification prompt

A metric qualification prompt allows you to select a function, or an operator, or specify the value for a metric, when you run a report. You can create the following types of metric qualification prompts:

  • Qualify on a metric prompt allows you to qualify on a metric. You can choose a metric by specifying a single metric to use when the report is run or by specifying a search object to restrict the list of metrics from which you can choose a metric, when a report is run.
  • Metric Object prompt allows you to select one or more metrics that meet specific criteria when a report is run. For example, you could use a search to return a list of all metrics that contain a certain fact. From that list of metrics, you can then choose the metrics that you want to see on the report.
  • Value prompt allows you to select a single value on which to qualify, such as a date, a specific number, or a specific text string.

Shortcut to a report

A shortcut to a report qualification is also known as Report as filter. In the Desktop, you select Add a Shortcut to a Report to access the report as filter functionality.

The report data set of an existing report can be used as a filter for another report. Often, the result of one report is exactly what is needed as a filter in another report. Rather than create a filter that mimics the results of a report, that report itself can be used as a filter in the second report. When usedas a filter, only the report’s data definition is considered; any changes to the view definition do not influence the filter conditions.

Using reports as filters provides a more visual way of building reports and analyzing their results. It also provides a fluid transition from viewing data in a report to analyzing additional reports based on the data in the original. Report as filter is a different way to achieve the same results as a metric qualification, but it is easier to understand and create.

Reports with consolidations or custom groups cannot be used as a shortcut to a filter.

Report Object Prompt

The report object prompt allows you to choose the results of one report to be included in another report. You can define areport object prompt by specifying a search object orspecifying a predefined list of objects to choose from, while executing a report.

Shortcut to a filter

Creating a shortcut to a filter allows you to use an existing filter, or add conditions to that filter, to apply to a report. In generic terms, Filter1 contains two conditions, A and B. You can use Filter1 in another filter and add another condition, C, to it. The data must then satisfy all three conditions - A, B, and C - to be included.

For example, you are a manager in New England, responsible for stores in Boston, Providence, and Greenwich. Your project contains a filtercalled Stores in my Region, which is defined as the Boston, Providence, and Greenwich stores. The Women’s Clothing filter includes the classes Blouses and Dresses. A third filter, All Days in Dec 01, is a date range that includes all the days in the month of December, 2003.

To study December sales in your stores for women’s clothing, create a new filter. Include a shortcut to each of the three filters.

Filter Object Prompt

The filter object prompt allows you to choose the filters to be included in a report. You can define a filter object prompt by specifying a search object or specifying a predefined list of objects to choose from, while executing a report.

Advanced qualification: custom expression

Advanced qualifications allow you to create custom expressions to fit particular needs. For example, you can create a relationship filter using the custom expression area of the advanced qualification window.

Advanced qualification: relationship filters

The advanced qualification window allows you to use commands rather than an interface. To work with an interface, see Set qualification: relationship qualification.

The following syntax is required to create a relationship filter using an advanced qualification:

<relation; (filter qualification)>
  {list of output attributes}

where:

  • The relation can be a fact, a table, or an empty filter. Facts and tables are relationships between attributes in Filtering Input and Output Level. Relationships determine which table is used during SQL generation.

    If a relationship is left empty, the schema is used to select the appropriate table.

  • The filter qualification defines input filtering criteria. It may consist of an attribute qualification, a filter qualification, or a metric qualification, followed by a comma and an output level.
  • The list of output attributes is a comma-separated list of the attributes to be filtered on. If your regional settings are not set to English, the list separator must be whatever is defined in your regional settings. The output level dictates the contents of the relationship filter output set.

It is easiest to simply drag an attribute from the Object Browser into the list. If you manually enter the attribute, it must be in the format

[attributename]@ID  or
[attributename]@DESC.

For example, if you are creating a report that shows all stores selling Nike shoes in the DC area, the relationship filter syntax looks like this:

<[Fact  Sales]; [Nike Shoes, Region]>
{Stores@ID}

where Fact Sales is the table name, Nike Shoes and Region form the filter qualification, and Stores is the attribute.

Advanced qualification: apply functions

Pass-through expressions, or apply functions, in MicroStrategy are intended to provide access to the special functions or syntactic constructs that are not standard in MicroStrategy, but are found in various RDBMS platforms. There are five predefined apply functions, each belonging to a different function type:

  • ApplySimple—Single-value function
  • ApplyAgg—Group-value function
  • ApplyOLAP—OLAP function
  • ApplyComparison—Comparison function
  • ApplyLogical—Logical function

Among these five functions, ApplyComparison andApplyLogical can be used to create custom expressions for filtering.

While an Apply function can be used wherever the function group it belongs to is applied, you should NOT use any Apply functions when standard MicroStrategy functions can be used to achieve the goal. This is because using Apply functions effectively bypasses the validations and other benefits of the product. Therefore, use it ONLY when support does not exist in the MicroStrategy product and submit an enhancement request so that MicroStrategy can evaluate your needs for inclusion in a future product release.

Advanced qualification: joint element list

Joint element lists allow you to choose attribute elements from different attributes to filter the report result set. Unlike attribute qualifications, joint element lists also allow you to join attribute elements and then filter on that attribute result set. In other words, you can select specific element combinations, such as quarter and category. As in the report sample included below, you can filter on electronics in Q1 2003 and music in Q3 2003.

Joint element list example

This example refers to information saved in the MicroStrategy Tutorial.

Before creating a joint element list, you must ensure that the Advanced Qualification option is displayed on the Filter Editor. From the Desktop, complete the following steps:

  1. Select My Preferences from the Tools menu.
  2. Choose the Editors tab.
  3. Click Filter Options.
  4. Select Show advanced qualification, if it is not already selected.
  5. Click OK to return to the Desktop.

Open the Basic Report. Note that Leanne Sawyer’s revenue is $316,786. This is sales for all time and all categories. You need to see revenue for specific quarter and category combinations, for example, electronics in Q1 2003 and music in Q3 2003. To do this, switch to Design View and create a joint element list, as described below.

To create a joint element list

  1. Double-click in the Report Filter pane to add a new qualification.
  2. Select Add an Advanced Qualification and click OK. The Advanced Qualification pane opens.
  3. Select Joint Element List from the Option pull-down list.
  4. Select Category and Quarter from the Available attributes list and click > to add them to the Selected attributes list.
  5. Click the Add icon to the right of the Element list. The first value in each attribute is added to the list.
  6. Click the Modify icon to the right of the Element list. The Select Element List dialog box opens.
  7. Double-click Electronics to change the category.
  8. Select Quarter from the Available Elements drop-down list.
  9. Double-click Q1 03 to change the Quarter.
  10. Click OK to return to the Advanced Qualifcations dialog box.
  11. Click the Add icon to add another element. Again, the first value in each attribute is added by default.
  12. Select the new element and then repeat steps 6 through 10, this time changing Category to Music and Quarter to Q3 03.
  13. Click OK to save the new qualification.

Execute the report. The results are displayed below:

This report is saved as Joint Element List.

Notice that Sawyer’s revenue is now only $18,901. The decreased revenue reflects the qualification, since only sales for electronics in the first quarter of 2003 and the sales for music in the third quarter of 2003 are included in the metriccalculations.

Searches relevant to you
Top