Page tree
Skip to end of metadata
Go to start of metadata

Contents:

PARENT Keywords

MAQL supports a set of keywords that can be used to override and control which parent-level filters are applied to a specified metric. When inserted into your metric definition, these keywords specify whether to apply any filters from the metric's parent metric, report, or dashboard to the data used in the metric calculation.

You can include or exclude all filters or specify the specific filters to include or exclude.

These keywords are applied to selecting data before it has been aggregated in the metric computation. For more information on applying filtering to data after it has been aggregated, see Filtering with the HAVING Clause.

The effects of these keywords are best demonstrated by example. Suppose you have the following metric (M1), which has report-level filters applied to it:

Definition

Report Filter

Metric Value

SELECT M1

(none)

500

SELECT M1

F1: Group = X

350

In the above table, you can see the value generated for the metric (M1) is 500 when no filtration is applied. When the report filter (F1) is applied, the value for the metric is 350. This example can be used to describe the effects of each of the following parent keywords.

If your metric contains multiple keywords, they must be listed in alphabetical order:

  SELECT (...) (BY ...) (FOR ...) (WHERE ...|HAVING ...) (WITH PF|WITH PF EXCEPT|WITHOUT PF|WITHOUT PF EXCEPT)

For any metric that utilizes the WITH or WITHOUT keywords to override report filters, you should include an indicator in the metric name. For example, you might append [WO/PF] to any metric's name that uses the WITHOUT PF keywords.

WITHOUT PARENT FILTER

Description
Removes parent filters from being applied to the metric. All parent filters specified in reports, dashboards, and any metrics in which this metric is nested are ignored.

Any user filters in the project are never ignored. They are always applied even if parent filters are ignored in individual metrics. See Filtering Data.

This clause can be shortened to WITHOUT PF.

Example
In the table below, you can see how the WITHOUT PARENT FILTER clause affects the filtered version of the report.

Definition

Report Filter

Metric Value

SELECT M1

(none)

500

SELECT M1

F1: Group = X

350

SELECT M1 WITHOUT PARENT FILTER

F1: Group = X

500

Since the metric in the third row removes the parent filter in the report, the returned value matches the calculation when no report filter is applied in the first row.

Syntax

SELECT ... WITHOUT PARENT FILTER
SELECT ... WITHOUT PF

WITHOUT PARENT FILTER EXCEPT

Description
Remove parent filters, except for any filters specified as a comma-separated list of attributes.

This clause can be shortened to WITHOUT PF EXCEPT.

In EXCEPT clauses, multiple attributes may be specified as a series of comma-separated values.

Excepted attribute filters are applied to the specified attribute and to all attributes of coarser granularity in the hierarchy. For example, in the hierarchy State-County-City, the values of the attribute City do not group the values of County, but County groups City because County has coarser granularity than City. So, if City is excepted, County and State are excepted too. If you apply the exception to the State attribute, it is the only attribute filter that is excepted.

Example 1

In the table below, you can see how the WITHOUT PARENT FILTER EXCEPT keyword works with the State-County-City hierarchy.

Definition

Report Filter

Metric Value

SELECT M1

(none)

1000

SELECT M1

F1: County = X

600

SELECT M1 WITHOUT PF

F1: County = X

1000

SELECT M1 WITHOUT PF EXCEPT City

F1: County = X

600

SELECT M1 WITHOUT PF EXCEPT State

F1: County = X

1000

SELECT M1 WITHOUT PF EXCEPT State

F1: State = Y

550

  • In the above example in the third row, the WITHOUT PARENT FILTER removes all parent filters from the metric, so COUNTY = X is not applied. The returned results match the unfiltered version of the metric in the first row.
  • In the fourth row, City is excepted. Since County has coarser grain than City, the parent report filter (County = X) is applied.
  • In the fifth row, the excepted filter is State. However, the report filter is at the County attribute level, which is finer than State. Therefore, the filter is not applied, and the results match the unfiltered version in the first row.
  • In the fifth row, the excepted filter is State, which matches the report filter. So, the results are filtered.

Example 2
In the table below, you can see how the WITHOUT PARENT FILTER EXCEPT keyword affects the filtered version of the report.

Definition

Report Filter

Metric Value

SELECT M1

(none)

500

SELECT M1

F1: Group = X

350

SELECT M1

F1: Group = X;F2: Priority is Y

250

SELECT M1 WITHOUT PARENT FILTER EXCEPT Group

F1: Group = X;F2: Priority is Y

350

In the above example in the fourth row, the WITHOUT PARENT FILTER EXCEPT removes all parent filters from the metric, except for one: Group, so the Priority filter is not applied to the metric. The generated metric value equates to the value generated in the version in the second row.

Syntax

SELECT ... WITHOUT PARENT FILTER EXCEPT Attribute1,...
SELECT ... WITHOUT PF EXCEPT Attribute1,...

WITH PARENT FILTER

Description
Applies all parent filters to the metric. All parent filters specified in reports, dashboards, and any metrics in which this metric is nested are applied to the calculation.

This clause can be shortened to WITH PF.

The WITH PARENT keyword is applied by default when other report filters are not present.

Example
In the table below, you can see how the WITH PARENT FILTER clause affects the filtered version of the report.

Definition

Report Filter

Metric Value

SELECT M1

(none)

500

SELECT M1

F1: Group = X

350

SELECT (SELECT M1 WITH PARENT FILTER) WHERE Group = Y

F1: Group = X

null

In the third row above, the definition includes a nested metric in which parent filters are forced to be included from inside the nested metric.

  • In the parent metric is the filter Group = Y.
  • At the report level, the filter is Group = X.
  • The WITH PARENT FILTER keyword means that all parent filters are applied to the metric. During evaluation, both Group = X and Group = Y are applied to the data as a logical AND. Since a data element may be associated with a single attribute value, there are no data elements that match both filters, and the result set is null.

Syntax

SELECT ... WITH PARENT FILTER
SELECT ... WITH PF

WITH PARENT FILTER EXCEPT

Description
Includes all parent filters, except for any filters specified as a comma-separated list of attributes.

This clause can be shortened to WITH PF EXCEPT.

In EXCEPT clauses, multiple attributes can be specified as a series of comma-separated values.

Excepted attribute filters are applied to the specified attribute and to all attributes of coarser granularity in the hierarchy. For example, in the hierarchy State-County-City, the values of the attribute City do not group the values of County, but County groups City because County has coarser granularity than City. So, if City is excepted, County and State are excepted too. If you apply the exception to the State attribute, it is the only attribute filter that is excepted.

Example

In the table below, you can see how the WITH PARENT FILTER EXCEPT keyword affects the filtered version of the report.

Definition

Report Filter

Metric Value

SELECT M1

(none)

500

SELECT M1

F1: Group = X

350

SELECT M1

F1: Group = X;F2: Priority is Y

250

SELECT M1 WITH PARENT FILTER EXCEPT Priority

F1: Group = X;F2: Priority is Y

350

In the above example in the fourth row, the WITH PARENT FILTER EXCEPT includes all parent filters in the metric, except for one: Priority, so in this case, the Priority filter is not applied to the metric. The generated metric value equates to the value generated in the version in the second row.

Syntax

SELECT ... WITH PARENT FILTER EXCEPT Attribute1,...
SELECT ... WITH PF EXCEPT Attribute1,...
  • No labels