This type of object is useful, fast, and it is calculated on the Intelligent Server side so it doesn't put load on the database. As the name suggests, it serves the purpose of consolidating row values. This means not only adding one row to another, but also doing row-to-row calculations.
Remember from previous chapters that there are multiple London or Paris elements in the City attribute, the schema was designed this way to respond to a specific requirement: allow the reports to display separate ZIP codes in large cities. Now we want to have a look at how much we sell in the entire city of London, but we don't have such an element in the City attribute.
So, we create our own virtual attribute to show, in one single row, a consolidated value for all the elements that represent the city of London and another row with the values for the rest of the UK.
We will create a consolidation in My Objects folder:
- In MicroStrategy Desktop application, open My Objects folder, right-click on the empty area on the right and select New | Consolidation from the context menu.
- Leave the default Empty Consolidation and click on OK.
- We are in the Consolidation Editor, very similar to other editors we used already, maximize this window to have sufficient space and click on the text element Click here to add new consolidation element.
- Change the text New Derived Element to
London Area, the red row below says Empty Expression. - In the Object Browser pane, double-click on the Geography hierarchy to explore the attributes that belong to it.
- Double-click on the Country attribute, then on the United Kingdom element and on the City attribute.
- Now you see a list of city elements, you can sort them by clicking on the DESC header. Use Shift + click to select all the London elements (there should be 14 of them) and drag them onto the expression text area in the lower-right part of the Consolidation Editor.
- Move to the upper part and hit Click here to add new consolidation element.
- Rename this element as
Rest of UK. - Now select all elements in the city list from Abingdon to Liverpool and drag them to the expression pane. Repeat the step with all elements from Maidenhead to York YO15. By default, MicroStrategy adds a + sign between elements.
- Click on Save and Close, name it
UK Areas. Let's put it to the test. - Go to My Reports folder and create a blank new one.
- Add Sum SalesAmount from FactResellerSales to columns; now click on My Personal Objects shortcut and open My Objects folder.
- Add to rows the recently created consolidation and run the report.
- Save the report as
50 UK Salesbut do not close it. - From the Data menu, click on Grand Totals—what happens? …Ouch! Nothing, no total here. By default, consolidations do not allow subtotals.
- Save and close the report, move to My Objects folder and double-click on UK Areas to edit.
- Right-click on any of the elements, for example, Rest of UK, in the context menu there is a Subtotals option, click on Enabled (see the following screen capture):
- Save and close the consolidation and re-run the report.
- Now the Total row shows 4279009.
If you look at the SQL view, you'll see what happens behind the scenes:
where a12.GeographyKey in (244, 245, 246, 247, 248, 249, […]group by a12.GeographyKey
MicroStrategy generates a query with all the UK elements in the WHERE clause, grouping by GeographyKey, which is the ID of the City attribute; the result is retrieved, then the consolidation is performed by the Intelligence Server (the part that does the calculation is called Analytical Engine as opposed to the SQL Engine, which generates the SELECT).
See the last sentence of the SQL view:
[Analytical engine calculation steps: 1. Calculate consolidation: <UK Areas> 2. Perform cross-tabbing ]
This means that the RDBMS knows nothing about UK Areas, we created our virtual attribute without having to modify the database schema.
One drawback of this technique is that you need to know your elements beforehand; you cannot dynamically select all London elements without prior knowledge of how many London(s) are there. See the custom groups later in the chapter for an alternative.
We want to add more rows to the grid, and look at how London compares to the rest of the UK. Consolidations allow us to perform this kind of inter-row operations, such as London / Rest of UK. Follow these steps:
- Close the report and open the UK Areas consolidation, add a new consolidation element.
- Give the new element the name
London / UK. - Drag the first consolidation element, named London Area, onto the lower part of the screen, in the Enter your expression here area. The resulting expression is [London Area].
- Click on the division button labeled with a forward slash, which is right above the expression and then drag the second consolidation element named Rest of UK and drop it after the / so that the resulting expression is: ([London Area] / [Rest of UK]).
- Click on Save and Close. Right-click on the report named 50 UK Sales, select Purge Report Cache and run the report again. What a surprise, the new row London / UK shows 1 and the total row 4279010. Hmmm… something's not working as expected.
What is happening is that we have a ratio (London / UK), which should be formatted as a percentage, but it's not; since we are showing no decimals, the number is rounded to 1.
Then in the Total row we have a sum of all the values in the consolidation, that's why the number shown is 4279010 instead of 4279009.
This is a very common mistake when using this type of object, and we should always keep in mind that, if we want a total here, we should create another element with the sum of the appropriate values only, in this case:
([London Area] + [Rest of UK])
One extremely useful feature of consolidations is that we can give different formats to different rows. It is worth noting that the format will be used for any metric used in conjunction with the consolidation (in the example, we are applying it to a SalesAmount metric, but could be any other metric); it is up to the developer of the report to use it appropriately. So, let's change that format and add a total:
- Edit the consolidation.
- Right-click on London / UK, select Formatting properties | Element Values....
- In the Format Cells dialog, under the Number tab, choose Percent, select the first option from Negative numbers and click on OK.
- Add a new consolidation element and name it
UK Total. - Use the formula
([London Area] + [Rest of UK]), see image:
- Click on Save and Close, and re-run the report.
- To disable the Total line, go to Data menu and then Subtotals and click on the button Clear All. Now you have a proper result.