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.

WITH
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