Properties can be used inside an MDX query in two ways. In the first approach, you can list the dimension and property combinations for each axis set. When a query is executed, the specified property is evaluated for all members from the specified dimension and included in the result set.
For example, on the column axis, the following query will return the GEN_NUMBER information for every Market dimension member. On the row axis, the query returns MEMBER_ALIAS information for every Product dimension member.
SELECT [Market].Members DIMENSION PROPERTIES [Market].[GEN_NUMBER] on columns, Filter ([Product].Members, Sales > 5000) DIMENSION PROPERTIES [Product].[MEMBER_ALIAS] on rows from Sample.Basic
When querying for member properties using the DIMENSION PROPERTIES section of an axis, a property can be identified by the dimension name and the name of the property, or just by using the property name itself. When a property name is used by itself, that property information is returned for all members from all dimensions on that axis, for which that property applies.
Note: | When a property name is used by itself within the DIMENSION PROPERTIES section, do not use brackets [] around the property name. |
In the following query. the MEMBER_ALIAS property is evaluated on the row axis for both Year and Product dimensions.
SELECT [Market].Members DIMENSION PROPERTIES [Market].[GEN_NUMBER] on columns, CrossJoin([Product].Children, Year.Children) DIMENSION PROPERTIES MEMBER_ALIAS on rows from Sample.Basic
In a second approach, properties can be used inside value expressions in an MDX query. For example you can filter a set based on a value expression that uses properties of members in input set.
The following query returns all caffeinated products that are packaged in cans.
Select Filter([Product].levels(0).members, [Product].CurrentMember.Caffeinated and [Product].CurrentMember.[Pkg Type] = "Can") Dimension Properties [Caffeinated], [Pkg Type] on columns
The following query uses the UDA [Major Market] to calculate the value [BudgetedExpenses] based on whether the current member of the Market dimension is a major market or not.
With MEMBER [Measures].[BudgetedExpenses] AS 'IIF([Market].CurrentMember.[Major Market], [Marketing] * 1.2, [Marketing])' Select {[Measures].[BudgetedExpenses]} on columns, Market.Members on rows Where ([Budget])
The following queries use alias table names.
SELECT [Product].Members DIMENSION PROPERTIES [Default] on columns from Sample.Basic; SELECT [Product].Members DIMENSION PROPERTIES [Long Names] on columns from Sample.Basic;