One of the questions I often get from newer MicroStrategy developers is “What is the difference between an Applysimple and an ApplyComparison?”. There are a few key differences which are easier to understand when you have real business samples but I’ll break these down with simple concepts.
First we should understand what an ApplySimple and ApplyComparison do. MicroStrategy calls these pass-through functions. In the simplest terms this means you’re allowed to “pass-through” your own SQL into certain use cases.
Applysimple
An applysimple is typically used in an attribute or a fact. I’ll provide a sample attribute with the syntax. The same syntax would can be applied in a fact
In my test case I have a table with a category column which contains “Books”, Electronics”, “Sundries”, and “Groceries” and can sometimes be null. I want to create my attribute from the category column that includes a case statement to account for the null values. The result I want is if the value is null then display “Unknown”. That syntax would look like this
Notice the #0 place holder which references the “category” database column at the end of the string.
Using this Applysimple, I now have an attribute which accounts for the Null Value.
ApplyComparison
An apply comparison is used as a filter in the where clause of the select statement. These filters can be very dynamic and is built depending on the use case. Most commonly I see these being used when a subquery is needed to filter the data or in combination with value prompts. For this simple example, I’ll create a applycomparison to only include records which have a category of books. That syntax would look like this
ApplyComparison(“ #0 = ‘Books’ ”, Category@ID)
Notice the #0 place holder which references the “category” database column at the end of the string.
A few things to keep in mind. If you create an Applycomparison filter the attribute must exist in the report for it to be applied. You will also need to enable the advanced filters in your user preferences in MicroStrategy desktop in Tools > My Preferences > Editors > Filters > Show advanced qualifications .
These filters can be as robust as your SQL imagination allows. Have fun!
Thanks for checking out this blog post. If you found this post helpful please consider donating. Any contribution is appreciated! Just click the PayPal icon at the bottom of this page.
Commentaires