Returns the tuples of a set that meet the criteria of a search condition.
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. |
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.
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")
This example uses the following parts of the Sample Basic outline:
![]() |
![]() |
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 |
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 |
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 |