Tag Archives: pass-through

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!

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)

201408

201402

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;

pic1

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

pic2

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

pic1

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!