Home      Functions     Common Solutions      Contact     

MDX Functions:
MDX Functions

Common Solutions

Common Solutions
Resource Links

MDX Books
MDX Related Software
MDX Related Blogs
Other Links

Contact Us

Return yesterday's date in a member format.

You can use the VBA NOW() function within MDX to return a date, month, or year from Analysis Services.

The query below returns a string for yesterday's date.

MEMBER [Measures].[Yesterday] AS
      ""+ FORMAT(NOW()-1,"MM/dd/yyyy") +""
SELECT {[Measures].[Yesterday]} ON 0
FROM [Adventure Works]

The following query creates a set that contains a date member that corresponds to a date 5 years ago for yesterday's date.

NOTE: Five years has been subtracted from yesterday's date so the returned member would correspond to a date member that actually exists in the Adventure Works Date dimension.  That way, the query could be executed against the Adventure Works cube and a value would be returned.

WITH SET [Yesterday] AS
      STRTOSET("[Date].[Fiscal].[Date].["+ FORMAT(DATEADD("YYYY",-5,NOW()-1),"MMMM dd, yyyy") +"]")
SELECT {[Measures].[Sales Amount]} ON 0,
{[Yesterday]} ON 1
FROM [Adventure Works]


Copyright 2008 by MDXpert.com.
Terms of Use                              Privacy Statement
Site Powered By - WinHost.Com