First, I'm brand new to OLAP. I've only been working with it less than 1
week.

I have a table which is a call center log of all calls made. It contains two
datetime fields, one for starttime and one for stoptime.

I want to create a measure for use in analysis services that calculates the
length of the call.

It seems DateDiff is available under Calculated Members but I can't seem to
figure out the syntax (if I am, in fact, doing this the write way.).

What I've done is

1) Opened the Cube Editor for my cube
2) Created a New Calculated Member
3) Left the parent dimension as "Measures"
4) Put the following VBA function into the value expression:
DateDiff( "ss", dbo.CallLog.StartTime, dbo.CallLog.StopTime )/60.0


When I click the Chekc button I get the following error:

Unable to update the calculated member. Formula error - cannot find
dimension member (dbo.CallLog.StartTime") in a name-binding function

Can anyone tell me what to do to get a cube that lets me aggregate call
times in addition to counts (which I have been able to get)?

Thanks

As far as I am aware DateDiff is not a valid MDX statement. But you can use
VBA expressions so you would want to try something like this.

This calculates the hours between two dimensions:

MEMBER [Measures].[hours] AS
'VBA!DateDiff ("w", CDate ([Time].[Day1].Properties ("DateStamp")),
CDate ([Time].[Day2].Properties ("DateStamp")))'

Have fun.





"Sterling Ledet" <sjledet (AT) ledet (DOT) com> wrote

Thanks a bunch for your answer. I'm not sure how to even use command line
code MDX code yet.

1) If I put a VBA expression in the value expression using the GUI, does it
still need to be proceeded by VBA! ?

2) If I'm using DateTime columns, would I still need to use CDate ?

Thanks again.


"AshVsAOD" <.> wrote