You can create a calculated member that calculates the days using the VBA
functions DateDiff() and CDate(). These are not listed in the functions
list, but they will work. Lookup "Visual Basic for Applications functions"
in books online help to get a complete list of such VBA functions that are
available. To avoid confusion with a measure called CDate you may need to
prefix this function with "VBA!".

E.g.: DateDiff( "d", VBA!CDate( Measures.CDate.Name), VBA!CDate(
Measures.DDate.Name) )

Next you create a hidden member that is simply a count of the entities.

Then you can create another calculated member that calculates the average by
dividing the days by the count .

Hope this helps,
- Matt Carroll
--
This posting is provided "AS IS" with no warranties, and confers no rights.

"devnoronha" <member33654 (AT) dbforums (DOT) com> wrote

Thanks Matt ,
How does one access the cube data in VBA ? I've done VBA scripts for
Excel . Is it the same ?
How exactly does the VBA function know what data to work on (how data is
passed) and how it returns the results ?

Thanks for the reply
Dev

--
Dev Noronha


Posted via http://dbforums.com

You dont use VBA in the way you mention, you still write MDX in the
calculated member definiton.

Within the MDX expression you may refer to VBA functions (e.g. Left,
InStr or DateDiff) and you can pass in strings/numbers, and return
strings/numbers.

If CDate and DDate are the names of your Measures, then create a new
calculated member, within the Measures dimension, and use this MDX
expression:
VBA!DateDiff("d", VBA!CDate([Measures].[CDate]),
VBA!CDate([Measures].[DDate]))

Thanks Magnus ,
That worked ... it did not give an error while creating the
calculated member ,but at run time i get #ERR in all the cells .
Possibly because some fields have NULL in the 'Cdate' and 'Ddate'
fields . Is that the problem ?

Also, how do I get the Count as Matt suggested ? And after that
the Average .

Thanks a lot

--
Dev Noronha


Posted via http://dbforums.com