Tag Archives: metrics

Converting seconds to time format

Hello there,

Today let’s talk about a quick tip: how to transform seconds to hh:mm or even hh:mm:ss.

You just have to:

  1. Create a new metric using your seconds metric and divide it by 86400.

Example of the formula would be: [metric with seconds] / 86400

2. Format the number to be like [h]:mm or [h]:mm:ss

 

That’s it. You will now see your seconds in a time format.

Hope it helps!

God bless you!

Number format in MicroStrategy

Hello there!

My friend Diogo Martins, told me a simple but powerful custom formatting to numbers that can help you so much!

Here is the tip:

Think that you have a Profit metric with a number like this: 1.304.141

And you want to divide that by 1000.

You just have to:

  1. Format the value and go to Custom
  2. Add a format like: 0,

Capturar

And now you will get your number like: 1304

The trick is the comma (,) at the end.

If you add 1 comma you will dividing by 1.000

If you add 2 commas you will dividing by 1.000.000

And so on…

So, just add commas after the 0 (for number) and you will format your number in a much better way than dividing it in your metric.

Hope it helps!

God bless you!

Adaptive Metrics

Hello there!

Today let’s talk about how to create different aggregation formula for each fact table used in a fact in Microstrategy.

Let’s say that we need to create a SUM(value) for Fact A and a COUNT(value) for Fact B. When we get data from Fact A, Microstrategy must SUM, when in Fact B, Microstrategy must COUNT.

To do that you just have to:

1. Create a new Fact;

2. Create a new expression with:

ApplySimple(“Count #0”, FIELD1) 

Only use Fact B for source tables.

3. Create a new expression with:

ApplySimple(“Sum #0”, FIELD2)

Only use Fact A for source tables.

pic

Save your Fact as: Fact_Test

4. Create a new Metric with:

ApplyAgg(“#0”, Fact_Test)

pic

5. Update your Schema.

 

That’s it.

Hope it helps

 

God bless you!

How to show the parameters set in metrics

Hello there!

Today I’m going to teach you a quick tip that can help you see the parameters that were set in a metric.

The default behavior of MicroStrategy is the hide all parameters set for your metrics. it’s much better to see everything that you are using.

To do that you just have to:

1. Open Developer/Desktop and edit a metric

2. Go to Views -> Show Function Parameters

pic

Now you will see every parameter that you metric have.

pic

 

Hope it helps!

God bless you!

Changing Metric Column Alias Name

Hello there!

Today let’s talk about changing the metric alias name in the SQL that MicroStrategy generates.

Sometimes you need to debug the query and you see something like this:

select name_pu as name, sum(salary) as #MAS01MQW from Fact_Table group by name_pu

If you have a lot of metric inside your report, it is very hard to know where is a specific metric in the query.

You can change that machine metric alias name to a human metric alias name using Metric Column Name.

To do that, you just have to:

1. Edit your metric;

2. Go to Tools;

3. Go to Advanced Settings;

4. Choose “Metric Column Option

pic2

Then, you just have to type a different name in the “Column Name used in table SQL creation“, like the image below:

pic1

Now your SQL is going to be something like this:

select name_pu as name, sum(salary) as Name_of_Metric from Fact_Table group by name_pu

That’s it.

Hope it helps!

God bless you!

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

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.

Hide permanently the “Metrics” column for MicroStrategy Web

Hello there !

Today let’s talk about removing the “Metric” column forever in MicroStrategy Web !!!

I always have to remove the extra column (web only) for every grid that I create in MicroStrategy, because I just don’t like seeing that there. But sometimes is very boring doing that everytime that I create a new report or grid in a document. So, if you are like me that simply don’t like that extra column, here is a very good tip to remove it forever from your Microstrategy Web visualization.

To do that you just have to:

1. Make sure the language that you are using in your projects in MicroStrategy web. To see which language your project is setup, just go enter in a project, go to Web Preferences -> Project Defaults -> Language.

pic1 pic2

2. Go to the folder where your MicroStrategy Web is installer. If you use IIS, go to: C:\Program Files (x86)\MicroStrategy\Web ASPx\WEB-INF\classes\resources or C:\Program Files\MicroStrategy\Web ASPx\WEB-INF\classes\resources. If you use Tomcat, C:\Program Files\Apache Software Foundation\Tomcat X\webapps\MicroStrategy\WEB-INF\classes\resources (where X is the version of your installed Tomcat ).

3. Edit the MessagesBundle specific to the language that you use in your MicroStrategy Web. In my case I use English, so I’m going to edit the MessagesBundle_en_US.properties file. If I was using Spanish, for example, I just have to edit the MessagesBundle_es.properties file, and so on.

pic3

4. Open the file in notepad (or other text edit of your choice) and find the word “Metrics”.

pic4

5. In version 9.4.1 Hotfix 3, just delete the word “metrics” from the “mstrWeb.962” content, andadd blank a space.

pic5

6. After that, restart MicroStrategy Web. If you use IIS, click in: Start, click Run type IISReset, and then click OK. If Tomcat, go to Tomcat Monitor and restart your web server.

7. Now, create a new report and you will see, when you execute it, that the “Metrics” column has just gone forever!!

pic6

Enjoy it! Hope it helps!

God bless you.