Topic Status: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).
Returns the numeric value specified by a Multidimensional Expressions (MDX)–formatted string.
The StrToValue function returns the numeric value specified by the MDX expression. The StrToValue function is typically used with user-defined functions to return an MDX expression from an external function back to an MDX statement that can be resolved to a single cell.
When the CONSTRAINED flag is used, the MDX expression must contain only a scalar value. The CONSTRAINED flag is used to reduce the risk of injection attacks via the specified string. If a MDX expression is provided that is not directly resolvable to a scalar value, the following error appears: "The restrictions imposed by the CONSTRAINED flag in the STRTOVALUE function were violated."
When the CONSTRAINED flag is not used, the specified MDX expression can be as complex as desired as long as it resolves to a valid Multidimensional Expressions (MDX) expression that returns a single cell.
Returning the result of an MDX expression as a numeric value can be useful if the value is stored as text and you want to perform arithmetic operations on the returned values.
The following example uses the StrToValue function to return the weight of each bicycle as a value.
WITH MEMBER Measures.x AS StrToValue ([Product].[Product].CurrentMember.Properties ('Weight') ,CONSTRAINED ) SELECT Measures.x ON 0 ,[Product].[Product].[Product].Members ON 1 FROM [Adventure Works] WHERE [Product].[Product Categories].[Bikes]