Use WITHOUT PARENT for Percentage Baselines

The WITHOUT PARENT FILTER clause blocks the propagation of report filters to the report's metrics.

This clause is frequently used for computing a base value in percentage expressions. To demonstrate the capabilities of the WITHOUT PARENT FILTER, the following example uses the default date dimension to calculate the number of weekends in a specific year.

  1. In the Simple Metric Editor, create a COUNT metric for the number of the Date attribute. Name this metric: # of Days.
  2. Create the same metric again, and add the WITHOUT PARENT FILTER clause to it. Name this metric: # of Days (WPF). This metric must be specified in the Custom Metric Editor:
    SELECT COUNT( Date ) WITHOUT PARENT FILTER

  3. For the third metric, create the following in the Custom Metric Editor to divide the first metric by the second. Name this metric:  % of Weekends :
    SELECT # of Days / # of Days (WPF)

  4. Add the Year  attribute to the report. Both COUNT metrics yield the same number for each year:
    Count of days by year with and without parent filter

    However, if the report is filtered to remove all business days from the report:
    Count of days by year with and without parent filter filtered for the weekend days

    The report filter has been applied to the first metric only. The WITHOUT PARENT FILTER clause blocked the propagation of the report filter to the second metric. Now, filter the report to show only the last six years. The second metric doesn't force the report to show all years.
    Report filtered for only the last six years
  5. Drill from years down to quarters. The WITHOUT PARENT FILTER clause remains in effect on the second metric.
    WITHOUT PARENT filter applies on drill paths to the same report