There is not well-known feature of MDX – embedded VBA functions. If we want to perform easy excel-like operations on the cube level we can accomplish that by using set of functions which are listed here.
How to use it? By using prefix vba! Like here:
WITH MEMBER vbaTest
AS
vba!left(“This is sample text”,8)
SELECT vbaTest on 0
FROM [Adventure Works]
And the result will be:
There is also possibility to nest vba functions like this:
WITH MEMBER vbaTest
AS
vba!left(vba!abs(vba!round(-23.42123,2)),1)
SELECT vbaTest on 0
FROM [Adventure Works]
There is also very useful VBA function called FORMAT where we can set specific format for our object based on data from other dimensions (for example).
WITH MEMBER [Measures].[Internet Sales Amount BY LOCAL CURRENCY]
AS
CASE
WHEN
[Customer].[Customer Geography].currentmember = [Customer].[Customer Geography].[Country].&[United States]
THEN vba!format([Measures].[Internet Sales Amount],”$#,###.00″)
ELSE vba!format([Measures].[Internet Sales Amount],”#,###.00€”)
END
SELECT [Internet Sales Amount BY LOCAL CURRENCY] ON 0,
[Customer].[Customer Geography].children ON 1
FROM [Adventure Works]
As you can see above, all measures are formatted based on location of the customer. SO if we want stick to the rule KISS (Keep it simple stupid) – we should the easiest way to achieve our goals – VBA functions in MDX could be very helpful in this.
Very useful! Thanks