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 format

Hi,

I have a date format problem with the AS 2005.

I have a Data Mart dimension with a field of the type smalldatetime. I have created a AS2005 dimension with the hierachy "Fulldate" which is based on this field. Due to the language settings of the os and of the cube I would expect that the hierachy would display the date like dd.mm.yyyy

Unluckily it appears as mm/dd/yyyy

Under the DataItems there is a property format which I tried to use to get the correct displayed datetime ...but whatever I use i always display US datetime format.

What can I do to get the dateformat like dd.mm.yyyy?

Thanks,

StSt

You can create a named calculation in the table in the DSV that formats the date however you desire using the SQL syntax of the provider. Then use this column as the name column for the date attribute. If you are using a server based time dimension because you don't have a time table, you won't be able to do this. In this case you may want to consider generating a time table so that you can have better control over it's contents. This can be done in the Dimension Wizard by choosing the option to build a dimension without a source and not using a template. Then when you are done you will need to run "Generate Relational Schema..." from the database and the system will actually create and populate a time table in your data source.|||

Unluckily this doesn't help me. The data source is a SQL Server 2005 Data Mart which has all language settings for german date format which means

dd.mm.yyyy

Even in the DSV it display the date correctly but as soon as I use it in the dimension it will only be display like

mm/dd/yyyy

Independent from the DimensionAttribute Type or the DataItem Format.

What else can I do?

Regards,

StSt

Matt Carroll wrote:

You can create a named calculation in the table in the DSV that formats the date however you desire using the SQL syntax of the provider. Then use this column as the name column for the date attribute. If you are using a server based time dimension because you don't have a time table, you won't be able to do this. In this case you may want to consider generating a time table so that you can have better control over it's contents. This can be done in the Dimension Wizard by choosing the option to build a dimension without a source and not using a template. Then when you are done you will need to run "Generate Relational Schema..." from the database and the system will actually create and populate a time table in your data source.