Multiple size constraints in MDX where clause
I have the following problem. If I am asking for values with a key that is a function, I cannot specify multiple values of the same size constraint, but if it is not a function, it works.
So this works:
SELECT {[Measures].[Netto]} on columns FROM TDC where
({NonEmpty([Time].[Month].[Month].&[2008-03-01T00:00:00]),
NonEmpty([Time].[Month].[Month].&[2008-04-01T00:00:00])})
But this is not the case:
SELECT {[Measures].[CalculatedFunction]} on columns FROM TDC where
({NonEmpty([Time].[Month].[Month].&[2008-03-01T00:00:00]),
NonEmpty([Time].[Month].[Month].&[2008-04-01T00:00:00])})
And this also works:
SELECT {[Measures].[CalculatedFunction]} on columns FROM TDC where
({NonEmpty([Time].[Month].[Month].&[2008-03-01T00:00:00])})
I think the solution is something like adding a where clause to the header, but I really like this solution because it is so simple.
Calucated function:
CREATE MEMBER CURRENTCUBE.[MEASURES].Ultimo
AS (iif ((not [Time].[Year - Month - Date].currentmember is [Time].[Year - Month - Date].defaultmember),
IIF(NOT ([Measures].[LagerStk] = 0),
Sum([Time].[Year - Month - Date].[Date].members(0):
ClosingPeriod([Time].[Year - Month - Date].[Date]),
[Measures].[LagerStk]), NULL)
,
IIF(NOT ([Measures].[LagerStk] = 0),
Sum([Time].[Year - Week - Date].[Date].members(0):
ClosingPeriod([Time].[Year - Week - Date].[Date]),
[Measures].[LagerStk]), NULL))),
VISIBLE = 1;
The code is inspired by this and modified for two hierarchies in time dimension: http://www.sqlserveranalysisservices.com/OLAPPapers/InventoryManagement%20in%20AS2005v2.htm
This applies to the Enterprise Server 2005 edition of SQL Server.
source to share
The problem lies with your calculated measure. You are using .CurrentMember and ClosingPeriod without the specific Member reference that implies calling .CurrentMember. When you set in a WHERE clause, there is no "current" member — there are multiple current members. Re-writing your MDX to something like the following should allow it to work with multiple members in the WHERE clause.
CREATE
MEMBER CURRENTCUBE.[MEASURES].Ultimo AS NULL;
SCOPE ([MEASURES].Ultimo);
SCOPE ([Time].[Year - Month - Date].[All]);
this = IIF
(
(NOT
[Measures].[LagerStk] = 0)
,Sum
(
NULL:Tail(Existing [Time].[Year - Week - Date].[Date],1).item(0).item(0)
,[Measures].[LagerStk]
)
,NULL
);
END SCOPE;
SCOPE ([Time].[Year - Week - Date].[All]);
this = IIF
(
(NOT
[Measures].[LagerStk] = 0)
,Sum
(
NULL:Tail(Existing [Time].[Year - Month - Date].[Date],1).Item(0).Item(0)
,[Measures].[LagerStk]
)
,NULL
)
);
END SCOPE;
END SCOPE;
I am using SCOPE for all members of two dimensions, it should be fast that outside IIF and also avoid referencing .CurrentMember. Then I replaced the call to ClosingPeriod () with Tail (existing [time]. [Year - week - date]. [Date], 1) .item (0) .item (0) , which is what it means to get a set of date items that exist in the current context, the call to Tail () then gets the last one as one set of items and .Item (0) .Item (0) causes the first member from the first tuple of that set to get.
Obviously, without having access to your cube, I cannot verify this. The problem you reported in your comment may be related to either incorrect reference to all members (I may have a different naming format for the one in your cube), or it may be related to the IIF () operator. I'm not sure if checking for 0 is evaluated in the correct context
You can try testing without IIF ()
CREATE MEMBER CURRENTCUBE. [MEASURES] .Ultimo AS NULL;
SCOPE OF APPLICATION ([MEASURES] .Ultimo); SCOPE ([Time]. [Year - month - date]. [All]); this = Sum (NULL: Tail (Existing [Time]. [Year - Week - Date]. [Date], 1) .item (0) .item (0), [Measures]. [LagerStk]); END SCOPE;
SCOPE ([Time].[Year - Week - Date].[All]);
this = Sum
(
NULL:Tail(Existing [Time].[Year - Month - Date].[Date],1).Item(0).Item(0)
,[Measures].[LagerStk]
);
END SCOPE;
END SCOPE;
source to share