Show TOC

Syntax documentation*XDIM_FILTER Locate this document in the navigation structure

Allowed uses: By Commit, MDX, SQL

*XDIM_FILTER {Dimension name} = {Members Set}

*XDIM_FILTER {Dimension name} < or > {Time member}

The member set used for a given dimension can be filtered using user-defined criteria with this instruction. This instruction does not replace the passed set with a hard-coded set, but filters the passed set with predefined criteria.

Example Example

If the members passed for the ACCOUNT dimension are Cash, Receivables and Payables, this instruction accepts only asset accounts, so that the resulting account set is limited to Cash and Receivables:

*XDIM_FILTER ACCOUNT = [account].properties("ACCTYPE")="AST"

End of the example.

If no member is passed, the filter criteria does not apply to only the noncalculated members (the default members set for all dimensions except currency), but to all members in the dimension. This could also be used as a way to modify the default filtering criteria.

The instruction automatically removes duplicates from the filtered set. This could be helpful when a returned member set contains duplicates, a situation that can easily be encountered in the entity dimension where the double hierarchy returns the same entity twice.

A typical use of this feature is to filter a list of members against one or more properties. When the members must be filtered against some values in the application (for example, only the entities that have a value <:> 0 in a given account), the instruction to use is *XDIM_GETMEMBERSET.

The *XDIM_FILTER {Dimension name} < or > {Time member} syntax also supports the %PREFIX% and %FLAG_PERIOD% keywords.

Example Example

*PROCESS_EACH_MEMBER=CATEGORY

*XDIM_FILTER CATEGORY=[CATEGORY].PROPERTIES("CALCULATE")="Y"

*FLAG_PERIOD=CATEGORY.FIRSTPERIOD

*XDIM_FILTER TIME = > %PREFIX%.%FLAG_PERIOD%

In such example, the selected categories are processed one by one. For each of them the property CALCULATE is evaluated, and only those that require calculation are processed by the logic.

Furthermore, the logic reads the property FIRSTPERIOD of the processed category, to derive the starting month from which the periods must be processed. For example, if the FIRSTPERIOD is “APR”, and the selected periods belong to year 2004, all periods preceding 2004.APR are ignored.

End of the example.