Date function for calculated member?

Hello,

In AS2000 I was using a VBA function Date to retrieve the current date and use it as part as my calculated member formula.

In AS2005, you cannot use VBA function and I do not find a way to retrieve the current date (without the time or with the time set to 00:00:00)

I tried to create a Named query in the DS but I cannot seem to be able to use this in the calculated member.

Any Idea?

Thanks,

Philippe

You can use the "Now()" function. Here is an example that I use to create a member based on the current system day:

--Create a date string using the following format: "9-Jun-06"

CREATE HIDDEN [As of Date String] =

Format(Now(),"d-") +

Left(Format(Now(),"m"),3) + "-" +

Right(CStr(DatePart("yyyy",Now())),2);

CREATE MEMBER CURRENTCUBE.[Date].[Calendar].[As of Date]

AS

StrToMember("[Date].[Calendar].[" + [As of Date String] + "]");

HTH,

Steve

|||

Steve,

Very useful! However, I am not able to recreate this in my cube. I added the following code to my cube's calculations (I'm using SSAS 2005 btw):

CREATE HIDDEN [Current Date String] =

CStr(Format(Now(), "MM/")) +

CStr(Format(Now(), "dd/")) +

CStr(DatePart("yyyy",Now()));

CREATE MEMBER CURRENTCUBE.[Time].[Fiscal Time].[Current Week]

AS

StrToMember("[Date].[Fiscal Time].[Actual Date].[" + [Current Date String] + "].Parent");

So when I browse my cube, I see the "Current Week" calculated member under the [Time].[Fiscal Time] members. However, when I drop it in the filters section of the browser, nothing happens. Am I missing a step?

|||

Todd,

If you are testing this using the browser in BIDS, the filter set will not work with a calculated member. You can test by simply using the same MDX to create a "set" for testing purposes. Try adding the following and then using the set in your filter statement:

CREATE SET CURRENTCUBE.[Current Week Test Set]

AS

StrToSet("[Date].[Fiscal Time].[Actual Date].[" + [Current Date String] + "].Parent");

HTH,

Steve