Few words about: VBA functions in MDX

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:

result of a query

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]

result of nested vba function

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]

mdx case

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.

Advertisements

One thought on “Few words about: VBA functions in MDX

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s