https://www2.microstrategy.com/producthelp/10.8/AdvancedReportingGuide/WebHelp/Lang_1033/Content/AdvancedReporting/Conditional_metric_and_report_filter_interactions.htm
These options are dependent on one another; although they each affect the filter interaction differently, they also work together. After you understand the options separately, refer to Combining the embedding method and remove related elements settings to learn how they come together to affect the conditional metric results.
The following diagrams represent the logic of the calculations, not the actual data values calculated by the conditional metric. The cross-hatched area represents the results of the conditional metric, indicating that results are calculated in each scenario. The cross-hatching is not meant to represent that those results will necessarily be identical sets of data.
Selecting the Merge report filter into metric option includes the symbol @2; in the metric formula.
Depending on the embedding method you select, symbols are added to the metric formula as described in the table below:
A symbol is also added to determine if related report filter elements are removed, as described in Remove related report filter elements.
The embedding method is relevant only if at least one filter contains a metric qualification, relationship qualification, or a shortcut-to-a-report qualification. The results from these types of qualifications can vary. For example, filtering on “country=US” always yields the same results. This is an example of an attribute qualification. However, filtering on “country where revenue is greater than $1000” can return different results if the data is for 2002 only, 2003 only, or both years combined. This is a metric qualification.
For a list of descriptions of the different types of qualifications, see Types of qualifications.
For example, you need to calculate revenue from high-volume customers, where high-volume is defined as a customer who has purchased more than 20 items. Create a conditional metric with a formula of Sum(Revenue), setting the condition as the set of customers where Units Sold > 20.
You create a report containing the High-Volume Customer Revenue metric and a filter for the 75 most profitable items. How should the High-Volume Customer Revenue metric be calculated? How should the report filter and the metric filter work together? Several possibilities exist, and the embedding method allows you to specify which possibility fits your business requirements. Note that both the metric filter and the report filter contain a metric qualification, so the embedding method is relevant to this example.
To do this, you must select the top 75 most profitable items, using all customers in the calculation. Next, find the customers that bought more than 20 units of those 75 items. Finally, the metric calculates the sales of those 75 items to the selected customers. Since the report filter contains the qualification for the profitable items, the report filter must be applied first.
Merge report filter into metric uses the results of the report filter to evaluate the metric filter. In this case, only one customer has bought more than 20 units of the selected items, as shown in the following report sample.
To do this, you must first determine the customers who bought more than 20 units; these are the high-volume customers. Note that the units do not have to be of the same item. Next, find the 75 most profitable items that these big-volume customers bought. Finally, calculate the sales of those 75 items to the big-volume customers only. Since the metric filter contains the qualification for high-volume customers, the metric filter must be applied before the report filter.
Merge metric condition into report uses the results of the metric filter to evaluate the report filter. Norman Klein is included on the report, as shown in the portion reproduced below, since we know from the previous example that he has bought more than 20 items.
A total of 9653 customers are selected for this report, since the filter to find the high-volume customers is not restricted to specific items; any customer buying more than 20 units of any and all items is included.
To do this, you must determine the customers that bought more than 20 units of any items. Note that the units do not have to be of the same item. Select the top 75 most profitable items, using all customers in the calculation, not just the selected customers. Finally, calculate the sales of those 75 items to the selected customers only. You want to intersect the filters to obtain the results that meet both qualifications.
Merge into new allows you to calculate the report filter and metric filter independently, then intersect the results when calculating the metric. Norman Klein is included on the report, as shown in the portion reproduced below, since we know from the first example that he has bought more than 20 items.
Notice that the revenue calculated for Hugh Abarca in this report is $2,010. In the second report, the value is shown as $1,993. Why? The list of items included in the revenue calculation differs between the two reports. The second example includes only those items sold to the high-volume customers, while the third selects from all items in the database, sold to any customer.
A total of 9655 customers is selected for this report, slightly more than the previous example. Why? The second and third examples both start with the same list of customers, which is customers who have purchased more than 20 of any and all items. However, as explained above, the list of profitable products differs between the two reports. For the second report, if a customer’s purchased units did not include any of the 75 most profitable items of the big-volume customers, he is not included on the report.
For another description of this setting, see Additional examples of the embedding method options.
Depending on the embedding method you select, symbols are added to the metric formula as described in the table below:
A symbol is also added to determine if related report filter elements are removed, as described in Remove related report filter elements.
For example, a report contains a Revenue metric and the Category attribute. The metric filter is set to the New York Call Center only and the report filter to all southern regions (South, Southeast, and Southwest).
Since Call Center and Region are related because they belong to the same hierarchy, the Remove related report filter elements setting is employed. The metric filter overwrites the report filter, so only New York revenue is included in the report. This setting allows you to ensure that the metric always calculates for the New York Call Center, regardless of whether other related elements are included in the report filter.
If the check box is cleared, the results are the intersection of the filters. In this case, New York and Southern regions exclude each other, so the combined filter is empty. The report returns no data. Clearing the option allows the metric filter to intersect with the report filter.
For another description of this setting, see Additional examples of the embedding method options.
The report filter itself is not changed; the setting determines the filter interaction only for the conditional metric. For example, add the Revenue metric to the previous report and see the results below.
Note that the Revenue metric does not have conditions or levels. It uses the report filter and therefore calculates revenue by category for all the southern regions.
If you select the Remove related report filter elements option, a plus (+) symbol is added after the symbol for the embedding method in the metric formula. If you clear the Remove related report filter elements option, a minus (-) symbol is added after the symbol for the embedding method in the metric formula. For information on the embedding method symbols, see Embedding method.
If you create these reports, be aware that the years and data have been updated in MicroStrategy Tutorial; the reports will not display the same values as the samples.
The report samples build on the scenario described in Embedding method. You need to calculate revenue from high-volume customers, where a high-volume customer is defined as having purchased more than 20 items. To calculate that revenue, use a conditional metric with a formula of Sum(Revenue), setting the condition as the set of customers where Units Sold > 20. The report contains Year, the High-Volume Customer Revenue metric, and a filter for the 75 most profitable items in the following Customer Regions: Central, Northwest, South, and Southwest.
These examples are also explained using diagrams, in Combining the embedding method and remove related elements settings: A visual explanation.
The report filter criteria is applied to the data first. Then the metric filter is applied to the results of the report filter evaluation. If the report filter contains a qualification based on an attribute related to an attribute in the metric filter, the attribute qualification in the metric filter overwrites the report filter qualification. This occurs only in the conditional metric and only for the related attributes.
In the sample report, the 75 most profitable items among customers in the specified Customer Regions are selected; these are the results from the evaluation of the report filter. The metric filter is applied to those results, so customers who bought more than 20 units of the selected items are chosen. The report filter condition on Customer Region is ignored because Customer Region is related to Customer in the metric filter.
In the report shown below, notice the revenue for the two years; as the options are changed in the following reports, the calculated revenue amount will change.
This report is referred to as the 1-Merge report filter/Remove report in the following sections.
The report filter criteria is applied first, then the metric filter is applied to those results. All report filter elements, regardless of whether they are related to elements in the metric filter, are included in the criteria.
In the sample report, the 75 most profitable items among customers in the specified Customer Regions are selected; these are the results from the report filter. The metric filter is applied to those results, so customers in the specified Customer Regions who bought more than 20 units of the selected items are chosen. The results are shown below.
This report is referred to as the 2-Merge report filter/Keep report in the following sections.
In this case, the calculated revenue is the same as the previous report (1-Merge report filter/Remove report), because the top 75 items among all customers are the same items as the top 75 items in the specified Customer Regions. Since the items are the same, whether Customer Region is removed from the filter does not change the final results.
You can see samples of the SQL in SQL samples of “Merge report filter into metric” reports to see that the reports are different, even though the results are the same.
The metric filter criteria is applied to the data first. Then the report filter is applied to the results of the metric filter evaluation. Report filter elements that are related to the metric filter are ignored.
In the sample report, the customers who bought more than 20 units of any items are selected; these are the results from the evaluation of the metric filter. The report filter is applied to those results, so the 75 most profitable items among the selected customers are chosen. Notice that Customer Region does not figure in any of these evaluations. This is because the report filter condition on Customer Region is ignored since Customer Region is related to Customer in the metric filter. The report results are displayed below.
This report is referred to as the 3-Merge metric filter/Remove report in the following sections.
The calculated revenue amount is significantly larger than the previous two reports (the “Merge report filter” reports), since the number of customers included on the report is larger. This report includes all the high-volume customers, regardless of what items they bought. It then calculates the revenue of the most profitable items that those customers bought. The “Merge report filter” reports select the most profitable items first, then include only those high-volume customers who bought those particular items. The number of customers is therefore smaller in the “Merge report filter” reports.
The report filter is applied to the results of the metric filter, which includes all its elements.
In the sample report, customers who bought more than 20 units of any item are selected; these are the results from the metric filter. The report filter is applied to those results, so the 75 most profitable items from the selected customers who are in the specified Customer Regions are chosen. The report results are shown below.
This report is referred to as the 4-Merge metric filter/Keep report in the following sections.
As with the 3-Merge metric filter/Remove report, the metric values are significantly higher than the “Merge report filter” reports ($3,419,930 vs. $1,169). This is for the same reason—this report includes all the high-volume customers, regardless of what items they bought. However, these metric values are lower than the 3-Merge metric filter/Remove report because the 4-Merge metric filter/Keep report is filtered on Customer Region. The metric calculates revenue only for customers in the selected Customer Regions. The other report was calculated for all high-volume customers, regardless of the Customer Region.
The metric filter and report filter are calculated independently, and then the results are intersected during the metric calculation. If any report filter elements are related to any metric filter elements, the report filter elements are not included in the new filter.
In the sample report, the customers that bought more than 20 units of any items are selected. Note that the units do not have to be of the same item. This condition comes from the metric filter. The report filter determines the top 75 most profitable items, using all customers not just the selected high-volume customers. Finally, the sales of those 75 items only to the selected customers is calculated.
Notice that Customer Region does not figure in any of these evaluations. This is because the report filter condition on Customer Region is ignored since Customer Region is related to Customer in the metric filter.
In short, this report calculates the revenue for the 75 most profitable items, based on all customers, sold to high-volume customers. The results are shown below.
This report is referred to as the 5-Merge into new/Remove report in the following sections.
Notice that the revenue values are closer to those in the 3-Merge metric filter/Remove report than in the 1-Merge report filter/Remove report. The difference is that this report determines the revenue for the 75 most profitable items on all customers, while the 3-Merge metric filter/Remove report calculates the revenue for the 75 most profitable items sold to high-volume customers only. The list of items included in the revenue calculation therefore differs. However, both reports start with the same list of customers, which is customers who have purchased more than 20 of any and all items.
The results of the metric filter and report filter are intersected when the metric is calculated. All report filter elements, regardless of whether they are related to elements in the metric filter, are used to determine the results of the report filter.
In the sample report, the metric filter determines the high-volume customers. The report filter determines the top 75 most profitable items, using all the customers in the specified Customer Regions, not just the high-volume customers. When the revenue metric is calculated, it determines the sales of the selected items to the high-volume customers in the specified Customer Regions. The results are shown below.
This report is referred to as the 6-Merge into new/Keep report in the following sections.
Notice that the revenue values are closer to those in the 4-Merge metric filter/Keep report than in the 2-Merge report filter/Keep report. The 4-Merge metric filter/Keep report and this report include all the high-volume customers, regardless of what items they bought. The metric values are lower than the 5-Merge into new/Keep report because this report filters for Customer Region.
Notice that the related report filter elements do not appear in the diagram, since Remove related report filter elements is selected. The results for the conditional metric are represented by the cross-hatched circle.
Notice that this diagram includes all report filter elements, even those related to the metric filter.
Again, this diagram does not include the related report filter elements since Remove related report filter elements is selected. The difference between this diagram and the first diagram is the outer circle—in the first diagram it is the report filter, which is applied first. In this diagram, it is the metric filter that is applied first.
The report filter is applied to the results of the metric filter. This diagram includes all report filter elements, even those related to the metric filter, since Remove related report filter elements is cleared.
The metric filter and the report filter are intersected. Only those results that satisfy the qualifications of both the metric filter and the report filter are returned. The intersection of the two filters, as shown by the cross-hatched area in the following diagram, is the data returned by the conditional metric on the report.
Notice that the related report filter elements do not appear in the diagram, since Remove related report filter elements is selected.
As with the previous diagram, the metric filter and the report filter are intersected. The difference is the third circle, which represents the related report filter elements. Since Remove related report filter elements is cleared, those filter qualifications are intersected with the other qualifications.
This is not the exact SQL from the reports; the samples have been edited for better readability.
Remove related report filter elements selected
select ORDER_DETAIL.CUSTOMER_ID AS CUSTOMER_IDfrom ORDER_DETAIL, TempTablewhere ORDER_DETAIL.ITEM_ID = TempTable.ITEM_IDgroup by ORDER_DETAIL.CUSTOMER_IDhaving sum(ORDER_DETAIL.QTY_SOLD) > 20.0select LU_DAY.YEAR_ID AS YEAR_ID,sum(REVENUE) from ORDER_DETAIL, TempTable, LU_DAYwhere ORDER_DETAIL.CUSTOMER_ID = TempTable.CUSTOMER_IDand ORDER_DETAIL.ITEM_ID = TempTable.ITEM_ID and ORDER_DETAIL.ORDER_DATE = LU_DAY.DAY_DATEgroup by LU_DAY.YEAR_ID
Remove related report filter elements cleared
select ORDER_DETAIL.CUSTOMER_ID AS CUSTOMER_IDfrom ORDER_DETAIL, TempTable, LU_CUSTOMER, LU_CUST_CITY,LU_CUST_STATEwhere ORDER_DETAIL.ITEM_ID = TempTable.ITEM_ID andORDER_DETAIL.CUSTOMER_ID = LU_CUSTOMER.CUSTOMER_ID and LU_CUSTOMER.CUST_CITY_ID = LU_CUST_CITY.CUST_CITY_ID and LU_CUST_CITY.CUST_STATE_ID = LU_CUST_STATE.CUST_STATE_ID and LU_CUST_STATE.CUST_REGION_ID in (7, 6, 4, 5)group by ORDER_DETAIL.CUSTOMER_IDhaving sum(ORDER_DETAIL.QTY_SOLD) > 20.0select LU_CUST_CITY.YEAR_ID AS YEAR_ID,sum(REVENUE)from ORDER_DETAIL, TempTable, LU_DAY,LU_CUSTOMER, LU_CUST_CITY, LU_CUST_STATEwhere ORDER_DETAIL.ITEM_ID = TempTable.ITEM_ID and ORDER_DETAIL.CUSTOMER_ID = TempTable.CUSTOMER_ID and ORDER_DETAIL.ORDER_DATE = LU_DAY.DAY_DATE and ORDER_DETAIL.CUSTOMER_ID = LU_CUSTOMER.CUSTOMER_ID and LU_CUSTOMER.CUST_CITY_ID = LU_CUST_CITY.CUST_CITY_ID and LU_CUST_CITY.CUST_STATE_ID = LU_CUST_STATE.CUST_STATE_IDandLU_CUST_STATE.CUST_REGION_ID in (7, 6, 4, 5)group by LU_DAY.YEAR_ID
You can change these defaults, however. Choose the embedding method and whether to remove related elements, then select the Remember option setting check box. When you create another conditional metric, the new defaults are used.
You want to identify the bottom 10 items in terms of revenue but you want to place an additional qualification to include only those items where sales are greater than $100. You place the Bottom 10 qualification in the report filter and the Revenue greater than $100 qualification in the metric condition. The results are shown below:
Only 3 rows are returned, instead of the 10 you expected based on your Bottom 10 qualification. Why?
By default, the report filter is applied first and then the metric filter is applied to that result set. This means that the bottom 10 sales are determined first, and then, of those bottom 10 sales, only those items with sales greater than $100 are returned on the report. The number of rows on the report is only 3, since most of the 10 items returned by the report filter have sales less than $100.
If you place the bottom 10 qualification in the metric and the revenue greater than $100 qualification in the report filter, the report results change, as shown below:
The report contains 10 rows, as expected, and all items have a revenue higher than $100. Since the report filter is applied first, all items with sales greater than $100 are found first. Then the metric condition is applied to those results. The bottom 10 revenue items are then selected, so the report returns 10 rows.
To get the results you want, you can switch the qualifications, as shown in these examples above, or you can change the embedding method. The previous two examples used the default embedding option, which is Merge report filter into metric.
Change the embedding option to Merge metric condition into report. Now the metric filter is evaluated first, and then the report filter is applied to those results. Therefore, the bottom 10 sales are determined and then, of those bottom 10, only those items with sales greater than $100 are returned on the report. The report, shown below, is identical to the first report example. Only 3 rows are returned, since 7 of the 10 items returned by the metric filter have sales less than $100.
Change the embedding option to Merge into new, and the metric filter and the report filter are intersected. Only those items that are in the bottom 10 in terms of sales and have sales greater than $100 should be included. The results should be the same as the above report sample, with 3 rows of data.
However, the report contains 10 rows of data. The bottom 10 items are listed, but some of them have revenue of less than $100. It does not seem that the report filter is being applied. Why? The embedding method is not the only option for conditional metrics that affects the interaction between filters. The Remove related report filter elements setting also influences the relationships between filters. By default, related report filter elements are removed. In this example, the filters use the same element (Item), so the report filter is ignored. Only the metric filter, for the bottom 10 sales, is applied. For a detailed description of how this setting works, see Remove related report filter elements. For a description of how the two settings interact, including a list of all the possible combinations, see Combining the embedding method and remove related elements settings.
Conditional metric and report filter interactions
When a report contains a filter and a conditional metric, the report filter and the filter contained in the metric (the metric filter or metric condition) interact to produce the data for the conditional metric. The advanced options for the conditional metric establish this interaction. These options are described below.| • | The Embedding method determines how the filters are merged by setting which filter is evaluated first. By default, the report filter is applied first. |
| • | The Remove related report filter elements option is used when the report filter includes a qualification that uses an attribute related to an attribute in the metric condition. By default, the related attributes in the report filter are ignored. |
Embedding method
Merging the report filter and the metric filter (also known as the metric condition) is accomplished by embedding one filter in the other or by embedding both filters in a new, empty filter. The options are described below.The following diagrams represent the logic of the calculations, not the actual data values calculated by the conditional metric. The cross-hatched area represents the results of the conditional metric, indicating that results are calculated in each scenario. The cross-hatching is not meant to represent that those results will necessarily be identical sets of data.
| • | Merge report filter into metric applies the report filter criteria to the data first. Then the metric filter is applied to the results of the first evaluation. This is the default setting. In the following diagram, the results for the conditional metric are represented by the cross-hatched circle. |
| • | Merge metric condition into report evaluates the metric filter first, then applies the report filter to those results. While the following diagram may look similar to the previous one at first glance, notice that the outer circle is for the conditional metric filter. |
Selecting the Merge report filter into metric option includes the symbol @2; in the metric formula.
| • | Merge into new intersects the metric filter and the report filter. Only those results that meet both the metric filter and the report filter are returned. The intersection of the two filters, as shown by the cross-hatched area in the following diagram, is the data returned by the conditional metric on the report. |
Depending on the embedding method you select, symbols are added to the metric formula as described in the table below:
| Embedding Method | Symbol |
| Merge into new | @1; |
| Merge report filter into metric | @2; |
| Merge metric condition into report | @3; |
The embedding method is relevant only if at least one filter contains a metric qualification, relationship qualification, or a shortcut-to-a-report qualification. The results from these types of qualifications can vary. For example, filtering on “country=US” always yields the same results. This is an example of an attribute qualification. However, filtering on “country where revenue is greater than $1000” can return different results if the data is for 2002 only, 2003 only, or both years combined. This is a metric qualification.
For a list of descriptions of the different types of qualifications, see Types of qualifications.
For example, you need to calculate revenue from high-volume customers, where high-volume is defined as a customer who has purchased more than 20 items. Create a conditional metric with a formula of Sum(Revenue), setting the condition as the set of customers where Units Sold > 20.
You create a report containing the High-Volume Customer Revenue metric and a filter for the 75 most profitable items. How should the High-Volume Customer Revenue metric be calculated? How should the report filter and the metric filter work together? Several possibilities exist, and the embedding method allows you to specify which possibility fits your business requirements. Note that both the metric filter and the report filter contain a metric qualification, so the embedding method is relevant to this example.
| • | Calculate the revenue for the 75 most profitable items, based on all customers, sold to the customers who bought more than 20 units of those top 75 items. |
Merge report filter into metric uses the results of the report filter to evaluate the metric filter. In this case, only one customer has bought more than 20 units of the selected items, as shown in the following report sample.
| • | Calculate the revenue for the top 75 most profitable items among customers who have purchased more than 20 units of any item. |
Merge metric condition into report uses the results of the metric filter to evaluate the report filter. Norman Klein is included on the report, as shown in the portion reproduced below, since we know from the previous example that he has bought more than 20 items.
A total of 9653 customers are selected for this report, since the filter to find the high-volume customers is not restricted to specific items; any customer buying more than 20 units of any and all items is included.
| • | Calculate the revenue for the 75 most profitable items, based on all customers, sold to customers who have purchased more than 20 items. |
Merge into new allows you to calculate the report filter and metric filter independently, then intersect the results when calculating the metric. Norman Klein is included on the report, as shown in the portion reproduced below, since we know from the first example that he has bought more than 20 items.
Notice that the revenue calculated for Hugh Abarca in this report is $2,010. In the second report, the value is shown as $1,993. Why? The list of items included in the revenue calculation differs between the two reports. The second example includes only those items sold to the high-volume customers, while the third selects from all items in the database, sold to any customer.
A total of 9655 customers is selected for this report, slightly more than the previous example. Why? The second and third examples both start with the same list of customers, which is customers who have purchased more than 20 of any and all items. However, as explained above, the list of profitable products differs between the two reports. For the second report, if a customer’s purchased units did not include any of the 75 most profitable items of the big-volume customers, he is not included on the report.
For another description of this setting, see Additional examples of the embedding method options.
Remove related report filter elements
The Remove related report filter elements setting influences the interaction between the metric filter and the report filter. When the check box is selected, if the report filter contains a qualification based on an attribute related to an attribute in the metric filter qualification, the metric filter qualification takes precedence. This is the default.Depending on the embedding method you select, symbols are added to the metric formula as described in the table below:
| Embedding Method | Symbol |
| Merge into new | @1; |
| Merge report filter into metric | @2; |
| Merge metric condition into report | @3; |
For example, a report contains a Revenue metric and the Category attribute. The metric filter is set to the New York Call Center only and the report filter to all southern regions (South, Southeast, and Southwest).
Since Call Center and Region are related because they belong to the same hierarchy, the Remove related report filter elements setting is employed. The metric filter overwrites the report filter, so only New York revenue is included in the report. This setting allows you to ensure that the metric always calculates for the New York Call Center, regardless of whether other related elements are included in the report filter.
If the check box is cleared, the results are the intersection of the filters. In this case, New York and Southern regions exclude each other, so the combined filter is empty. The report returns no data. Clearing the option allows the metric filter to intersect with the report filter.
For another description of this setting, see Additional examples of the embedding method options.
The report filter itself is not changed; the setting determines the filter interaction only for the conditional metric. For example, add the Revenue metric to the previous report and see the results below.
Note that the Revenue metric does not have conditions or levels. It uses the report filter and therefore calculates revenue by category for all the southern regions.
If you select the Remove related report filter elements option, a plus (+) symbol is added after the symbol for the embedding method in the metric formula. If you clear the Remove related report filter elements option, a minus (-) symbol is added after the symbol for the embedding method in the metric formula. For information on the embedding method symbols, see Embedding method.
Combining the embedding method and remove related elements settings
The embedding method and the Remove related report filter elements setting work together to affect the interaction of the report filter and the metric filter. The following list describes all the possible combinations of these advanced settings. A report sample is included for each combination to provide a concrete example.If you create these reports, be aware that the years and data have been updated in MicroStrategy Tutorial; the reports will not display the same values as the samples.
The report samples build on the scenario described in Embedding method. You need to calculate revenue from high-volume customers, where a high-volume customer is defined as having purchased more than 20 items. To calculate that revenue, use a conditional metric with a formula of Sum(Revenue), setting the condition as the set of customers where Units Sold > 20. The report contains Year, the High-Volume Customer Revenue metric, and a filter for the 75 most profitable items in the following Customer Regions: Central, Northwest, South, and Southwest.
These examples are also explained using diagrams, in Combining the embedding method and remove related elements settings: A visual explanation.
| • | Merge report filter into metric with Remove related report filter elements selected. These are the default settings. |
In the sample report, the 75 most profitable items among customers in the specified Customer Regions are selected; these are the results from the evaluation of the report filter. The metric filter is applied to those results, so customers who bought more than 20 units of the selected items are chosen. The report filter condition on Customer Region is ignored because Customer Region is related to Customer in the metric filter.
In the report shown below, notice the revenue for the two years; as the options are changed in the following reports, the calculated revenue amount will change.
This report is referred to as the 1-Merge report filter/Remove report in the following sections.
| • | Merge report filter into metric with Remove related report filter elements cleared. |
In the sample report, the 75 most profitable items among customers in the specified Customer Regions are selected; these are the results from the report filter. The metric filter is applied to those results, so customers in the specified Customer Regions who bought more than 20 units of the selected items are chosen. The results are shown below.
This report is referred to as the 2-Merge report filter/Keep report in the following sections.
In this case, the calculated revenue is the same as the previous report (1-Merge report filter/Remove report), because the top 75 items among all customers are the same items as the top 75 items in the specified Customer Regions. Since the items are the same, whether Customer Region is removed from the filter does not change the final results.
You can see samples of the SQL in SQL samples of “Merge report filter into metric” reports to see that the reports are different, even though the results are the same.
| • | Merge metric condition into report with Remove related report filter elements selected. |
In the sample report, the customers who bought more than 20 units of any items are selected; these are the results from the evaluation of the metric filter. The report filter is applied to those results, so the 75 most profitable items among the selected customers are chosen. Notice that Customer Region does not figure in any of these evaluations. This is because the report filter condition on Customer Region is ignored since Customer Region is related to Customer in the metric filter. The report results are displayed below.
This report is referred to as the 3-Merge metric filter/Remove report in the following sections.
The calculated revenue amount is significantly larger than the previous two reports (the “Merge report filter” reports), since the number of customers included on the report is larger. This report includes all the high-volume customers, regardless of what items they bought. It then calculates the revenue of the most profitable items that those customers bought. The “Merge report filter” reports select the most profitable items first, then include only those high-volume customers who bought those particular items. The number of customers is therefore smaller in the “Merge report filter” reports.
| • | Merge metric condition into report with Remove related report filter elements cleared. |
In the sample report, customers who bought more than 20 units of any item are selected; these are the results from the metric filter. The report filter is applied to those results, so the 75 most profitable items from the selected customers who are in the specified Customer Regions are chosen. The report results are shown below.
This report is referred to as the 4-Merge metric filter/Keep report in the following sections.
As with the 3-Merge metric filter/Remove report, the metric values are significantly higher than the “Merge report filter” reports ($3,419,930 vs. $1,169). This is for the same reason—this report includes all the high-volume customers, regardless of what items they bought. However, these metric values are lower than the 3-Merge metric filter/Remove report because the 4-Merge metric filter/Keep report is filtered on Customer Region. The metric calculates revenue only for customers in the selected Customer Regions. The other report was calculated for all high-volume customers, regardless of the Customer Region.
| • | Merge into new with Remove related report filter elements selected. |
In the sample report, the customers that bought more than 20 units of any items are selected. Note that the units do not have to be of the same item. This condition comes from the metric filter. The report filter determines the top 75 most profitable items, using all customers not just the selected high-volume customers. Finally, the sales of those 75 items only to the selected customers is calculated.
Notice that Customer Region does not figure in any of these evaluations. This is because the report filter condition on Customer Region is ignored since Customer Region is related to Customer in the metric filter.
In short, this report calculates the revenue for the 75 most profitable items, based on all customers, sold to high-volume customers. The results are shown below.
This report is referred to as the 5-Merge into new/Remove report in the following sections.
Notice that the revenue values are closer to those in the 3-Merge metric filter/Remove report than in the 1-Merge report filter/Remove report. The difference is that this report determines the revenue for the 75 most profitable items on all customers, while the 3-Merge metric filter/Remove report calculates the revenue for the 75 most profitable items sold to high-volume customers only. The list of items included in the revenue calculation therefore differs. However, both reports start with the same list of customers, which is customers who have purchased more than 20 of any and all items.
| • | Merge into new with Remove related report filter elements cleared. |
In the sample report, the metric filter determines the high-volume customers. The report filter determines the top 75 most profitable items, using all the customers in the specified Customer Regions, not just the high-volume customers. When the revenue metric is calculated, it determines the sales of the selected items to the high-volume customers in the specified Customer Regions. The results are shown below.
This report is referred to as the 6-Merge into new/Keep report in the following sections.
Notice that the revenue values are closer to those in the 4-Merge metric filter/Keep report than in the 2-Merge report filter/Keep report. The 4-Merge metric filter/Keep report and this report include all the high-volume customers, regardless of what items they bought. The metric values are lower than the 5-Merge into new/Keep report because this report filters for Customer Region.
Combining the embedding method and remove related elements settings: A visual explanation
The following diagrams provide a visual reference for the combinations discussed in the previous section.| • | Merge report filter into metric with Remove related report filter elements selected. These are the default settings. |
| • | Merge report filter into metric with Remove related report filter elements cleared. |
| • | Merge metric condition into report with Remove related report filter elements selected. |
| • | Merge metric condition into report with Remove related report filter elements cleared. |
| • | Merge into new with Remove related report filter elements selected. |
Notice that the related report filter elements do not appear in the diagram, since Remove related report filter elements is selected.
| • | Merge into new with Remove related report filter elements cleared. |
SQL samples of “Merge report filter into metric” reports
The two reports with the embedding method set to Merge report filter into metric returned the same revenue values. This is just an anomaly of the Tutorial data. The following SQL samples illustrate the difference between the reports. In the second sample, the bolded items are customer data and are not included in the first sample.This is not the exact SQL from the reports; the samples have been edited for better readability.
Remove related report filter elements selected
select ORDER_DETAIL.CUSTOMER_ID AS CUSTOMER_IDfrom ORDER_DETAIL, TempTablewhere ORDER_DETAIL.ITEM_ID = TempTable.ITEM_IDgroup by ORDER_DETAIL.CUSTOMER_IDhaving sum(ORDER_DETAIL.QTY_SOLD) > 20.0select LU_DAY.YEAR_ID AS YEAR_ID,sum(REVENUE) from ORDER_DETAIL, TempTable, LU_DAYwhere ORDER_DETAIL.CUSTOMER_ID = TempTable.CUSTOMER_IDand ORDER_DETAIL.ITEM_ID = TempTable.ITEM_ID and ORDER_DETAIL.ORDER_DATE = LU_DAY.DAY_DATEgroup by LU_DAY.YEAR_ID
Remove related report filter elements cleared
select ORDER_DETAIL.CUSTOMER_ID AS CUSTOMER_IDfrom ORDER_DETAIL, TempTable, LU_CUSTOMER, LU_CUST_CITY,LU_CUST_STATEwhere ORDER_DETAIL.ITEM_ID = TempTable.ITEM_ID andORDER_DETAIL.CUSTOMER_ID = LU_CUSTOMER.CUSTOMER_ID and LU_CUSTOMER.CUST_CITY_ID = LU_CUST_CITY.CUST_CITY_ID and LU_CUST_CITY.CUST_STATE_ID = LU_CUST_STATE.CUST_STATE_ID and LU_CUST_STATE.CUST_REGION_ID in (7, 6, 4, 5)group by ORDER_DETAIL.CUSTOMER_IDhaving sum(ORDER_DETAIL.QTY_SOLD) > 20.0select LU_CUST_CITY.YEAR_ID AS YEAR_ID,sum(REVENUE)from ORDER_DETAIL, TempTable, LU_DAY,LU_CUSTOMER, LU_CUST_CITY, LU_CUST_STATEwhere ORDER_DETAIL.ITEM_ID = TempTable.ITEM_ID and ORDER_DETAIL.CUSTOMER_ID = TempTable.CUSTOMER_ID and ORDER_DETAIL.ORDER_DATE = LU_DAY.DAY_DATE and ORDER_DETAIL.CUSTOMER_ID = LU_CUSTOMER.CUSTOMER_ID and LU_CUSTOMER.CUST_CITY_ID = LU_CUST_CITY.CUST_CITY_ID and LU_CUST_CITY.CUST_STATE_ID = LU_CUST_STATE.CUST_STATE_IDandLU_CUST_STATE.CUST_REGION_ID in (7, 6, 4, 5)group by LU_DAY.YEAR_ID
Changing the condition advanced option defaults
In most circumstances, you want the report filter to apply to the conditional metric, except for qualifications that contain attributes related to attributes in the metric filter. The conditional metric should be calculated based on the specified metric condition, regardless of whether any attributes from the same hierarchy appear in the report filter. Therefore, by default, the embedding method is set to Merge report filter into metric and the Remove related report filter elements check box is selected.You can change these defaults, however. Choose the embedding method and whether to remove related elements, then select the Remember option setting check box. When you create another conditional metric, the new defaults are used.
Additional examples of the embedding method options
The following examples offer another scenario to explain how conditional metrics and report filters affect each other.You want to identify the bottom 10 items in terms of revenue but you want to place an additional qualification to include only those items where sales are greater than $100. You place the Bottom 10 qualification in the report filter and the Revenue greater than $100 qualification in the metric condition. The results are shown below:
Only 3 rows are returned, instead of the 10 you expected based on your Bottom 10 qualification. Why?
By default, the report filter is applied first and then the metric filter is applied to that result set. This means that the bottom 10 sales are determined first, and then, of those bottom 10 sales, only those items with sales greater than $100 are returned on the report. The number of rows on the report is only 3, since most of the 10 items returned by the report filter have sales less than $100.
If you place the bottom 10 qualification in the metric and the revenue greater than $100 qualification in the report filter, the report results change, as shown below:
The report contains 10 rows, as expected, and all items have a revenue higher than $100. Since the report filter is applied first, all items with sales greater than $100 are found first. Then the metric condition is applied to those results. The bottom 10 revenue items are then selected, so the report returns 10 rows.
To get the results you want, you can switch the qualifications, as shown in these examples above, or you can change the embedding method. The previous two examples used the default embedding option, which is Merge report filter into metric.
Change the embedding option to Merge metric condition into report. Now the metric filter is evaluated first, and then the report filter is applied to those results. Therefore, the bottom 10 sales are determined and then, of those bottom 10, only those items with sales greater than $100 are returned on the report. The report, shown below, is identical to the first report example. Only 3 rows are returned, since 7 of the 10 items returned by the metric filter have sales less than $100.
Change the embedding option to Merge into new, and the metric filter and the report filter are intersected. Only those items that are in the bottom 10 in terms of sales and have sales greater than $100 should be included. The results should be the same as the above report sample, with 3 rows of data.
However, the report contains 10 rows of data. The bottom 10 items are listed, but some of them have revenue of less than $100. It does not seem that the report filter is being applied. Why? The embedding method is not the only option for conditional metrics that affects the interaction between filters. The Remove related report filter elements setting also influences the relationships between filters. By default, related report filter elements are removed. In this example, the filters use the same element (Item), so the report filter is ignored. Only the metric filter, for the bottom 10 sales, is applied. For a detailed description of how this setting works, see Remove related report filter elements. For a description of how the two settings interact, including a list of all the possible combinations, see Combining the embedding method and remove related elements settings.
Thanks for your information. very good article.
ReplyDeleteMicrostrategy Certification
Microstrategy Online Training Hyderabad