Attribute qualification - Microstrategy

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.

Attribute element list qualification example

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.

Attribute form qualification example

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.

Attribute-to-attribute qualification example

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.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Microstrategy Topics