Dynamic date filter in MDX using VBA

When I create a new cube I always add a few named sets for getting time periods automatically. These are nice to have if I have scheduled reports that should present data for today, yesterday, last week or maybe current month.

An example could be a named set that returns yesterdays date.

CREATE SET CURRENTCUBE.[Yesterdays date] AS 
StrToMember("[Date].[Date].&[" + Format(now()-1, "yyyy-MM-dd") + "T00:00:00]", CONSTRAINED); 

And the named set might be used like this

SELECT [Measures].[Internet Total Sales] ON 0,
 [Current Date] 
 * [Product].[Model Name].[Model Name] ON 1
FROM [Internet Sales]

You can of course also use this within a single query like this.

WITH SET [Current date] AS 
StrToMember("[Date].[Date].&[" + Format(now()-1, "yyyy-MM-dd") + "T00:00:00]")    -- minus 1 gives yesterdays date

SELECT [Measures].[Internet Total Sales] ON 0,
 [Current Date] 
 * [Product].[Model Name].[Model Name] ON 1
FROM [Internet Sales]

Please note that the format of the member must be correct. In my case the Date dimensions members look like this: [Date].[Date].&[2005-01-10T00:00:00]. If you try to run these samples against the sample database you will not get any result. Since the database does not have data for the current period. If you want to test a valid date you can check how many days you have to remove by running this SQL:

select DATEDIFF(day, getdate(), '2006/05/18')

And then you can change the code “now()-1” to “now()-[The number of days the query returned]”

Here are some other useful samples

//Sample date sets.
CREATE SET CURRENTCUBE.[Current date] AS 
StrToMember("[Date].[Date].&[" + Format(now(), "yyyy-MM-dd") + "T00:00:00]", CONSTRAINED);       
     
-- Must be used with [This Year]
CREATE SET CURRENTCUBE.[This month] AS 
StrToMember("[Date].[Month].&[" + Format(now()-1, "MM") + "]", CONSTRAINED);        

CREATE SET CURRENTCUBE.[This year] AS 
StrToMember("[Date].[Calendar Year].&[" + Format(now(), "yyyy") + "]", CONSTRAINED);      

-- Can you create a named set for current week? Last week?
Advertisements