Right-click on the report and click on the 'Edit' menu. DataFrame. The CaseV function takes a numeric value M1 as input and compares it to another numeric value V1. Embedded Analytics Solutions Solutions; Financial Services; Healthcare; Retail; GovernmentThis article explains the behavior of the commit level VLDB propertyTo simplify the explanation, this function is used to apply non-standard SQL expressions that MicroStrategy does not support or does not support. Open the VLDB Properties Editor to display the VLDB properties for the level at which you want to work. The New Grid dialog box opens. Select the Database Instance used for the project warehouse, right-click and select 'VLDB properties'. Workaround 4: Create a metric based on the attribute Country, for example Max (Country). 2 metric aggregations will not be dimensionality aware. MicroStrategy SQL Generation Engine will not automatically add parentheses around the list. However, end users have to provide both custom SQL and function inputs every time they use these functions in creating MicroStrategy objects like metrics, filters and so on. The following. Place attributes Country and Distribution Center on report template, and the above metric either on report template, or in Report Objects window if you have an OLAP license. This article explains the behavior of the commit level VLDB property35. They act as containers for non-standard SQL expressions that MicroStrategy does not support. Functions in MicroStrategy are powerful tools used in expressions to define MicroStrategy objects and initiate complex user-selected calculations. This article notes a scenario where no aggregation occurs in the SQL generated by MicroStrategy. For complete details about all VLDB properties, see SQL Generation and Data Processing: VLDB Properties. Let us create one simple table named numbers and store the. Sum <Distinct, FactID, UseLookupForAttributes>(Argument)Where: Argument is an attribute, fact, or metric representing a list of numbers. CAUSE: The attribute form used for the 'group by' clause of the SQL generated is not an ID form. Instead, they are intended to enhance the MicroStrategy product by taking advantage of what the RDBMS platforms can offer. The downward outer join pass. Determines how distinct counts of values are retrieved from partitioned tables. The default syntax can be modified by using 'Column Pattern' VLDB property. Qualify on the attribute form that you want to compare (presumably the Year ID), and then in the Operator section, change the dropdown from its default 'Value' to 'Custom'. Group by alias. The property is named "document grids from multiple datasets" and defaults to enabled but can be set to disabled. Accessing Report VLDB Properties. You can use the SQL DISTINCT clause within the SQL SUM function. Number of Views 822. Expand Tables and select. x introduces a third option to the VLDB Property "Drop Temp Table Method. In this case, the report SQL may not be possible to complete on the database. 1. Alternately, there is a VLDB property, "Engine attribute role options," which will allow the MicroStrategy SQL Engine to attempt to determine automatically when attribute roles are present in the schema and generate SQL accordingly. Steps: 1. The new VLDB Property has the following 3 options: If option 2 or 3 are selected, Cartesian Join Governing replaces Cartesian Join Warning and Cartesian Join Warning no longer works and becomes obsolete. The display format for dates does not change even after changing the SQL Date format and Date Pattern settings under VLDB properties of the project in MicroStrategy Developer. Single-value functions can be used to create. 0. the MicroStrategy SQL Generation Engine generates the following SQL for a report in Tutorial that requests the sum of units received for each Item, Quarter and. Double-click the security role you want to assign to the user or group. This article describes how to show distinct total value via setting distinct subtotal with across level in Microstrategy Developer. This property can only be enabled through Workstation. If the select level is the same as the table key level and the table's true key property is selected, DISTINCT is not used. By default, the metrics Compound Profit and Profit Margin do not use a dynamic aggregation function. What I ended up doing is run the query without the Sum () Function and then use the Totals feature that is available on forms in Datasheet view. If no ordering is specified, the result of some window functions (e. Returns the sum of all numbers in a list of values. , a temporary table is created but not referenced in a later pass; Reuses redundant SQL passesKB37567: MySQL table specific SQL hints cannot be used in reports against MicroStrategy 9. 68K KB325314: In MicroStrategy 9. The 'Group By ID Attribute' VLDB property is not applied in a MicroStrategy Desktop 8. Metric with an IF Function. This document will use the MicroStrategy Tutorial project with a warehouse in SQL Server to demonstrate the concept: Create a numeric value prompt and name it P1. Create a metric with M03 = MonthStartDate (CurrentDate). These are called pass-through functions because Microstrategy does not check the actual SQL construct and dumps it as is on the database. Return the sum of all Quantity fields in the OrderDetails table: SELECT SUM (Quantity) FROM OrderDetails; Try it Yourself ». Rank) calculated in Analytical Engine" found under the 'Metrics' folder in the list of VLDB properties. If it is necessary to apply different connection parameters to different warehouses using Pre Statement VLDB Properties, it is possible to use the Table Pre Statement VLDB Properties instead. The ApplyAgg function itself is a group-value function and accepts facts, attributes, and metrics as input. They act as containers for non-standard SQL expressions that MicroStrategy does not support. This calculation can be aggregated from the Employee level to the Region level. expression is an expression of the exact numeric or approximate numeric data type category, except for the bit data type. MicroStrategy uses different defaults for the join type based on the database you are. This query does not return all desired elements of category from the Data Warehouse. There is a method to change the default syntax for table and column names while generating MicroStrategy SQL during report execution in MicroStrategy by using VLDB properties. Some of these limitations are described in the following article: KB30257 - "Limitations of "derived table" intermediate table type VLDB setting in MicroStrategy SQL Engine" Another limitation of this setting is using multisource SQL. All articles. If you are editing the If function, use the values in step 7. Select one: To create a new security filter, click New. Go to Data > VLDB Properties > Joins > "Preserve all final pass result elements. Determines how distinct counts of values are retrieved from partitioned tables. Hi, I am having the same problem with Microstrategy Version 8. expression. In the event of a code change in future builds, MicroStrategy may not be able to provide additional code on this matter even though this customization is provided. While the default values should result in the best report performance in most cases, some reports may perform better when certain VLDB Properties are set to non-default values. To provide a reasonable level of generality, the feature is based on a 'pattern and arguments' scheme. ')"; [Net Weight. CAUSE: The MicroStrategy SQL Generation Engine distinguishes between the SQL passes generated for the Report (to return the data for the result set) and the passes generated for the datamart (to create and populate the datamart table) when applying the VLDB settings. Default value changed for "Separate Count Distinct" VLDB setting for Impala 2. Transformations − They are the data transformation features used for time-series based analysis of the data. This allows SQL to be run after the report execution, and is not tied to the subscription. x and 10. The Moving Sum function returns a cumulative total of values based on a specified window size. x. Apply functions provide access to functionality that is not standard in MicroStrategy, but is available in the relational database. This is true whether the null checking is written into the metric formula using the MicroStrategy function NullToZero(My_Fact), or using a database pass-through function such as ApplySimple("NVL(#0, 0)", My_Fact) for Oracle. The Apply functions are not meant to take the place of the standard MicroStrategy functions. [M1]:. This is available under the analytical engine VLDB properties inside of project configuration. As these accounts do not have access to "Configuration Managers" in the Administration tab, the only way to view the VLDB. This function is used to display the ranking of values in a list relative to the other values. If all conditions are false, a default value is returned. Without this option all reports would execute their SQL pass by pass in a sequential order over a single database connection. x database and leads to different SQL. If you are upgrading to MicroStrategy 10. FROM OrderDetails WHERE ProdictId = 11; Try it Yourself » Use an Alias Give the summarized column a name by using the AS keyword. This article describes how the "Set Operator Optimization" VLDB property works in MicroStrategy Controls whether tables are joined only on the common keys or on all common columns for each table. Attribute form expressions must be rendered into SQL and cannot use any MicroStrategy functions specific to the Analytical Engine. Explain how MicroStrategy handles metadata. In general, a user specifies a list of conditions and corresponding return values. Tutorial project is used here. For DataFrames, specifying axis=None will apply the aggregation across both axes. Generate Oracle AWR report or monitor the database to identify the low performance query. Splitting the fraction: Numerator: Sum (VOL) {~} Denominator: Max ( ApplySimple ("replace (#0, ',', '. Interact with the engaged MicroStrategy Community to get answers to all your. compound metrics. This article describes how the element browsing pre-post sql statements work in MicroStrategyFunctions Reference. This setting can substantially reduce the number of SQL passes generated by MicroStrategy. The First option, 'CREATE and. Define the statistics and enterprise warehouse database. After running a report containing the attribute "Subcategory" and metric "Sum of COST", "Sum of COST" is found not aggregated but just listed all the related records in the data source file. After a report is run, we keep the connection open (DB connection timeout values apply). Community & Support Search Discussions Open A Case View My CasesThe SUMIF function in Excel returns the sum of cells that meet a single condition. This flexibility is an important benefit of VLDB properties because it permits adjustment of many levels such as: REPORT - highest priority. ApplyOLAP – Uses OLAP functions such as Rank, Ntile, and so on. 8 and up . For Compound Profit to be dynamically aggregated correctly, you change the dynamic aggregation function to Sum. Set up the Enterprise Manager. This article describes how to create a metric with an attribute as a filter using a Case statement in a Visual Insight dashboard in MicroStrategy Web. The database was created with files stored directly in Azure blob storage , using a Premium Storage. . What function does the MicroStrategy Intelligence Server serve? Ans: Query processing; Metadata repository management; Data caching and Intelligent Cubes; Security and authentication; Load balancing; 47. A given VLDB setting can support or. Sum is a group-value function. This article covers the Constant Column Mode VLDB property and its options and function in MicroStrategyIt has two options: Disable Set Operator Optimization: SQL will be generated as in previous versions. SELECT SUM(DISTINCT salary) AS "Total Salary" FROM employees WHERE salary > 25000; If there were two salaries of. The SUM () function returns the total sum of a numeric column. , row number) is non-deterministic. While this code may apply to other releases directly, MicroStrategy Technical Support makes no guarantees that the code provided will apply to any future or previous builds. In general, a user specifies a list of conditions and corresponding return values. For information about accessing these properties, see the page reference for each property in the table below. Database instance, report, and templateSYMPTOM: The user has a report where the VLDB property of SQL Global Optimization has been set as "Level 4: Level 2 + Merge All Passes with Different WHERE. The syntax for the If function is IF (Condition, TrueResult, FalseResult) where:Explanation. This setting also applies to the use of security filters for creating an element cache. This setting can reduce the number of SQL passes generated by MicroStrategy. The 1NF table will have one number per row, and will contain more rows than the initial table. SELECT SUM(DISTINCT salary) AS "Total Salary" FROM employees WHERE salary > 25000; If there were two salaries of. If that level is also set to the default or if the VLDB property is not set at the project level, the setting at the database instance is used. Help your customers get more out of the app experience with an engaging AI assistant that provides automatic answers or outputs in real time. It does not apply to situations where index creation occurs before insertion of data. x mixed case columns names for a DB2 database by default have SQL generated with quote characters enclosing the name . Using linear and seasonal regression for Revenue F. 6. To set these properties, open the report in the Report Editor or Report Viewer. The maximum number of rows returned to the Server for the final result set. Enable Set Operator Optimization (if supported by database and [Sub Query Type]): Use set operators when possible. The calculation can restart based on attributes identified in the parameter settings. Join common key on both sides. on how division by zero is handled and can cause failures when your queries use features like indexed views, computed columns or XML methods. The SQL COUNT() function is used to count the rows in a table, and can be used with ‘ALL’ and ‘DISTINCT’ arguments. On the Freeform Sources tab, select Create Freeform SQL report. Case. Always consider to collect database statistics as the query execution plan is generated based on that. For Compound Profit to be dynamically aggregated correctly, you change the dynamic aggregation function to Sum. Specifies that SUM returns the sum of unique values. Action If the CASE expression has only one branch -- i. For information on connecting to databases, see Connect to Databases. Group by alias. This query does not return all desired elements of category from the Data Warehouse. x includes a VLDB property, 'CREATE and INSERT Support,' under the Tables folder, at the database instance level. KB11125: How to control the join order of metrics in the final pass when using the "Do not do downward outer join" VLDB setting in MicroStrategy SQL Generation Engine. Option 1: Keep the backward compatibility with MicroStrategy 2019 where SQL queries are governed by Cartesian Join Warning VLDB. This property exists at the Database instance, Report and Metric levels: MicroStrategy SQL Generation Engine makes it easy to show data that exists on the lookup table and not in the fact table by the use of the 'Preserve all lookup table elements' VLDB property. A report that shows sales for all the years and all the customer regions even if sales do not exist in the intersection of them can be achieved by the use. The GROUP BY Clause in. Define the statistics and enterprise warehouse database. x. Intermediate Table Type. g. Developers can retrieve the report SQL statement when running a report with the following customization: Launch the Web Customization Editor. When the 'Use inherited value' flag was checked for the setting at the report level, it went back to the higher object with an unchecked flag (the metric level) and used that. Reuses redundant SQL. Although tables and indexes are the most important and commonly used schema objects, the database supports many other types of schema objects, the most. sum #. The formula of a simple metric is based on group functions (such as sum or average). The ApplySimple function is a single-value function. e. MicroStrategy SQL Generation engine 10. Since the report or template is not associated with a specific database instance, the primary. In MicroStrategy, a hint can be defined in VLDB properties to appear within a MicroStrategy-issued SQL statement. The two metrics filters are simple attribute qualifications which are allowed under Global Optimization. For complete details about all VLDB properties, see SQL Generation and Data Processing: VLDB Properties. g. Case This function evaluates multiple expressions until a condition is determined to be true, then returns a corresponding value. To modify the VLDB Properties at the database instance level right-click on the data warehouse database instance in the Database Instance Manager and select VLDB Properties…. For Series this parameter is unused and defaults to 0. This article describes how to modify MicroStrategy to remove coalesce functionsOpen the VLDB Properties Editor: . compound metrics. This version of the product is no longer supported in accordance with MicroStrategy's support lifecycle. Join common key on both sides. MicroStrategy SQL Generation engine 10. Ca se functions Microstrategy Case functions return specified data in a SQL query based on the evaluation of user-defined conditions. Community & Support Search Discussions Open A Case View My CasesVLDB is an acronym for “Very Large DataBase”. VLDB properties allow you to customize the SQL that MicroStrategy generates, and determine how data is processed by the Analytical Engine. Open the VLDB Properties Editor this way. This article describes the limitations of using derived tables for SQL generation. Several additional VLDB properties are introduced with MicroStrategy 9. The SQL can be obtained from the SQL Statement section, as shown below: Published: Apr 13, 2017. The VLDB property "Attribute ID Constraint" is used during creation of intermediate tables to specify whether the attribute ID columns in those tables will or will not allow null values, or to use other constraints allowed in the warehouse's SQL implementation. This setting provides two options - 'Permanent table' or 'True temporary table'. Why MicroStrategy Why MicroStrategy; Customer Stories; PlatformExecute the report and see the result below. 0 and above. 0, a VLDB property is available to control the join behavior between component metrics in a nested compound metric: "Nested Aggregation Outer Join," located under Joins in the VLDB property editor. Stream processing has been an active research field for more than 20 years, but it is now witnessing its prime time due to recent successful efforts by the. Apply (Pass-Through) Functions MicroStrategy Apply. The image below shows the SQL view of the Report Pre Statement 5s output. The only tables that are needed are the. Two different passes of SQL for metrics which only differ via a simple filter are note merged via SQL Global Optimization levels 3 and 4 despite notes that it can. Apply functions: These functions provide access to functions and syntactic constructs that are not standard in MicroStrategy but are offered by various relational database. A report that shows sales for all the years and all the customer regions even if sales do not exist in the intersection of them can be achieved by the use. The "Downward Outer Join" VLDB property allows higher-level metrics to be outer joined by constructing a table of all necessary attribute elements to be included early in the join path. To configure:-. Most of MicroStrategy’s OLAP functions (24 out of 31) are calculated via SQL in Teradata rather than in the MicroStrategy analytical engine. The types of arguments determine how the parameter markers inside the pattern are replaced. Some databases do not support implicit creation, so this is a database-specific setting. [M1]:. Types of variables Before going into the details of the types of Apply Functions , I will point out that these functions require certain variables to be passed as parameters. The following steps describes how to create such derived metric. They act as containers for non-standard SQL expressions that MicroStrategy does not support. DataFrame. If parentheses are required in the database syntax, it is the user's responsibility to provide them, e. To see an example that uses this technique, see Estimating dynamic aggregation values with different aggregation functions. In general, a user specifies a list of conditions and corresponding return values. The ApplyAgg function itself is a group-value function and accepts facts, attributes, and metrics as input. When VLDB settings are configured at the report level to allow for an outer join to the lookup table in MicroStrategy Developer, the SQL does not show an outer join. The ApplyAgg function is used to define simple metrics or facts by using database-specific, group-value functions. Create another filter F2 with the same parameters as F1, but do not copy or use the same filter, as the optimization engine will otherwise not generate a duplicate pass. From MicroStrategy 9. With MicroStrategy, SQL optimization is performed, by default, across passes corresponding to different elements of a Custom group when possible. The image below shows the SQL view of the Report Pre Statement 5s output. This procedure assumes you are already working with a metric in the Metric Editor. Case This function evaluates multiple expressions until a. Some databases, such as DB2, support both Join 89 and Join 92. The functionality of Apply functions is provided by wrapping the expression, as a string, inside a dedicated (Apply) function call. Case This function evaluates multiple expressions until a condition is determined to be true, then returns a corresponding value. In the condition you can use logical operator (<,>,<>,=) and wildcards (*,$) for partial conditions. ; In some limited cases you can rewrite the aggregate to avoid. [TOT_DOLLAR_SALES]) AS Revenue into [ZZEA00] from. Microstrategy fetches the list of values to be displayed in an Attribute Element Prompt by executing two passes of SQL as seen below: Pass 0. 3. Grouping. 1) When I execute the STored Procedure by setting it in PreSQL. The VLDB setting "Do not do downward outer join for databases that support full outer join" is appropriate in the following situations: The following steps provide an overview for configuring secure Freeform SQL text prompts: Step 1: Identify Database Support for Parameterized Queries. Returns a date or timestamp plus an offset (+ or -) n days. order byApply Security Filters: Enables and disables the use of security filters in element requests. In the examples in this article, we will be using a 12 TB database, appropriately named VLDB01. There is no VLDB setting associated with this option. OR, to convert an existing filter into a security filter, click Import. Multiple passes are generated only when necessary to resolve all the metric definitions. DAY_DATE DAY_DATE, a12. ; Distinct is a TRUE/FALSE parameter that allows you to. These settings can be changed in the VLDB Properties dialog box for either reports or the database instance. REGION_NAME) REGION_NAME, Apply functions: These functions provide access to functions and syntactic constructs that are not standard in MicroStrategy but are offered by various relational database management system (RDBMS) platforms. For all other valid Intermediate Table Type VLDB settings, the SQL does not reflect the values set for these properties. To configure:-. That Stored Proced has an Input Parameter which I need to. Group by position. 1 client subtotal behavior. This allows queries to run faster since there are no CREATE TABLE or DROP TABLE statements to catalog, no corresponding locks on the created tables or the system. x. For example, your report contains Store and Sum (Sales), and it is possible that a store does not have any sales at all. However after re-migrating the freeform SQL report to the target environment, the report still shows the results with the total subtotal function and dynamic. ) From the Tools menu, select Show Advanced Settings. Beginning with MicroStrategy SQL Engine 9. Aggregate functions and subqueries are not. Group by column. When this subtotal line is enabled, the Analytical Engine looks to the 'Total subtotal function' to decide which Subtotal object in the metadata to use when evaluating the subtotal. Use of M03 in Microsoft SQL Server 2000 would result in DATEADD (DD, (1 - DAY (GetDate ())), GetDate ()). While this code may apply to other releases directly, MicroStrategy Technical Support makes no guarantees that the code provided will apply to any future or previous builds. , one condition, a true expression and a false expression -- the MicroStrategy IF function may be used instead. No pre-query is required and MicroStrategy can produce derived table or common table expression syntax. The SQL SUM () Function. This article notes a scenario where no aggregation occurs in the SQL generated by MicroStrategy. Let us create one simple table named numbers and store the. In the Metrics folder, two available options are shown as below: 'Transformable Aggmetric' -- False Metadata partitioning is the term that MicroStrategy uses to distinguish a partitioning setup that is stored in the MicroStrategy Metadata repository. From the Select a Project drop-down list, select the project for which to assign the security role. AddDays. Tables − They simply represent data in a tabular form (columns and rows). Passes that hit the same fact table across different elements of a custom group are merged provided that they satisfy certain conditions. You can apply transformations to simple metrics but not for compound metrics. g. Wherever you can define an expression, you can use a function. Sum is a group-value function. 0 or above, to pick up the fix, enable project level setting "Data Engine Version" to 11. This setting can substantially reduce the number of SQL passes generated by MicroStrategy. In general, a user specifies a list of conditions and corresponding return values. It is a new feature in. This article describes how MicroStrategy uses subqueries and how the options of the "Sub Query Type" VLDB property control the SQL syntax used. In MicroStrategy Developer, right-click a metric and select Edit. '. Example Name the column. The location of each property in the CREATE TABLE statement is given below. When they do appear, report designers have some degree of control over the subquery syntax using the Very Large Data Base (VLDB. Select the Members tab. Ca se functions Microstrategy Case functions return specified data in a SQL query based on the evaluation of user-defined conditions. Each of the functions in this category substitutes for one of the function types mentioned above and can be used wherever that type is used. The VLDB property, 'Additional Final Pass Option' is intended to override this behavior for single-pass reports. In that case, MicroStrategy accesses the partitioned data transparently by referring to a single table name. Use Count (Attribute@ID) to calculate total element number (uses count distinct if necessary) For Tandem databases, the default is Use ODBC Cursor. It is possible to disable the warning by setting ansi_warnings off but this has other effects, e. If you are not, do one of the following:Here are the series of steps to setup report objects in which metrics and subtotals so only the subtotal field will contain the RunningSum and the regular metric values will be standard sum values. The Preserve All Lookup Table Elements VLDB property is used to show all attribute elements that exist in the lookup table, even though there is no corresponding fact in the result set. Select VLDB Properties from the Data menu to access the VLDB Properties (Report) dialog box. However, the SQL used to execute the report can still be viewed and obtained. This is true whether the null checking is written into the metric formula using the MicroStrategy function NullToZero(My_Fact), or using a database pass-through function such as ApplySimple("NVL(#0, 0)", My_Fact) for Oracle. Note that the SUM() function ignores NULL values. The RANK function is closely related to other OLAP functions. The resulting SQL will contain just the Metric Alias. Visit the MicroStrategy download site to download the latest supported version of this product. Below is the supported OLAP functions by MicroStrategy with associated SQL pattern. The VLDB settings report now displays all the default settings for the data source. From creating basic objects to building complex reports and analyzing data, you have the ability to create custom. Note that the SUM() function ignores NULL values. However, MicroStrategy cannot guarantee an optimal ordering of tables unless there is at least one report level metric. x. This article does contain a solution. In some cases, it could be appropriate to change this setting for DB2 LUW. Key distinctions of the RANK function are that it requires an order by clause and does not support a window clause. x includes a VLDB property, 'CREATE and INSERT Support,' under the Tables folder, at the database instance level. For more details on performing outer joins between metrics at different dimensionalities, refer to the following MicroStrategy Knowledge Base documents: Ca se functions Microstrategy Case functions return specified data in a SQL query based on the evaluation of user-defined conditions. Case This function evaluates multiple expressions until a condition is determined to be true, then returns a corresponding value. MSTR The key specified is the true key for the war. expression is an expression of the exact numeric or approximate numeric data type category, except for the bit data type. This article covers the Constant Column Mode VLDB property and its options and function in MicroStrategy Using Apply functions is recommended only when corresponding functionality does not exist in MicroStrategy. 3. This property is found in the Select/Insert folder of the VLDB property editor: This property has four options: Group by expression. When none of the above conditions are met, the option selected for this property determines how DISTINCT and GROUP BY are used in the SQL statement. SQL Global Optimization. In MicroStrategy, SQL Global Optimization reduces the total number of SQL passes with the following optimizations: Eliminates unused SQL passes, e. 36. The following list of articles can be used for further reference regarding the 'derived table' behavior with MicroStrategy SQL engine. MicroStrategy provides Apply functions as a means of handing off these data and RDBMS-specific functions to your database, which results in your database—not MicroStrategy—performing the computations you require. Most databases expect only one statement per SQLExecDirect. For example, when counting from the ‘Employees’ table, ‘TotalEmployees’ (using ‘ALL’) is 5, indicating there are 5 employee IDs in total, whereas ‘UniqueDepartments’ (using ‘DISTINCT’) is 3, indicating. So when the value was changed to 'Check for NULL in all queries' at the report level, the MicroStrategy SQL Generation Engine issued a NULL validation for the metric. the aggregation function is not applied to the fact for the metric calculation. The Profit metric is a simple sum of the Profit fact. 4. There is no VLDB setting associated with this option. Although transformations can be applied to any attribute hierarchy, the Time hierarchy is used most often. They act as containers for non-standard SQL expressions that MicroStrategy does not support. The property may be set project wide in the database instance, or on a report-by-report basis. Notice the new setting under Project Configuration > Project Definition > Advanced > Analytical engine VLDB properties > Subtotals over consolidations compatibility:This knowledge base article describes an issue in MicroStrategy 10. a temp table is created but not referenced in a later pass. Is a constant, column, or function, and any combination of arithmetic, bitwise, and string operators. Case This function evaluates multiple expressions until a condition is determined to be true, then returns a corresponding value. This article describes how to use wildcards to display temporary table names in pre/post statements. Return the sum of all Quantity fields in the OrderDetails table: SELECT SUM (Quantity) FROM OrderDetails; Try it Yourself ». Metric definition: Sum (IF (Employee@ID In (1,2,4,5),Revenue,0)){~+} This is good as a quick solution, since.