MicroStrategy ONE

Consolidation Elements

Consolidation elements are attribute elements that define the consolidation. Consolidation elements can also be an expression of attribute elements that make up a consolidation. They can be defined from any of the following:

  • Elements from the same attribute, such as two cities
  • Attribute elements from different levels of the same hierarchy, such as Region and Call Center in the Geography hierarchy
  • Elements from attributes in different hierarchies, such as Country (from the Geography hierarchy) and Year (from the Time hierarchy)
  • Existing consolidation elements, such as the ratio of Spring and Summer sales to Fall and Winter sales
  • Elements from any other consolidation in the project (elements imported from an existing consolidation into another one)

You can combine the elements with simple mathematical expressions. For example, you can have an expression that adds attribute elements together, such as combining June, July, and August to get a Summer consolidation element. A consolidation element can also contain the logical operator AND.

The following example demonstrates the use of mathematical expressions (addition and subtraction) and the AND operator.

Example of AND Used in a Consolidation Element Expression

You must report on the difference in revenues between the USA and Web for the winter of 2022. Create the following consolidation elements:

  • USA: Winter 2022

    ({Month=Jan 22 AND Country=USA} + {Month=Feb 22 AND Country=USA} + {Month=Mar 22 AND Country=USA})

  • Web: Winter 2022

    ({Month=Jan 22 AND Country=Web} + {Month=Feb 22 AND Country=Web} + {Month=Mar 22 AND Country=Web})

You cannot type AND into the expression. You must drag and drop an attribute element into the expression to trigger the AND operator. For more details, see the online help.

Create a consolidation element that uses the above elements to calculate the difference:

  • USA - Web: Winter 2022

    ([USA: Winter 2022] - [Web: Winter 2022])

Finally, create a report with this consolidation and the Revenue metric. The report looks like the following:

Elements from the Same Attribute

A consolidation can contain elements from the same attribute, such as (March) and (April), both elements of the attribute Month of Year. With reference to the previous example, consolidation elements allow you to expand the consolidation to see the values for each month.

For example, using elements from the same attribute, you can modify the report result set as follows by adding the following three elements to the consolidation:

  • Element 1 (March)
    Month of Year=March
  • Element 2 (April)
    Month of Year=April

  • Element 3 (March-April)

    {March}-{April}

With the use of consolidation elements, the report can now display the following.

A consolidation can contain any expression on the pairs of elements, such as (March - April). Using another example, an element expression can also be [DC, 2022] / [DC, 2023].

Elements from Different Levels of the Same Hierarchy

A consolidation can contain elements from different levels within the same hierarchy, such as Item and Subcategory from the Products hierarchy.

For example, you may want to compare the contribution of different items to the Subcategory sales. Your consolidation, for the items Chocolate Roses and Chocolate Spoons, looks like:

  • Element 1 (Roses percent)
    [{Item=Chocolate Roses} / {Subcategory=Chocolate}]

  • Element 2 (Spoons percent)

    [{Item=Chocolate Spoons} / {Subcategory=Chocolate}]

With the use of consolidation elements, the report displays the following.

Elements from Attributes in Different Hierarchies

A consolidation element can contain elements from attributes in different hierarchies.

For example, you can calculate the difference between two different regions for a particular month. For the months March and April, the consolidation could contain the following elements:

  • Element 1 (March Southeast - Southwest)
    [Month of Year=March AND Region=South-East]
    - [Month of Year=March AND Region=South-West]
  • Element 2 (April Southeast - Southwest)
    [Month of Year=April AND Region=South-East]
    - [Month of Year=April AND Region=South-West]

The report now appears as follows:

Existing Elements

Using existing elements allows you to perform row level math, as described previously. For an example, see Perform Row Level Math.

Importing Elements from Other Consolidations

You can import consolidation elements from an existing consolidation. When a consolidation element is imported, a new consolidation element is created and embedded into the consolidation.