It all eventually boils down to a bottom line. With MicroStrategy, you have the option to add several totals and subtotals and have fairly detailed control over them. In this recipe, we'll see how to add grant totals, subtotals, and custom totals.
Say that you have a report like the one in the image:
You can use the 18 Dynamic Aggregation by Country and Product report we created in the previous recipe and modify it. If you want to simply add a total on the last line, you'd select the Data | Grand Totals menu or press F11; if you want to add subtotals by Product Category, read on.
We want to add a subtotal at the end of Bikes, Clothing, and Accessories, which is like saying we want a total of all the countries where we sold bikes, all the countries where we sold clothing, and so on.
- Click on the Data | Subtotals menu to bring up the Subtotal dialog box:
- With Total selected, click on Advanced.
- In the lower part there is a group named Applied levels with the first option By position selected. Check the second option: Across level, and in the list that appears check the Country box (remember we want the total of the countries belonging to bikes).
- Click on OK and then on OK again. Now you have the subtotals grouping the Country attribute at the end of every category, and no total at the bottom.
- To add both the subtotals and the grand total, click on the Data | Subtotals menu.
- Click on the Advanced button and check the Product Category box. Then click on OK and then on OK.
Totals can be added by position (rows, columns, and page) or by level. If you add them by position, they aggregate the entire report and display only one number. If you want a total to display only a partial aggregation, you can add them by level. When totaling by level, you check the box next to the attribute "at the end of which" you want the number to appear. If you check the Country attribute the subtotal will appear at the end of the countries belonging to the attribute on the left, in this case, Product Category. At first, this may be misleading: but after a couple of reports all the totals will fall into place.
Some people like to see Bikes Total, Clothing Total, and so on; this is very useful in case of long reports where you cannot see the element of the attribute on the left. To do this, we must create what is called a custom subtotal:
- From the Data menu, select Subtotals; with the Total line selected, click on the Advanced button.
- Uncheck the Country attribute and click on OK to confirm. Now click again on Advanced and hit the New button.
- In the Custom Subtotal Properties window, go to the first textbox, remove
New Custom Subtotaland write#1 Total. - Hit OK and now click on the line that reads #1 Total (it has a different icon) to select it.
- When the Applied levels group appears, click on the Across level radio button and check the Country box.
- Click on OK and then on OK again.
- See that the subtotals now have the name of the category element they belong to.
There is another very useful setting in the Subtotals window. If you click on the Display Options tab, you will be able to change the position of the bottom line and set it to be the top line; in case you prefer to see the subtotals at the beginning of each group instead of the end. See the following screen capture:
