I want to add a calculated member to the Fiscal Time hierarchy of my
Time dimension which represents the current week based on the system
date. So something like this:

[Time].[Fiscal Time].[Current Week]
which would equal
[Time].[Fiscal Time].[Fiscal Week].[08/13/2006] (i.e. week ending
8/13/2006)

I added the following code to my cube's calculations:

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");

However, when I browse the cube and filter by this member, any measures
on the browser do not change. Am I on the right track?

A couple of checks may help identify the issue:


1) If you hard-code a date, does that work, like:

CREATE MEMBER CURRENTCUBE.[Time].[Fiscal Time].[Current Week] AS
StrToMember("[Date].[Fiscal Time].[Actual Date].["
+ "08/18/2006" + "].Parent");

2) Create a measure to display the string built, like:

CREATE MEMBER CURRENTCUBE.[Measures].[WeekString] AS
"[Date].[Fiscal Time].[Actual Date].["
+ [Current Date String] + "].Parent";


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***