Tag Archives: filters


Hello there,

I’ve been trying to drive people in my team to think and not just do what they are asked to do. I want to do exactly the same here in this post. Show you that think is the best way.

Today, people are driven to do the tasks and sometimes they don’t find space to think on what they are doing neither better ways to do it.

When you have to create several metrics, what do you normally do? Probably you check if the project has the facts and attributes and you start building the metrics as there were no tomorrow.

Let’s think about this metric creation task. Have you ever thought that we could create metrics using the advanced metric option in MSTR Developer and that could save a huge amount of time?

Last week, I had a task to create approximately 80 metrics and I realized that I could do it in a better way. See that I used the word realized? Instead of just start doing the task, I first stopped for a moment, and thought in better ways to perform those creations. The metrics needed filters, a count function and the value format to be as a percentage.

1. Filters: do you know that filters can be created using command manager? Yes, we can do it with CM. Here is the script:

CREATE FILTER “NameOfTheFilter ” IN FOLDER “\Public Objects\Filters” EXPRESSION “Attribute@DESC= ^” value to be filtered ^” ; ON PROJECT “MicroStrategy Tutorial”;

With this script, you will save a lot of time right clicking and left clicking your mouse to create and save a new filter. Using the advanced filter in Developer, you can add the metrics/filters/transformations and MicroStrategy will create the combination of them with just a single click of your mouse. This feature will let you go home earlier that day.

2. Base formula: Think about creating a base formula for your metrics. This way you won’t need to create a new expression every time that you have to create a new metric.

In java, we are driven to reuse the classes and method. It’s not a good idea to create new line of codes if you are have them. Reuse, don’t rewrite the same piece of code multiple times. That’s the reason why Jave has methods.

The same concept applies to MicroStrategy. We are good to say that with MicroStrategy we can reuse objects. That’s not stands true if you don’t use it.

Let’s think about using base formulas to create the metrics so they will be consistent and we can reuse the formulas in multiple objects.

3. Find and replace: do you know that we can format a bulk of metrics with just a few clicks? If you go to Tools -> Find and Replace in Developer, you will see that we can easily change the format of the metrics. This way, you will also save a LOT OF TIME.

This is just an example that confirms that think and do is better than just do.

I hope I’ve encouraged you to think…

God bless you.

Creating filter for an incomplete date field using pass-through functions

Hello there!

Today let’s talk about how to create a dynamic filter for an incomplete date field in a report using DB functions in MicroStrategy.

DB functions or pass-through functions are MicroStrategy functions that allows you to customize the query that MicroStrategy creates for the reports.

Those are very powerful functions. With them, you can write specific SQL for your database. So, you can input a SQL syntax in a filter or other objects, like: attributes and metrics.

Here is a case:

You want to get the last 4 months of Sales. Suppose you have a date field like this:

201409 (yyyyMM)



So, you don’t have the complete date, like: 01-01-2011, for example.

How can you get the last 4 months dynamically?

To do that you can use an ApplySimple DB Function to filter your report.

As I told you before, you write a specific Query for your DB, so, if you are using SQL Server for your Data Mart or DW , the syntax should be different from a Oracle DB, for example. In my case, I’m using SQL Server to write this code.

For SQL Server the syntax should be:

ApplySimple("case when (right((CONVERT(integer, #0) - 4),2) > 12 or right((CONVERT(integer, #0) - 4),2) = 0) then case when right(CONVERT(integer, #0) - 4,2) = 0 then left(CONVERT(integer, #0) - 100,4) + '' + '12' else  left(CONVERT(integer, #0) - 100,4) + '' + right(CONVERT(integer, #0) - 4,2) - 88 end else (CONVERT(integer, #0) - 4) end"; Date@ID)

Where #0 refers to the first attribute after the “;”. In this case, Date@ID. The @ID is referring to ID attribute form of Date attribute. You use a different attribute form if you want.

Note that this “;” is for Brazil, if you live in EUA, you should use your regional separator, that should be a “,”.

To insert this in your report, you just have to:

1. Create a blank report;

2. Add your Date attribute (at least) to your report;


3. Create a new Report Filter using your Date attribute and Qualify on ID. Use Custom below “Operator”;


4. Change the operator to Greater than and insert the ApplySimple function as written above;


5. Click ok and run your report.


That’s it. Now you can the last 4 months dynamically. If you want to get a different range of months, just change all  – 4 value to another one. For example, for last 5 months you should do:


ApplySimple("case when (right((CONVERT(integer, #0) - 5),2) > 12 or right((CONVERT(integer, #0) - 5),2) = 0) then case when right(CONVERT(integer, #0) - 5,2) = 0 then left(CONVERT(integer, #0) - 100,4) + '' + '12' else  left(CONVERT(integer, #0) - 100,4) + '' + right(CONVERT(integer, #0) - 5,2) - 88 end else (CONVERT(integer, #0) - 5) end"; Date@ID)

Hope it helps.


God bless you!