Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Basic Conversion Functions
Conversion functions become necessary anytime we need to convert data to a different datatype. Real-world examples abound. For the purposes of this tutorial, we'll assume a business need that will use a member property (which we've already discovered to be a string datatype) as a number in an expression. MDX is not endowed with conversion functions of this sort, but allows access to external functions. (For information about the external functions that are available for use with MDX, see the Analysis Services Books Online.)
If we attempt simply to use member properties as numbers in our MDX expressions, MDX will return a syntax error. To avoid errors when we need to use a member property in an arithmetic context, we use an external function such as CDbl in VBA (an example of a function that converts a string to a number), to convert our string to a working number datatype.
With the MyCalcMem calculated member that we've created, we'll attempt to display individual store square footage after a decrease of approximately 300 square feet that has been budgeted for development as a local receipts staging room. The requirement in this simple example is that we subtract 300 square feet from each store's total square footage (a detail-level property of the Store dimension members, called Store Sqft, whose datatype is currently string).
We'll enhance our understanding of member properties, as well as look forward with a conditional element in our expression building, by taking the following steps to accomplish this objective:
To prepare for the example, drag the Store dimension from the top pane down again, to swap its position with the Product dimension in the row axis (see Figure 18).
Figure 18 The Store dimension replaces the Product dimension in the lower
data tab.Select the Value property for MyCalcMem.
Click the ellipsis (...) button and change the expression to the following:
[Store].CurrentMember.Properties("Store Sqft")Click OK. The information returned should resemble Figure 19.
Figure 19 The initial consequences of the use of strings as
numbers.If we drill down to the individual store (as shown in Figure 20) by double-clicking Canada, BC, Vancouver and Victoria, we see that the square footage appears as numbers for the Store members. This is because the "numbers" that we see are actually detail-level member properties for the Store dimension members. As strings, they appear at the Store 19 and Store 20 level in our example.
Figure 20 The square footage member property appears correctly at the
Store level.Let's remove the #ERR values for the rollup members. (These occur because, while numbers roll up to summary lines, strings cannot do so.) A less confusing way to present this might be to show square footage for non-rollup values only. To do this, we'll add a function to filter out the rollup values.
To build into the Value property a filter that will make possible the display of "all except rollup values," proceed as follows:
Type the following into the Value Expression box:
IIF(Store.CurrentMember.Level.Name="Store Name",
Store.CurrentMember.properties("Store Sqft"),"")NOTE
The code-continuation arrow (
) indicates that the code should all be
typed on one line. The line is broken here for space considerations.Click OK. We've now achieved our objective of screening out rollup levels, as shown in Figure 21. Now we need to convert the square footage string so that we can perform a mathematical operation on it.
Figure 21 The #ERR result is screened out by the addition of a
conditional statement.Return to the Value Expression box and change the Value property to the following:
IIF(Store.CurrentMember.Level.Name="Store Name",CDbl(Store.CurrentMember.properties("Store Sqft")),Null)Click OK. Compare the result set to Figure 22.
Figure 22 The result set with number datatypes.While the results appear to be the same as those in Figure 21, the MyCalcMem column now contains a number datatype instead of a string datatype. Our changes were simply to a) add conversion function CDbl, with its parentheses around the property value it's converting to a number, and b) place a null in the function instead of an empty string (""). Since the CDbl function is registered on our systems as a VBA function, we can include it in an MDX expression.
Let's finalize the Value Expression for MyCalcMem by placing -300 at its end. This value represents the projected square footage reduction for the proposed layout changes outlined earlier. The final expression should resemble the one in Figure 23.
Figure 23 The expression as required to modify the stores' floor
space.Click OK and then compare the result set to that shown in Figure 24.
Figure 24 Partial result set depicting the projected floor space
reductions.
)
Figure 18)
Figure 19)
Figure 20)
Figure 21)
Figure 22)
Figure 23)
Figure 24