Search code examples
ssasmdx

Applying MEMBER_CAPTION to the return value of ClosingPeriod

The following does not error, or bug, just returns a blank result set.

I realize there are other functions I can use but for the sake of experimentation I'd like to stick with ClosingPeriod

Why is it returning a blank?
How do I amend it so it returns yesterday? 

WITH MEMBER [Date].[Date - Calendar Month].[member_caption] AS
    ClosingPeriod(
        [Date].[Date - Calendar Month].[Calendar Day]
        ).MEMBER_CAPTION
SELECT 
    NON EMPTY 
        [Date].[Date - Calendar Month].[member_caption]
    ON COLUMNS
FROM [OurCube]

EDIT

The MSDN documentation for the ClosingPeriod function is here and states:

• If a level expression is specified, the ClosingPeriod function uses the dimension that contains the specified level and returns the last sibling among the descendants of the default member at the specified level.

• If both a level expression and a member expression are specified, the ClosingPeriod function returns the last sibling among the descendants of specified member at the specified level.

Solution

  • I guess this is because the default member of the [Date].[Date - Calendar Month] is not in the [Calendar Day] level; so this equivalent to :

    ClosingPeriod( [Date].[Date - Calendar Month].[Calendar Day], null )

    obsviously null has no descendants and ClosingPeriod does not return anything. Perhaps you want something like:

    ClosingPeriod( 
  [Date].[Date - Calendar Month].[Calendar Day],
  [Date].[Date - Calendar Month].defaultMember 
)