Introduction to MSSQL Server 2000 Analysis Services Custom Cubes: Financial Reporting (Part I) - Page 15
January 6, 2003
At this point, we will give the Account dimension members in our cube meaningful names - names that make sense to accounting / finance knowledge workers. While many accountants and / or their counterparts in finance know the chart of accounts by account codes, from daily usage and coding conventions, other information consumers will need to know the account names. To strike a useful compromise, let's give them both, by using the Member Name Column property of our new Account dimension.
37. Click the Account dimension, to select it in the cube tree.
38. Click the Advanced tab in the Properties pane.
39. Modify the All Caption to read All Accounts.
40. Click the Account_Id level that appears below the Account dimension to select it.
41. Select the Basic tab, then the Name property.
42. Change the Name to Account.
43. Select the Member Name Column property.
44. Type the following expression into the property field:
Cstr("account"."account_id")+ ' ' +"account"."account_description"
Note that we are simply concatenating the Account ID (an Integer) and the Account Name, either or both of which we have determined might be helpful to information consumers in the use of the cube we are designing. The CStr function in the expression allows us to combine different data types in the concatenation, converting the Account ID (an integer data type) to a string to make it compatible with the string data type of the Account Description. Cube processing will fail if we do not make this conversion.
While MDX (which underlies the expressions we use in working with member properties) has no conversion functions to meet the immediate need, MDX allows us to access external functions; CStr is one of many VBA functions automatically included in MDX. (A list of available external functions can be obtained from the SQL Server 2000 Books Online, which references resources for more details within the MSDN Library and other documentation.
45. Press the Enter key.
The Properties pane, with the modifications, should appear as shown below: