how to get latest date in mdx?

Question

hi

if i pass my mdx like this i am get what i am expecting..

With
Set [TimeLimit] as 
 '{([CurrDate].[All CurrDate].[2010].[Q3].[Jul].[2010-07-21])}'
select 
 NON EMPTY {[Measures].[Balance], [Measures].[Peso Equiv]} ON COLUMNS,
 NON EMPTY {
  [PARTICULARS].[All Particulars], 
  [PARTICULARS].[All Particulars].[RESIDENTS], 
  [PARTICULARS].[All Particulars].[NON-RESIDENTS]
 } ON ROWS
from [depositlib_22]
where ([TimeLimit])"

Question?:- i dont want hardcode this part "[2010].[Q3].[Jul].[2010-07-21]",i need to get date from my cube(cube always geting latest date from database table).

let me know any one how we can possible to get?

pls,help us on this i am trying past 2 days...still unable to fondout solution.

Thanking u in advace...

Answer 1

You have two ways to deal with date issues like this in MDX. One is to use a VBA date function to build a string using the date you want. If you make this string match the member name in your dimension you can then convert it into a set. See http://stackoverflow.com/questions/3512028/sql-analysis-services-current-date

The second way, which I think you need to look at, is to use MDX functions to find out which is the last (i.e. most recent) member in your [CurrDate] dimension. You can then use that member in your query. This will work fine as long as your dimension contains only dates in the past. I once implemented this idea and then found a future date of 2015 crept in the cube by accident, and then my MDX didn't show me todays date (which was usually the most recent member).

The set you need might be:
{[CurrDate].[Date].members.Item([CurrDate].[Date].members.count-1)}
Or the equally un-elegant:
{[CurrDate].lastChild.lastChild.lastChild.lastChild.lastChild}