Custom Subtotal over attribute elements

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


2. Create a new Subtotal with this formula:


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:


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


4. Create a new report using these objects below:


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


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



That’s it.

Hope it helps.


God bless you.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s