I just created a cube in Analysis Services 2000 where I'm trying to get
average length of telephone calls. The calls are represented in
seconds but I would like to display these values in a nn:ss (n=minute,
s=second) format.

What I've tried so far:
I've actually tried using this "nn:ss" in the format string property of
the measure. I believe the numeric values are being represented as
hours (e.g. 300 turns into 25 instead of 5 meaning the divisor is 12
instead of 60), but when I convert my seconds to hours in MDX, it's
completely off.

I am not horribly familiar with VB but after doing some function
browsing I can't seem to find a suitable VB function that fits my needs
for just straight time conversion.

Also, I'm trying to use a Calculated Member to accomplish this in order
for the dimension rollups to look correct. If I do this within SQL and
import the proper values via the fact table, I would have to create a
custom rollup in each dimenision of the cube (in order for the numbers
to aggregate correctly), which just isn't reasonable.


My question is really a four part question, so here it goes:

a) Is there a Seconds to Minutes conversion function that works in MDX,
be it in VB, MDX, or some other language that MDX is compatible with?

b) Is there a modular arithmetic operator (generally a %) that works in
MDX?

c) Is there a certain string format that I can use in the calculated
member's Format String property that will give me the result I want?

d) Does anyone have any suggestions besides those that I have listed
above?


Even if part c) of my question does have a suitable answer, the client
application that we use to display the cube doesn't utilize the Format
String property for any measure, be it calculated or not, but my morbid
curiosity has the best of me so I would still like to know.

Thanks for any suggestions/feedback/input,

John E.

Bump... Anyone have any ideas or suggestions relating to this?


John wrote: