Search code examples
ssasmdxolap

Count children members in MDX

How I can count number of children, for current member? I would like to print number of days for given month in current year, something like:

    WITH
MEMBER [Measures].[Label] AS [Dim_Gregorian Calender].[MonthName].CURRENTMEMBER.MEMBER_CAPTION

MEMBER [Measures].[Count] AS 
count(STRTOSET("[Dim_Gregorian Calender].[YMD].[Year].&[" + CStr(Format(Now(), "yyyy")) + "].&[2].children"))

SELECT 
{
    [Measures].[Label]
    ,[Measures].[Count]
} ON 0
,topcount([Dim_Gregorian Calender].[MonthName].[MonthName], 2) ON 1
FROM [Ret]

Now it shows me incorrect values in count (I need to put something insted &[2] to calculate it for every row).

I need to show only two rows.

Solution

  • I'd recommend to add a fact table based on your Date dimension to count rows (days). Nonetheless, you may use the MDX solution as well:

    Count(
  existing [Dim_Gregorian Calender].[YMD].[Day].Members
)