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

Date Dimension in SSAS2005

Dear all,

In AS2000 when we use a date field of the dimesion table as a date dimension and defines the level (ex:Year,Month and dat) AS2000 formats the date field as given format.

In SSAS2005 i used the same methord but SSAS2005 does goes in that way.It simply gives me all the date as in database (10/10/2005)

How can i do it in level and hirarchy (year,month and day)

Hope u understand the Question

from

sufian

Dear all,

AS i studied in and tested i come to know that we can use two type of time dimesnion in SSAS2005 beased on the user requirement.

(1) Time Dimension (2) Server Time Dimension

Time Dimesion : Is similar to other dimension and supplies the attributes of the dimension.Each column with define an attribute for a specific time period.

Server Time Period: This is the one which defines the attribute on Year,Month Day and much more.

Thanx

Regards

Mohd Sufian