*SELECT

Allowed uses are: By Global, MDX, SQL

*SELECT ({variable}, {What}, {From}, {Where})

Allows you to retrieve a list of elements from a dimension and save it in a user-defined variable for later use anywhere else in the rules.

Example

*SELECT(%REPORTING_CURRENCIES%, "ID", "CURRENCY", "[GROUP] = 'REP'")

In this case, you retrieve the ID of all members in the CURRENCY dimension where the property GROUP has the value REP. Running this example fills the variable %REPORTING_CURRENCIES% with the list of reporting currencies defined in the current model

Important information

The SELECT statement generates a SQL query, and not an MDX query. This implies that it can be executed against any SQL table existing in the environment database, and not just against the properties of a dimension in the model. The prefix mbr is automatically added to any name entered in the table parameter. Otherwise, the name of the table is taken as is. If you want to select a list of elements from a dimension using an MDX query, see the *MEMBERSET statement.
In case any parameter contains embedded commas the entire parameter must be enclosed in an extra set of double quotes.
The *SELECT statement is executed at the time that the logic is validated, and the expanded result is written in the LGX file. This means that if the related dimension is modified, it may be necessary to revalidate the rules.
Statements returning no members do not necessarily cause the validation of the rules to fail. In this case, the model writes a warning in the validation log.
These instructions are not specific to a given logic section, but they can be written once anywhere in the rules and used across multiple commit sections. The following example works correctly.

Example
//example --------------------------------------------------------------------------------

*SELECT(%INCACC%, "[ID]", "ACCOUNT", "ACCTYPE='INC'")

*XDIM_MEMBERSET ACCOUNT=%INCACC%

[category].[#realistic]=[category].[actual] *1. 2

*COMMIT

*XDIM_MEMBERSET ACCOUNT=%INCACC%

[category].[#optimistic]=[category].[actual] *1. 3

//end of example -----------------------------------------------------------------------

Note also that, since the *SELECT statement is expanded first, it can be placed anywhere in the logic. The above example would work even if the select statement was the LAST line in the logic, like this:

//example --------------------------------------------------------------------------------

*XDIM_MEMBERSET ACCOUNT=%EXPACC%

[category].[#realistic]=[category].[actual] * 1.2

*COMMIT

*XDIM_MEMBERSET ACCOUNT=%EXPACC%

[category].[#optimistic]=[category].[actual] *1. 3

*SELECT(%EXPACC%, "[ID]", "ACCOUNT", "ACCTYPE='INC'")

//end of example -----------------------------------------------------------------------

The special format **SELECT( )

The instruction SELECT can also be triggered with a double leading asterisk. This triggers the execution of the instruction before any expansion is performed on the logic file (such as function substitutions or file inclusions). This feature can be useful in case the result of the SELECT instruction is needed to control other tasks such as INCLUDE or RUNLOGIC, which could be dependant on the result of the SELECT itself.

Example

//------------------------------------------------------------------------------------------

**SELECT(%MYFILE%, [filename], MyTable, "[CATEGORY]='%CATEGORY_SET%' AND [ENTITY]=%ENTITY_SET%'")

*INCLUDE %MYFILE%.LGF

//------------------------------------------------------------------------------------------

In the above example an included rules file is derived from an entry in a special table using the passed category and entity as key. Note that in this case the keywords %CATEGORY_SET% or %ENTITY_SET% must contain only one member each.

Note

A rule containing a **SELECT instruction can only be executed in LGF format. Such rules might also refuse to validate if the result of the instruction is a function of the region passed at runtime, like in the above example.