Category Archives: Subtotals

Custom subtotal names

Leave a reply

Hello there!

Today let’s talk about a good tip of how to change and place wildcards in custom subtotal names.

To create a custom subtotal, you just have to:

1. Edit a report;

2. Click on Data -> Subtotal;

pic

3. Click on Advanced button and click on the New button;

pic

pic

4. Change the name of the subtotal. You add one of these wildcards:

#A - The name of the attribute where the subtotal is displayed
#P - The name of the attribute at the left or above where the subtotal is displayed  
#0 - All forms of the parent's element
#1 - The first attribute form of the parent's attribute from left to the right from top to the bottom
#2 - The second attribute form of the parent's attribute from left to the right from top to the bottom
#3 - The third attribute form of the parent's attribute from left to the right from top to the bottom
#4 - The fourth attribute form of the parent's attribute from left to the right from top to the bottom

One example would be something like:

Subtotal of #1

Hope it helps!

God bless you!

Custom Subtotal over attribute elements

2 Replies

Hello there!

Today let’s talk about creating subtotal over attribute elements.

Let’s suppose I have 2 attributes and 1 metric in a report template: Year (in columns) , Category (in rows) and Profit(in columns). Let’s say that I need to calculate the growth of Profit among 2011 and 2012 for each category. How can I do that?

One thing that could be done is to create 2 metrics and insert a conditional “2011” for the first metric and “2012” for the second metric. Using this procedure I will have some problems to calculate the growth over different years. I would have to create another metrics with another filters for each different year.

That isn’t a good approach. Instead, you can create a subtotal that gets the first year and the last year automatically and then calculates that growth for me.

To do that you have to:

1. Go to Project Objects -> Subtotals

pic1

2. Create a new Subtotal with this formula:

pic2

Here I’m using the Last and First function to get the first year and the last year of my report. The trick here is to use the SortBy = Year. If you don’t use this SortBy, MicroStrategy won’t get the correct value for First and Last. The “@” indicates the place where Profit Metric will be inserted.

If you can’t see the functions parameters, just go to View ->and click on “Show Functions Parameters”, just like the image below:

pic8

3. Go to Profit metric and include this subtotal to the available subtotals:

pic3

4. Create a new report using these objects below:

pic4

5. Go to Data-> Subtotals and add check the Growth subtotal.

pic5

6. Now when you run your report, MicroStrategy will divide 2012/2011 just like we wanted.

pic6

 

That’s it.

Hope it helps.

 

God bless you.