Rudimentary Conversion Functions
Conversion functions become necessary anytime we need to convert data to a different data type. Real-world examples abound. For the purposes of our tutorial, we will assume a business need that will use a member property (which we have already discovered to be a string data type) as a number in an expression. MDX is not endowed with conversion functions of this sort, but allows us 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 data type.
With the MyCalcMem Calculated Member that we have created, we will attempt to display individual store square footage after a decrease of approximately 300 square feet for each store that has been budgeted for development as local receipts staging rooms. 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 data type is currently string.)
We will 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 our objective:
- To prepare for the example, drag the Store dimension from the top pane down, once again, to "swap" its position with the Product dimension in the row axis. The lower pane of the Cube Editor (Data Tab) should resemble that shown in Illustration 18.
Illustration 18: The Store Dimension replaces the Product Dimension in the lower Data Tab
- Select the Value property for MyCalcMem.
- Click the ellipses button and change the expression to the following:
- Click OK and ensure that the information returned resembles the following:
Illustration 19: The Initial Consequences of the Use of Strings as Numbers
If we drill down to the individual store (by double-clicking each level under Canada; for instance: Canada -> BC -> Vancouver and Victoria), as we show in Illustration 20, 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.
Illustration 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, non-additive 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 will 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," we will proceed as follows:
- Type the following into the Value Expression box:
IIF(Store.CurrentMember.Level.Name="Store Name", Store.CurrentMember.properties("Store Sqft"),"")
- Click OK. We have now achieved our objective of screening out rollup levels, as seen below in Illustration 21. Now we need to convert the square footage string so that we can perform a mathematical operation upon it.
Illustration 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 that illustrated below.
Illustration 22: The Result Set with Number Data Types
While the results appear the same as those in Illustration 21 above, the MyCalcMem column now contains a number data type instead of a string data type. Our changes were simply to 1) add conversion function CDbl, with its parenthesis around the property value it is converting to a number, and 2) to 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 (the projected square footage reduction for the proposed layout changes we outlined earlier) at its end. Our expression should now resemble the expression shown below:
Illustration 23: The Expression as Required to Modify the Stores' Floor Space
- Click OK, and compare the result set to that shown in Illustration 24 below:
Illustration 24: Partial Result Set depicting the Projected Floor Space Reductions
Page 8: More on MDX Expressions
See All Articles by Columnist William E. Pearson, III