Filter

Returns the tuples of a set that meet the criteria of a search condition.

Syntax

FILTER ( set, search_condition )

set The set through which to iterate.
search_condition A Boolean expression. The search condition is evaluated in the context of every tuple in the set.

Description

This function returns the subset of tuples in set for which the value of the search condition is TRUE. The order of tuples in the returned set is the same as in the input set.

Notes

If a set containing values is included with a set that is empty, only the set containing values is reported. For example, in the following expression, because of the misspelling "trxas," only the set for Cola is reported:

Read: Cola, @UDA(Market, "trxas")

Example

Example 1

This example uses the following parts of the Sample Basic outline:

Gen 2 of Year dimension All levels of Product dimension

The following expression

Filter ( [Product].levels(0).members,  [Qtr1] <= 0.00 )

returns the set of only the level-0 Product dimension members that have negative data:

400-30
-394

And the following expression

{ [Year].generations(2).members }

returns the set:

{ [Qtr1], [Qtr2], [Qtr3], [Qtr4] }

Therefore, the following query

SELECT
  { [Year].generations(2).members }
ON COLUMNS,
Filter (
        [Product].levels(0).members, 
        [Qtr1] <= 0.00 
       ) 
ON ROWS
FROM Sample.Basic

returns the grid:

Qtr1 Qtr2 Qtr3 Qtr4
400-30 -116 -84 -145 -49

Example 2

The following query on Sample Basic returns Qtr2 sales figures for products where the sales have increased by at least 10% since Qtr1.

SELECT 
{ 
  Filter (
    [Product].Members,
    [Measures].[Sales] >  
    1.1 * 
      ( [Measures].[Sales], [Year].CurrentMember.PrevMember )
  ) 
}
on columns
FROM sample.basic
WHERE ([Year].[Qtr2], [Measures].[Sales])
Cola Dark Cream
16048 11993

Example 3

The following query on Sample Basic returns sales figures for product family "100" where the monthly sales of that product family are greater than 8,570. The filtering logic is stored as a named set in the WITH section.

WITH SET [High-Sales Months] as 
' 
 Filter( 
 [Year].Levels(0).members, 
 [Measures].[Sales] > 8570 
 ) 
' 
SELECT 
   {[Measures].[Sales]} 
ON COLUMNS, 
    {[High-Sales Months]}    
ON ROWS 
FROM 
  sample.basic 
WHERE 
 ([Product].[100]) 
Sales
Apr 8685
May 8945
Jun 9557
Jul 9913
Aug 9787
Sep 8844
Dec 8772

©2004 Hyperion Solutions Corporation. All Rights Reserved.
http://www.hyperion.com