Category Archives: Reports

Adding a prompt to a filter based on a metric

Hello there,

If you need to add a prompt to a filter based on a metric, you just need to do this:

  1. Create a filter using a metric in a report;
  2. In the parameters, just use Custom for the expression;
  3. Insert an Applysimple function. Ex.:

ApplySimple(“Dateadd(Month, Datediff(Month, 0, DATEADD(m, -#0, current_timestamp)), 0)”,? Months)

4.   In this example, I’m using a Months prompt to get an integer answer from the user to fill the #0 wildcard.

The trick here is the put the prompt name as: ? NAME_OF_THE_PROMPT

Hope it helps.

God bless you.

Report Performance Tips

Hello there,

I was searching for report performance and I found a nice post in this url:

The user Omar V wrote some tips to help improving the report performance that you should try and see if those VLDB settings works for you.

Hope it helps!

God bless you

Search string in SQL View

Hello there!

I don’t know if you had to search for a specific string in the SQL view and had to copy and paste in a text editor to search for it.

There is a shortcut that you can use to search in the SQL View.

Just go to the SQL View and hold Ctrl + i.

That’s it. Now you can search in SQL View.


God bless you!

Parallel Query Execution

Hello there!

There is a excellent vldb option to execute in parallel independent sql passes.So. if you have more than one pass in your SQL and those aren’t dependent, you can instruct Microstrategy to execute them separately. Microstrategy will open DB connections to execute those SQLs.


You have a query with some passes like:





If Pass1 isn’t dependent of Pass2, Microstrategy will execute them separately.

To do that, you just have to:

1. Open your report;

2. Click on Data -> VLDB Properties

3. Select Query Optimization -> Parallel Query Execution


Justchoose one of those 2 “Enable” options. Just read it and see which one is better for you.

Hope it helps!

God bless you!

Removing Auto Apply changes in Page-by

Hello there!

Today I’m going to show you how to remove the auto apply changes made in a Page-by in a report for Web.

When you put an attribute in a page-by section of your report, and changes to a different element, it will automatically apply that change.

To change that behavior inserting an apply button, you just have to:

1. Enter in you project in MicroStrategy Web;

2. Go to Preferences;


3. Go to Project Defaults -> Grid Display -> Uncheck “Automatic page-by”.


Now when you put an attribute in Page-by you will see a button like this:


Hope it helps!

God bless you!

Performance in Reports (Part III)

Hello there!

Today I’m going to talk about a simple technique to reduce the amount of cache used in your project.

As you may know, Microstrategy is able to cache the reports to run it much faster than normal. It’s stored in memory ram and disk. So, it reduces the amount of space of your Hard Disk and Memory RAM of your Intelligence Server machine.

The idea is pretty simple:

Create a single report to be used in more than one document.

For example, if you have to bring: category and subcategory in one report and region and city in another one, you can create 1 report that contains all of those attributes and use this report for a dashboard that contains region and city and another one that contains category and subcategory.

So, always think about creating 1 report to cover more dashboards instead of creating one report for each document.

Hope it helps.

God bless you!

Performance in Reports (Part II)

Hello there!

Today I’m going to talk about the second part of the series “Performance in Reports”. If you would like to read the first part just click on this link “Report Performance (Part I)“.

I believe that nowadays I always see the query that Microstrategy creates when creating a new report. It recommend you to do so.

Sometimes you see that the query delays so much, and you don’t know what is going on.

Let’s believe that you have created all indexes needed to perform that query and still you want to improve the performance. Have you already heard about aggregated fact table?

Let’s analyse this simple database below:


As you can see, we have 2 hierarchies and 1 fact table here. Inside hierarchy Employee we have two dimension tables: employee and language. Two tables in geography hierarchy: country and continent. The fact Salary was created using the lowest level of the hierarchies: employee_id and country_id. That facts are commonly called Base Facts, just because it uses the base (lowest level) of your hierarchies.

So, if you want to display language_DESC and continent_DESC in a report? Microstrategy needs a few more steps to accomplish that, as you can see below:

select language_DESC, continent_DESC from Salary_fact a1
inner join employee a2 on (a1.employee_ID = a2.employee_ID)
inner join language a3 on (a1.language_ID = a3.language_ID)
inner join country a4 on (country_ID = a1.country_ID)
inner join continent a5 on (a5.continent_ID = a1.continent_ID)

It needs to go to Employee and Country tables because Salary doesn’t have a bridge to language and continent, only to employee and country.

Now, look at this datagram below:


I have added another fact called Salary_Fact_Agg that contains language_ID and continent_ID. Now, Microstrategy reduce the steps to get those columns.

select language_DESC, continent_DESC from Salary_fact_Agg a1
inner join language a2 on (a1.language_ID = a2.language_ID)
inner join continent a3 on (a1.continent_ID = a3.continent_ID)

That will make your query run faster than the first one.If you reduce the amount of joins that is required to get the data that you desired, you will increase performance of your report. So, if you change the granularity of your facts aggregating them in a higher level, you create an aggregated fact table.


  1. Performance – The amount of joins is reduced and your query is executed faster.


  1. Database size – Creating more facts will increase the size of your database;
  2. ETL complexity – ETL needs to maintain another fact;

An advice:

Only create aggregated fact table when you notice that you have a lot of queries that are using a higher level dimension than the base facts that you have and the performance isn’t that good. Don’t agg facts at the beginning of your data warehouse creation. You have to test the base facts first and see if it’s really required an aggregate fact table to resolve performance problems.


Hope it helps.

God bless you.