Advanced Kepion users might have considered using Attribute values to inject Dimension Members through custom MDX dynamically. You need to use a Properties AND a StrtoMember function to do so. This article will explain each function and then show you an example.
Properties Function
The Properties function allows users to return Attribute values as strings:
[Dimension].[Hierarchy].Properties("AttributeName")
To clarify, an Attribute value refers to the user-inputted text in your Attribute columns. Below, we have an Account Type Attribute. Asset and Equity would be the Attribute values.
The Properties function alone returns the desired value but in quotations (i.e., as a string). If you want to use the value as a Dimension Member, those quotations prevent MDX from recognizing the value. That's where the StrtoMember function comes into play.
StrtoMember Function
The StrtoMember function converts a string to a Dimension Member for use in an MDX query:
StrToMember("[DimensionX].[HierarchyX].&[" + ${[DimensionY].[HierarchyY]}.Properties("AttributeName") + "]")
${[DimensionY].[HierarchyY]} represents a Filter Variable, which resolves using the StrtoMember function as follows:
- StrToMember('[DimensionX].[HierarchyX].&[' + [DimensionY].[HierarchyY].&[Filter Selection].Properties('AttributeName') + ']')
- StrToMember('[DimensionX].[HierarchyX].&[' + 'Attribute value' + ']')
- StrToMember('[DimensionX].[HierarchyX].&[Attribute value]')
- [DimensionX].[HierarchyX].&[Attribute value]
With this function, you can take your Attribute values and apply them in your custom MDX as you would any other Member.
Example
For example, each of your scenarios has a different start month. You want your Form to change the month based on your Scenario Filter selection dynamically, so you create an Attribute to hold all your start months. We've called it ForecastStartMonth.
We then create a Standard Variable, using our StrtoMember function as its definition:
StrToMember("[Time].[Months].&[" + ${[Scenario].[Scenario]}.Properties("ForecastStartMonth") + "]")
We put Scenario and Months on the Form's columns. The former has also been set as a Filter Variable.
You can see how changing the Scenario resolves to its corresponding start month in the GIF below:
