ApplyComparison ("#0 >= (select max(Hour)-1 from FACTTABLE where DAYDATE = current_date)", Hour@ID)
MicroStrategy’s strongest feature is it’s SQL Engine. The ability to define object relationships and then allow MicroStrategy to generate all of the appropriate SQL as you manipulate and drill is the core of the product. But of course, it can’t handle every since situation and you may sometimes need to help it along in certain instances. To extend this flexibility to you, MicroStrategy offers 5 functions which allow you to directly supply the SQL you want for a specific piece of the Query: ApplySimple, ApplyAgg, ApplyComparison, ApplyOLAP and ApplyLogical. Today, I’ll talk about ApplyComparison, which allows you to provide custom SQL in the WHERE clause of the query.
Example Scenario
Say that you have a report that should compare Units Sold for Today vs Yesterday by Hour. If you were to build this report by simply adding the Hour attribute and two Conditional Metrics for Today Units Sold and Yesterday Units Sold, you’d end up with a graph like this:
Fictitious Data
It’s a nice picture of how the days are comparing, but it’s a little ugly since we don’t have data for 7am and ahead for today yet. Ideally, we’d like to filter off the data for Today that we haven’t received, but how do we know where we are? Assuming that we aren’t strictly real time and can’t assume based on the current time, we’d want to check the max Hour that exists in the table, and then go up to that minus 1. We could probably build that with some different types of metrics or a Report as Filter, but I’ll choose to do this using an ApplyComparison.
Before you start
First of all, you can only build this kind of filter in Desktop (so, not Web). You also need to enable the feature in Desktop, as it’s not available be default. Go to Tools->My Preferences->Filters and check the box for Show Advanced Qualifications. This will enable the option to use ApplyComparison’s in Filters.
Back to the Report
Double click on your Report Filter and you’ll now have the option to add an Advanced Qualification:
You can then provide the code for the ApplyComparison. The code you provide will go into the WHERE clause, and you can pass attribute values using the #n syntax, where n is the zero based number of parameters you’re passing. In my example, I’m passing a single parameter, so it’s #0. I could pass additional values by also including #1, #2, etc in my code if I needed them. Also note that when you provide the value at the end of the statement, it’s in the format Hour@ID, not just Hour. MicroStrategy changes the display of it slightly in my screenshot to Hour (ID), but the actual code you’ll be using looks like this:
ApplyComparison ("#0 >= (select max(Hour)-1 from FACTTABLE where DAYDATE = current_date)", Hour@ID)
And now the graph looks nice and clean:
Fictitious Data
BONUS TIP
Another way to accomplish this using ApplySimple instead of ApplyComparison would be to drag the Hour attribute to the Report Filter like normal, choose ID as the form and Greater Than or Equal To as the operator, and choose Custom from the drop down box and provide the ApplySimple code:
ApplySimple("(select max(Hour)-1 from FACTTABLE where DAYDATE = current_date)", 0)
Since we’re not including any parameters for this query, we can use 0 as the placeholder at the end to satisfy the function’s parameters.
This will generate the same SQL and results, but without needing to enable the Advanced Qualification option which could be confusing to some.
ApplyComparison("#0<=(select to_number(to_char(add_months(sysdate(),-1), 'yyyymm'))) ",[Instance Month]@ID)
Worked example:
select distinct a11.month_sid
from mstr_datamart.date_dim a11
where a11.date_sid<=(select to_number(to_char(add_months(sysdate(),-1), 'yyyymmDD'))) and a11.date_sid>=(select min(utc_date_sid) from mstr_datamart.ox_transaction_sum_daily_fact)
order by 1 desc
ApplyComparison("#0<=(select to_number(to_char(add_months(sysdate(),-1), 'yyyymmDD'))) and #0>=(select min(utc_date_sid) from mstr_datamart.ox_transaction_sum_daily_fact order by 1 desc)",[UTC Date]@ID)
MicroStrategy’s strongest feature is it’s SQL Engine. The ability to define object relationships and then allow MicroStrategy to generate all of the appropriate SQL as you manipulate and drill is the core of the product. But of course, it can’t handle every since situation and you may sometimes need to help it along in certain instances. To extend this flexibility to you, MicroStrategy offers 5 functions which allow you to directly supply the SQL you want for a specific piece of the Query: ApplySimple, ApplyAgg, ApplyComparison, ApplyOLAP and ApplyLogical. Today, I’ll talk about ApplyComparison, which allows you to provide custom SQL in the WHERE clause of the query.
Example Scenario
Say that you have a report that should compare Units Sold for Today vs Yesterday by Hour. If you were to build this report by simply adding the Hour attribute and two Conditional Metrics for Today Units Sold and Yesterday Units Sold, you’d end up with a graph like this:
Fictitious DataIt’s a nice picture of how the days are comparing, but it’s a little ugly since we don’t have data for 7am and ahead for today yet. Ideally, we’d like to filter off the data for Today that we haven’t received, but how do we know where we are? Assuming that we aren’t strictly real time and can’t assume based on the current time, we’d want to check the max Hour that exists in the table, and then go up to that minus 1. We could probably build that with some different types of metrics or a Report as Filter, but I’ll choose to do this using an ApplyComparison.
Before you start
First of all, you can only build this kind of filter in Desktop (so, not Web). You also need to enable the feature in Desktop, as it’s not available be default. Go to Tools->My Preferences->Filters and check the box for Show Advanced Qualifications. This will enable the option to use ApplyComparison’s in Filters.
Back to the Report
Double click on your Report Filter and you’ll now have the option to add an Advanced Qualification:
You can then provide the code for the ApplyComparison. The code you provide will go into the WHERE clause, and you can pass attribute values using the #n syntax, where n is the zero based number of parameters you’re passing. In my example, I’m passing a single parameter, so it’s #0. I could pass additional values by also including #1, #2, etc in my code if I needed them. Also note that when you provide the value at the end of the statement, it’s in the format Hour@ID, not just Hour. MicroStrategy changes the display of it slightly in my screenshot to Hour (ID), but the actual code you’ll be using looks like this:
ApplyComparison ("#0 >= (select max(Hour)-1 from FACTTABLE where DAYDATE = current_date)", Hour@ID)
And now the graph looks nice and clean:
Fictitious DataBONUS TIP
Another way to accomplish this using ApplySimple instead of ApplyComparison would be to drag the Hour attribute to the Report Filter like normal, choose ID as the form and Greater Than or Equal To as the operator, and choose Custom from the drop down box and provide the ApplySimple code:
ApplySimple("(select max(Hour)-1 from FACTTABLE where DAYDATE = current_date)", 0)
Since we’re not including any parameters for this query, we can use 0 as the placeholder at the end to satisfy the function’s parameters.
This will generate the same SQL and results, but without needing to enable the Advanced Qualification option which could be confusing to some.
ApplyComparison("#0<=(select to_number(to_char(add_months(sysdate(),-1), 'yyyymm'))) ",[Instance Month]@ID)
Worked example:
select distinct a11.month_sid
from mstr_datamart.date_dim a11
where a11.date_sid<=(select to_number(to_char(add_months(sysdate(),-1), 'yyyymmDD'))) and a11.date_sid>=(select min(utc_date_sid) from mstr_datamart.ox_transaction_sum_daily_fact)
order by 1 desc
ApplyComparison("#0<=(select to_number(to_char(add_months(sysdate(),-1), 'yyyymmDD'))) and #0>=(select min(utc_date_sid) from mstr_datamart.ox_transaction_sum_daily_fact order by 1 desc)",[UTC Date]@ID)
Comments
Post a Comment