VBA functions in MDX?!?
-
Can someone provide an example of using a VBA function in mdx?
For example, if I were to use the vba LOG() function, how do I write the mdx so that it is set-based in the same way that the mdx AVG() requires a set?
My goal is to calculate a geometric average in mdx. I can calculate it in t-sql using three nested functions: geomean = exp(avg(log('units'))). So, if I can get this equation in mdx, I'd be thrilled!
Thanks in advance.
-
Some additional clarification:
I'm trying to create a calculated member in AS that computes a geometric mean; not trying to use mdx in excel vba. Only way to accomplish this, I think, is to use VBA functions in the MDX expression. Since the AVG() function requires a set, I would think that exp() and log() would need to be set-based even though AS doesn't prompt for a set.
-
Hey Peter,
Using the formula from your first post, I get a result using the following on Foodmart:Sales.
WITH
MEMBER [Measures].[bob] AS 'EXP(AVERAGE(LOG([Measures].[Unit Sales])))'
SELECT
{[Measures].[bob]} on 0,
{[Gender].members} on 1
FROM Sales
Note that it's using the Excel/VBA average function rather than the MDX Avg function, actually all functions used above are the VBA ones.
Steve.
-
Just further clarification on the last post. After a little research, it looks like i can't send thru the base for the log function, and instead of it defaulting (as per office 2003 doco) to base 10, it defaults to base e. So, when using the mdx as posted above, the exp of the avg of the log of [unit sales] ends up being the same number...
Steve.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply