Skip to main content

Applycomparison advancd filter with a select statement

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: ApplySimpleApplyAggApplyComparisonApplyOLAP 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:
external image tvy1.gifFictitious 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:
external image tvy4.gif
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)
external image tvy3.gif
And now the graph looks nice and clean:

external image tvy2-300x170.pngFictitious 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:
external image tvy5.gif
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

Popular posts from this blog

Microstrategy Custom number formatting symbols

Custom number formatting symbols If none of the built-in number formats meet your needs, you can create your own custom format in the Number tab of the Format Cells dialog box. Select  Custom  as the Category and create the format using the number format symbols listed in the table below. Each custom format can have up to four optional sections, one each for: Positive numbers Negative numbers Zeros Text Each section is optional. Separate the sections by semicolons, as shown in the example below: #,###;(#,###);0;"Error: Entry must be numeric" For more examples, see  Custom number formatting examples . To jump to a section of the formatting symbol table, click one of the following: Numeric symbols Character/text symbols Date and time symbols Text color symbols Currency symbols Conditional symbols Numeric symbols For details on how numeric symbols apply to the Big Decimal data type, refer to the  Project Design Guide . ...

mstrio – Python and R wrappers for the MicroStrategy

mstrio – Python and R wrappers for the MicroStrategy REST APIs Connecting to MicroStrategy  Create a connection to the Intelligence Server using   Connection()   and    connect()  in Python and R, respectively. Required arguments for the   Connection()  function are the URL for the MicroStrategy REST API server, MicroStrategy Intelligence Server username and password, as well as the MicroStrategy project name. By default, the   connect()  function anticipates your MicroStrategy Intelligence Server username and password. LDAP authentication is also supported. Use the optional argument    login_mode=16    in the    connect()  function for LDAP authentication.  Extract data from cubes and reports  To extract data from MicroStrategy cubes and reports, use the   get_cube()  and   get_report()  functions. Use...

Error. Engine Attribute Role Limit Exceeded! To get rid off this error: (1) Turn off Engine Attribute Role setting in VLDB dialog

Error. Engine Attribute Role Limit Exceeded! Some times Microstrategy will give the below error when updating the schema after an attribute is created. MicroStrategy Developer --------------------------- Error(s) occurred while loading schema: [DFCSCHEMA] Population Exception: The object shown in the following hierarchy no longer exists in schema: -Table LKUP_TEST_FEE_SHIPMENTS error. Engine Attribute Role Limit Exceeded! To get rid off this error: (1) Turn off Engine Attribute Role setting in VLDB dialog; OR (2) Use Table Alias featureDSSSQLEngine: Schema loading error: Message from GetErrorInfo : Report cache is not found.. Error(s) occurred while loading schema: 63. CAUSE: This error message means that the table shown in the error text needs to be split into a very large number tables. MicroStrategy has a limitation that only allows one table to be split into no more than 100 tables. The error above is shown when this limitation is surpassed....

Relative path for images in Microstartegy

Best practices on how to reference an image specified using a relative path for exported documents in MicroStrategy If possible, users should use HTTP paths to reference an image other than relative path or absolute path. If it is necessary to use a relative path to reference image, multiple locations need to store the copy of image as shown in the following table: Important notes: MicroStrategy Web Server is not involved in storing image when exporting. Image should be stored in Intelligence Server or MicroStrategy Developer Client For MicroStrategy Developer export, local MicroStrategy Developer handles the export, image should be placed on every single Developer machine If you don't know your Common Path or MSTR Home Path you can obtain them from the following registry keys if using windows or msireg.reg if using Unix HKEY_LOCAL_MACHINE --> SOFTWARE -->Microstrategy --> DSS Server -> Casetor -->Home Path HKEY_LOCAL_MACHINE --> SO...

Microstrategy Dossiers explained

Microstrategy  Dossiers With the release of MicroStrategy 10.9, we’ve taken a leap forward in our dashboarding capabilities by simplifying the user experience, adding storytelling, and collaboration.MSTR has  evolved dashboards to the point that they are more than dashboards - they are  interactive, collaborative analytic stories . Ultimately, it was time to go beyond dashboards, both in concept and in name, and so  the've  renamed VI dashboards to  ‘ dossiers ’.  Dossiers can be created by using the new Desktop product or Workstation or simply from the Web interface which replaces Visual Insights. All the existing visual Insights dashboards will be converted to Dossiers   With MicroStrategy 10.9, there was an active focus on making it easier to build dashboards for the widest audience of end users. To achieve this, some key new capabilities were added that make it easier to author, read, interact and collaborate on dashboards ...

Ways to create derived elements

Ways to create derived elements Using the Derived Elements Editor, you can create derived elements using the techniques described in the topics listed below: Grouping attribute elements to create derived elements Filtering attribute elements to create derived elements Using calculations to create derived elements

Microstrategy Dossier training videos

Microstrategy Dossier training videos Adding data to a Dossier: Creating a visualization filter in Dossier: Sending Dossier to User libraries: How to format Dossiers: Adding a designer filter to a Dossier: Use Bookmarks in Dossiers: Dynamic Links in Dossier: Recreating documents from Dossiers: Exploring and searching in Dossiers: Exploring sample dossiers in MSTR library: Using Page to Page Targets in Dossiers:

Fact tables levels tables in Microstrategy explained

Fact tables levels in Microstrategy: Fact tables are used to store fact data. Fact tables should contain attribute Id's and fact values which are measurable. All the descriptive information about the fact tables should stored in Dimension tables either in Star Schema fashion or Snow Flake Schema fashion which is best suited to your reporting solution. Since attributes provide context for fact values, both fact columns and attribute ID columns are included in fact tables. Facts help to link indirectly related attributes using these attribute ID columns. The attribute ID columns included in a fact table represent the level at which the facts in that table are stored. So the level of a fact table in the Fact_Item_Day_Customer can be the attribute Id's which is at Day, Item & Customer Id level. For example, fact tables containing sales and inventory data look like the tables shown in the following diagram: Base fact columns ver...

Disable data blending in MicroStrategy

Disable data blending in MicroStrategy Starting in MicroStrategy 9.4 data blending was made available for documents and dashboards. This permits grid, graph and widget objects to source data from multiple different datasets at the same time.  This is available under the analytical engine VLDB properties inside of project configuration. The property is named "document grids from multiple datasets" and defaults to enabled but can be set to disabled.  Below are the steps to enable/disable the settings of data blending: 1. Go to project configuration by right clicking on specific project(You need admin rights to do this). 2. In the Project configuration windows as shown below select Configure under Project level VLDB settings section. 3. Now it will open the VLDB settings window, select + on " Analytical Engine Settings " and then click on " Document Grids from multiple datasets " option. You will be presented with two...

Fiscal Week, Fiscal Month, Fiscal Quarter and Fiscal Year calculations in Microstrategy

Fiscal Week, Fiscal Month, Fiscal Quarter and Fiscal Year calculations in Microstrategy FiscalWeek Returns the numeric position of a week within a fiscal year, for a given  input date. This function is useful in financial reporting when the start of the fiscal year is different than the start of the calendar year. Syntax FiscalWeek< firstWeekDay ,  firstMonth >( Date / Time ) Where: • Date / Time  is the input date or timestamp. • firstWeekDay  (default value is 1) is a parameter that determines which day of the week is considered as the first day of the week. You can type an integer value from 1 to 7, with 1 representing Sunday, 2 representing Monday, and so on until 7 representing Saturday. • firstMonth  (default value is 1) is a parameter that determines which month is considered as the start of the fiscal year. You can type an integer value from 1 to 12, with 1 representing January, 2 representing February, and so on until ...