MicroStrategy ONE

Creating consolidation element expressions

A consolidation element expression contains at least one attribute element, such as Region = Northeast. It is the formula defining the consolidation element and therefore determining what is included in the report.

An expression can contain any of the following:

  • Elements from the same attribute, such as two regions

  • Attribute elements from different levels, such as region and country

  • Elements from unrelated attributes, such as country and year

  • Existing elements, such as the ratio of spring and summer sales to fall and winter sales, where spring, summer, fall, and winter are existing consolidation elements in the same consolidation

  • Elements imported from another consolidation

Multiple elements are combined with simple mathematical expressions. For example, an expression can add attribute elements, such as combining June, July, and August for a Summer consolidation element:

({Month of Year=June} + {Month of

Using AND as an operator

A consolidation element can also contain the logical operator AND. For example, you can create a Summer of 2023 consolidation element: (June AND 2023) + (July AND 2023) + (August AND 2023). Taken one step further, you could create a consolidation that shows the difference between Summer 2023 sales and Summer 2022 sales: [(June AND 2023) + (July AND 2023) + (August AND 2023)] – [(June AND 2022) + (July AND 2022) + (August AND 2022)].

You cannot type AND into the expression. You must drag and drop an attribute element into an existing expression to trigger the AND operator. The second attribute element cannot belong to the same attribute as the first, otherwise a plus sign (+) is used. For more details, see Creating consolidation element expressions.

For more details and examples, see the Consolidation elements section of the Custom Groups and Consolidations chapter of the Advanced Reporting Help.

Prerequisite

This procedure assumes you have already created a consolidation and added a consolidation element to it. For instructions, see Creating consolidations.

To create a consolidation element expression

  1. If the Consolidation Editor is not open, access it. (How?)

  2. In the Consolidation Editor, select the consolidation element. The Selected element pane is activated.

  3. Do one of the following:

    • From the Object Browser, drag and drop an attribute element into the Selected element pane.
    • From the Elements for this consolidation pane, drag and drop a consolidation element into the Selected element pane.

  4. To add another attribute or consolidation element using the + or AND operators, drag and drop the element from the Object Browser into the Selected element pane.

    • If the elements are from the same attribute, they are added together. That is, the + operator is used.

    • If the elements are from different attributes (such as Year and Month of Year), the AND operator is used. If you want to use + instead, right-click the element in the Object Browser, then drag and drop it into the Selected element pane. From the pop-up menu, select Add to Selection.

    • The new attribute is automatically placed after the first one, as in {Category=Books AND or ({Category=Books} + {.

  5. To add another attribute or consolidation element using the other mathematical operators (that is, -, *, /), complete the following steps:

    • Click the desired operator on the operator bar.

    • Drag and drop the attribute or consolidation element from the Object Browser into the Selected element pane.

  6. To overwrite an attribute element in the expression with another element, complete the following steps:

    • From the Object Browser, right-click an attribute element, then drag and drop it into the Selected element pane.

    • Select Replace Selection from the pop-up menu.

      Depending on the expression, the entire expression may be overwritten. In this case, the expression itself rather than an element is highlighted in the Selected element pane.

  7. To perform row level math, use operators to combine consolidation elements that have already been defined. If the elements are defined in this consolidation, simply drag and drop as described above. If they are defined in another consolidation, you must import them first.

    Importing creates new consolidation elements; it does not add them to an existing element.

  8. Continue adding additional operators and attribute/consolidation elements, as described above, to complete the expression.

    If the expression contains multiple elements connected by AND, and you add an element from an attribute already present, the existing element is overwritten. For example, you drag and drop the supplier Scribner into the expression{. The element Bantam Books is replaced by Scribner.

  9. Click Validate to make sure the expression is valid.

  10. You can add more consolidation elements to complete the consolidation. (How?)

  11. Save the consolidation. (How?)

    You cannot save a consolidation that contains an empty expression. Delete any element containing an empty expression before attempting to save the consolidation.