The value of an MDX property in Essbase can be a numeric, Boolean, or string type. MEMBER_NAME and MEMBER_ALIAS properties return string values. LEVEL_NUMBER, and GEN_NUMBER properties return numeric values.
The attribute properties return numeric, Boolean, or string values based on the attribute dimension type. For example, in Sample.Basic, the [Ounces] attribute property is a numeric property. The [Pkg Type] attribute property is a string property. The [Caffeinated] attribute property is a Boolean property.
Essbase allows attribute dimensions with date types. The date type properties are treated as numeric properties in MDX. When comparing these property values with dates, use the TODATE function to convert date strings to numeric before comparison.
The following query returns all Product dimension members that have been introduced on date 03/25/2007. Because the property [Intro Date] is a date type, the TODATE function must be used to convert the date string “03-25-2007” to a number before comparing it.
SELECT Filter ([Product].Members, [Product].CurrentMember.[Intro Date] = TODATE("mm-dd-yyyy","03-25-2007"))ON COLUMNS FROM Sample.Basic
When a property is used in a value expression, you must use it appropriately based on its value type: string, numeric, or Boolean.
You can also query attribute dimensions with numeric ranges.
The following query retrieves Sales data for Small, Medium, and Large population ranges.
SELECT {Sales} ON COLUMNS, {Small, Medium, Large} ON ROWS FROM Sample.Basic
When attributes are used as properties in a value expression, you can use range members to check whether a member's property value falls within a given range, using the IN operator.
For example, the following query returns all Market dimension members with the population range in Medium:
SELECT Filter( Market.Members, Market.CurrentMember.Population IN "Medium" ) ON AXIS(0) FROM Sample.Basic