CODEWITHSUNDEEP
sql-server-2005mdx
Anders Rune Jensen
Anders Rune Jensen

Reputation: 3796

multiple dimension restrictions in a MDX where clause

I have the following problem. If I query values with a keyfigure which is a function I can't specify multiple values of the same dimension restriction, 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 doesn't:

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 guess the solution is something like adding the where clause to the header but I really like this solution because it's so simple.

The Calucated function is:

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 from this and modified for two hierarchies in the time dimension: http://www.sqlserveranalysisservices.com/OLAPPapers/InventoryManagement%20in%20AS2005v2.htm

This is on SQL server 2005 Enterprise edition.

Upvotes: 0

Views: 8846

Answers (2)

Darren Gosbell
Darren Gosbell

Reputation: 1940

The problem is in your calculated measure. You are using .CurrentMember and ClosingPeriod without a specific member reference which implies a call to .CurrentMember. When you have set in the WHERE clause there is no "Current" member - there are multiple current members. Re-writting 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 on the All members of the two dimensions, this should be fast that the out IIF and will also avoid one reference to .CurrentMember. I then replaced the ClosingPeriod() call with Tail(Existing [Time].[Year - Week - Date].[Date],1).item(0).item(0) what this does is to get the set of date members that exist in the current context, the Tail() call then gets the last on of these as a single member set and the .Item(0).Item(0) calls get the first member from the first tuple of that set.

Obviously, not having access to your cube, I can't test any of this. The issue you reported in your comment could relate to either an incorrect reference to the All members (I may have a different naming format to the one in your cube) or it may be related to the IIF() statement. I'm not sure that the check for 0 is being evaluated in the correct context

You could try testing without the IIF()

CREATE MEMBER CURRENTCUBE.[MEASURES].Ultimo AS NULL;

SCOPE ([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;

Upvotes: 0

Anders Rune Jensen
Anders Rune Jensen

Reputation: 3796

Ok, this works:

WITH MEMBER [Time].[Month].a AGGREGATE 
({[Time].[Month].[Month].&[2008-03-01T00:00:00], 
  [Time].[Month].[Month].&[2008-04-01T00:00:00]})
SELECT {[Measures].[CalculatedFunction]} on columns FROM TDC where a

Upvotes: 1

Related Questions