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!

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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