In this exercise, you will learn to create a Consolidation:
- Browse to the MicroStrategy TutorialPublic ObjectsReportsMy Exercises folder.
- Right-click | New | Consolidation.
- Once the editor is open, click on the area labeled Click here to add new consolidation element.
- Name the First Element South Region.
- In the Object Browser, locate the and double-click Geography Hierarchy. Double-click on Region Attribute. You will see the Region's elements.
- Holding the Control key, select South, Southeast, and Southwest | Drag and drop them into the expression window. You will see a new expression created: ({Region=South} + {Region=Southeast} + {Region=Southwest}).
You can also use a different arithmetic operator. The expression indicates how all Metrics will behave when aggregated to that new Consolidation element. In the previous scenario, it will add or consolidate whichever Metric is on the Template for those Regions.
- Create another Consolidation Element | Name it North Central Region| create the following expression: ({Region=Central} + {Region=Northeast} + {Region=Northwest}).
- Create another Consolidation Element | Name it Internet Channel | create the following expression: {Region=Web}.
- Create one last Consolidation Element | Name it Foreign Region | create the following expression: ({Region=Canada} + {Region=England} + {Region=France} + {Region=Germany}).
Your final Consolidation should look like this:
How the final consolidation should look
- Save and Close | Name it Custom Regions.
- Create a new Report | Add a Custom Regions and Revenue Metric | Execute the Report; it should look like this (note that there is no Revenue for Foreign Regions):
Custom Regions and Revenue
- While in the executed Report, browse to Schema ObjectsAttributesGeography | Add the Region Attribute to the right of the Custom Regions Consolidation. You should now see how each Custom Region brakes into the corresponding Region.
MicroStrategy does not validate whether the Consolidation created uses the same Attribute elements on all the Consolidation elements (for example, mixing Regions and Products will make no sense), or if one is missing or repeated (for example, adding Southeast to both the North and South Regions). It is the responsibility of the developer to make sure the Consolidation is created by conforming to the business logic.