You are viewing our older product's guide. Click here for the documentation of GoodData Cloud, our latest and most advanced product.

PARENT Filters

A parent filter is any data filter applied to a project object from one of its parent objects. A parent object can be considered any report that contains the metric or another metric built on the metric.

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 (M~1~), which has report-level filters applied to it:

Definition	Report Filter	Metric Value
`SELECT M1`	(none)	500
`SELECT M1`	F₁: Group = X	350

In the above table, you can see the value generated for the metric (M~1~) is 500 when no filtration is applied. When the report filter (F~1~) 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 Filter Expressions .

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`	F₁: Group = X	350
`SELECT M1 WITHOUT PARENT FILTER`	F₁: 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`	F₁: County = X	600
`SELECT M1 WITHOUT PF`	F₁: County = X	1000
`SELECT M1 WITHOUT PF EXCEPT City`	F₁: County = X	600
`SELECT M1 WITHOUT PF EXCEPT State`	F₁: County = X	1000
`SELECT M1 WITHOUT PF EXCEPT State`	F₁: 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`	F₁: Group = X	350
`SELECT M1`	F₁: Group = X;F₂: Priority is Y	250
`SELECT M1 WITHOUT PARENT FILTER EXCEPT Group`	F₁: Group = X;F₂: 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,...

Example 3

This example works with XAE updated to version 3 and newer. For more information about how to upgrade, see Upgrading XAE to Version 3.

Syntax

Definition	Report Filter
`SELECT (SELECT M1 WITHOUT PF EXCEPT Date) WHERE (SELECT M2 BY Date) > Y`	Group = X

M2 is filtered by group.
M1 is not filtered by group, only by the numeric range filter in the WHERE part.

Example 4

This example works with XAE updated to version 3 and newer. For more information about how to upgrade, see Upgrading XAE to Version 3.

Syntax

Definition	Report Filter
`SELECT (SELECT M1 WITHOUT PF EXCEPT Date) WHERE (SELECT M2 BY Date) > Y`	Group = X Date = Z

M2 is filtered by group, date.
M1 is filtered by date and the numeric range in the WHERE part.

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`	F₁: Group = X	350
`SELECT (SELECT M1 WITH PARENT FILTER) WHERE Group = Y`	F₁: 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.

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`	F₁: Group = X	350
`SELECT M1`	F₁: Group = X;F₂: Priority is Y	250
`SELECT M1 WITH PARENT FILTER EXCEPT Priority`	F₁: Group = X;F₂: 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,...

Use WITHOUT PARENT for Percentage Baselines