Getting getdate(), datediff in SSAS (MDX) query

Question

We have a MDX query which looks like this:

WITH Member [Measures].[Z] AS (DATEADD("H",-1,[X].[Y].Member_Caption)) 
SELECT { [X].[Y].Children } ON Rows,
       { [Measures].[Z] } ON Columns
FROM ABC

It basically returns Datetime (LastRefresh).

My task is to create an MDX query that returns the following columns:

LastRefresh, Getdate(), datediff(Mins, LastRefresh, Getdate())

Its equivalent SQL query is:

select LastRefresh, getdate() as CurrentDate, datediff(Mins, LastRefresh, getdate()) as DateDifference from XYZ

How can I do the same in MDX on the Cube?

Note:

We can't create any new measures, as we are just using the Cube to get data, and don't have any kind of access to create measures etc.

what is your question? I cannot see a question mark anywhere. — whytheq, Jul 05 '17 at 22:03
LastRefresh is not available via mdx against the cube (unless you've added a Last Refresh member somewhere?!) — whytheq, Jul 11 '17 at 10:19
We have lastRefreshMember,I want getdate() and differnce on the same — Chetan Kulkarni, Jul 11 '17 at 11:21
does this post help? https://stackoverflow.com/questions/22629814/mdx-dateadd-function-over-a-set-of-tuples or this one: https://stackoverflow.com/questions/35479968/mdx-where-date-is-less-than-now — whytheq, Jul 11 '17 at 22:25

score 0 · Answer 1 · answered Aug 02 '17 at 15:51

GETDATE() is a SQL function, but you are writing MDX, so it is not allowed.

If you are using Microsoft SSAS then VBA is allowed within MDX, and you can use Now() to return the current date and time.

Use the VBA format of DateDiff (not the SQL version!) and you can do the calculation that way. You probably need CDate() to turn the string value of a member name into a date object for VBA to use. Such as...

DateDiff("d", CDate([X].[Y].MemberName), Now())

Getting getdate(), datediff in SSAS (MDX) query

Note:

1 Answers1