Category Archives: Filters

In list import in report

Hello there!

Today let’s talk about a simple but good tip: Import a list to filter a report!

Let’s suppose that you have to filter 100 elements in a report and you have those elements in a txt, csv or .xls file. To import those values you just have to:

1- Open report filter and on qualify on field choose the specific form attribute that you want to filter;

pic

2- In Operator choose In List;

pic

3- Click on Import… button and select your input file. The elements should appears like this:

pic

 

That’s it! Now you can import a list that contains all elements to filter a report.

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!