Filter Reports using SQL with ApplyComparison and ApplySimple
No doubt MicroStrategy has powerful filtering capabilities but if you are familiar with SQL, somtimes you might find that you feel a bit constrained in certain situations and thinking you could do things easier with a simple query and there may come a time where you need to filter a report based on the results of a SQL query.
For example, suppose you have a monthly sales report querying data that is stored only at the month level. Now, let’s also assume that you would like the report filter to dynamically determine the current month based on yesterday’s date (today – 1 day). This is common in retail because you are typically always looking at the previous days sales. To keep things simple, let’s assume you have a table named “LU_DAY” with the columns Date, Month, Quarter and Year.
If you wanted to return the current month based on yesterday the SQL query would look something like this “SELECT DISTINCT MONTH_ID FROM LU_DAY WHERE DAY_DATE = Date-1“.
Now, let’s take a look at how we can put this all together in a report using the MicroStrategy Tutorial Project.
Step 1 – Create a new report and add the attribute month and the revenue metric as seen in the below screenshot.
Step 2 – Next add a filter and choose the option “Add an Advanced qualification” as seen in the below screenshot.
Step 3 – This is where the magic happens. Now, we need to use the ApplyComparison function to compare two values in our filter. In this case we will be comparing the Month attribute to the results of our SQL query that will return the current MONTH_ID. Take a look at the resulting function in the below screenshot and then we will walk through each parameter.
First Parameter “#0 IN (#1)” – This tells the ApplyComparison function how to compare the other values being passed as parameters. The #0 and #1 are place holders for the other parameters being passed to the function. So in this case you are telling the function to only return results where the value mapped to #0 is found within the value mapped to #1. It is also worth pointing out that since our query will only return one value we could have also entered “#0 = #1” but hopefully you get the idea.
Second Parameter [Month]@ID – Here we are simply passing in the Month attribute from our report. This value will get mapped to #0 in the first parameter.
Third Parameter ApplySimple(“SELECT DISTINCT MONTH_ID FROM LU_DAY WHERE DAY_DATE = Today()-1”,0) – In this parameter we are nesting another MicroStrategy function ApplySimple which allows us to use SQL pass-through to query the database directly from within our report. The ApplySimple function is a powerful function that can be used in many other places including metrics.
In closing, I do understand that in the tutorial project this was unnecessary because sales is stored at the day level so you could have simply used a normal filter but the purpose of this article is to show how you can incorporate SQL in your filters because there are instances where it is necessary. For example, as I mentioned in the beginning of the article, if the data you are reporting on is ONLY stored at a particular level like month, however, you need to filter the report based on data stored at a lower level like day, this might be an option to consider.