Microstrategy Blog

Sometimes it’s easier to push the functions to the database level. As a MicroStrategy developer, and a SQL programmer, I pick and choose my battles, and as much as it’s recommended in MicroStrategy to make complex metrics with “Last” or different Level Dimensionality, there’s also situations where you want to make the database do the work…such is the Case with the ApplyComparison filter.

Some key things to understand when making these formulas. The “SQL” portion of your code always come first…and by first, I mean in quotes, right after your opening parenthesis. In this portion of code you have your dynamic values, which is represented by the hash tag (#). The next part of your Apply references what that #0 references. For example:

In this case, I’m telling the database engine that I want this filter to include all [Fiscal Periods] up and including ‘200905′. The value following the @ sign is the Form you’re using from your attribute creation. This technique works wonderfully, though you have to be aware of the structure of your fact tables. Here’s a report of the SQL that’s generated.

select
a12.ACCOUNT_GROUP_ID ACCOUNT_GROUP_ID,
max(a12.ACCOUNT_GROUP_DESC) ACCOUNT_GROUP_DESC,
(sum(a11.xAMOUNT) * -1.0) AMOUNT_YTD
from v_xFACT_TRANSACTION_Amt a11
join LU_ACCOUNT_GROUP a12
on (a11.ACCOUNT_GROUP_ID = a12.ACCOUNT_GROUP_ID)
where (a11.FISCAL_PERIOD_ID <= '200905')
group by a11.ACCOUNT_GROUP_ID

The reason a Function like ApplyComparison is so useful, is you can combine it with prompts, and make complex SQL phrases, that otherwise would take some serious work using your basic MicroStrategy functions…here’s an example.

ApplyComparison(“#0<#1", Customer@ID,?[CustomerPrompt])

So this filters for the first “X” amount of Customers based on a value you enter in a Prompt.

Granted, this can be done with multiple or simple filters in MicroStrategy, but, you’ll be surprised how much an understanding of the Apply family of phrases can simplify some of your processes, ad well as how much easier it makes categorizing and sorting your various filters. Is much easier to have your specific filters like above, all in one folder, without having to track the multiple filters that may be included is dependents.

Flash, Tips and Tricks ApplyComparison