Return Bottomcount of hierarchy level in MDX

Question

I am using SSRS to create a report where a hierarchy is displayed on the x-axis. I would like to make the level dynamic based on the lowest level of the hierarchy the user selects through a performancepoint filter. For example, if the user selects 2015 plus December 2004, the report should be at the month granularity but showing December 2014 - December 2015 inclusive.

Using the AdventureWorks cube (2008R2), I can query the Ship Date Fiscal hierarchy as follows:

WITH 
    MEMBER [SDName] AS [Ship Date].[Fiscal].CURRENTMEMBER.NAME
    MEMBER [SDLevel] AS [Ship Date].[Fiscal].CURRENTMEMBER.LEVEL_NUMBER

SELECT
    {[SDName], [SDLevel]} ON COLUMNS

    ,STRTOSET(" {[Ship Date].[Fiscal].[Fiscal Year].&[2011],
                [Ship Date].[Fiscal].[Fiscal Quarter].&[2006]&[3],
                [Ship Date].[Fiscal].[Fiscal Quarter].&[2008]&[3],
                [Ship Date].[Fiscal].[Month].&[2007]&[5]}", CONSTRAINED) ON ROWS

FROM    [Adventure Works]

Where the STRTOSET function would contain the output of the user multi-selection parameter. This returns:

As you can see, there has been 3 different levels of the hierarchy selected (year, quarter and month). I would like to return the month level, i.e. 4.

I have tried using Bottomcount as follows:

WITH 
    MEMBER [SDName] AS [Ship Date].[Fiscal].CURRENTMEMBER.NAME
    MEMBER [SDLevel] AS [Ship Date].[Fiscal].CURRENTMEMBER.LEVEL_NUMBER

SELECT
    BottomCount
    (
        {[SDName], [SDLevel]},
        1,
        [SDLevel]
    ) ON COLUMNS

    ,STRTOSET(" {[Ship Date].[Fiscal].[Fiscal Year].&[2011],
                [Ship Date].[Fiscal].[Fiscal Quarter].&[2006]&[3],
                [Ship Date].[Fiscal].[Fiscal Quarter].&[2008]&[3],
                [Ship Date].[Fiscal].[Month].&[2007]&[5]}", CONSTRAINED) ON ROWS

FROM    [Adventure Works]

but this returns exactly the same results as shown in the image above.

I wondered if this was because the WHERE clause needed to filter the data first (thinking like SQL), so I tried:

WITH 
    MEMBER [SDName] AS [Ship Date].[Fiscal].CURRENTMEMBER.NAME
    MEMBER [SDLevel] AS [Ship Date].[Fiscal].CURRENTMEMBER.LEVEL_NUMBER

SELECT
    BottomCount
    (
        {[SDName], [SDLevel]},
        1,
        [SDLevel]
    ) ON COLUMNS

    ,[Ship Date].[Fiscal].ALLMEMBERS ON ROWS

FROM    [Adventure Works]
WHERE   (STRTOSET(" {[Ship Date].[Fiscal].[Fiscal Year].&[2011],
                [Ship Date].[Fiscal].[Fiscal Quarter].&[2006]&[3],
                [Ship Date].[Fiscal].[Fiscal Quarter].&[2008]&[3],
                [Ship Date].[Fiscal].[Month].&[2007]&[5]}", CONSTRAINED))

but I get the error:

which I don't understand.

I also tried using a subquery:

WITH 
    MEMBER [SDName] AS [Ship Date].[Fiscal].CURRENTMEMBER.NAME
    MEMBER [SDLevel] AS [Ship Date].[Fiscal].CURRENTMEMBER.LEVEL_NUMBER

SELECT
    BottomCount
    (
        {[SDName], [SDLevel]},
        1,
        [SDLevel]
    ) ON COLUMNS

    ,[Ship Date].[Fiscal].ALLMEMBERS ON ROWS

FROM    (SELECT STRTOSET("  {[Ship Date].[Fiscal].[Fiscal Year].&[2011],
                [Ship Date].[Fiscal].[Fiscal Quarter].&[2006]&[3],
                [Ship Date].[Fiscal].[Fiscal Quarter].&[2008]&[3],
                [Ship Date].[Fiscal].[Month].&[2007]&[5]}", CONSTRAINED) ON COLUMNS
    FROM    [Adventure Works])

but this gives me all the members at the lowest level of the hierarchy (albeit is has filtered it using the STRTOSET input):

Can anyone help please? My MDX is not too good and I'm struggling to get the right output.

NOTE: I realised when I started exploring this problem, I could parse the parameter output to look for certain levels of the hierarchy, i.e.

@DateSelection LIKE "*[Month]*"

but I'm hoping to use this exercise to improve my MDX skills!

Quarcheek Quarcheek · Accepted Answer · 2015-11-20T09:45:11

Figured it out: while viewing this website I noticed that the Topcount function was being applied to the rows axis, not columns as I had above. By changing the structure, so that Topcount filters the rows, it returns the correct result:

WITH 
    MEMBER [SDName] AS [Ship Date].[Fiscal].CURRENTMEMBER.NAME
    MEMBER [SDLevel] AS [Ship Date].[Fiscal].CURRENTMEMBER.LEVEL_NUMBER

SELECT
    {[SDName], [SDLevel]} ON COLUMNS

    ,Topcount(
                STRTOSET("  {[Ship Date].[Fiscal].[Fiscal Year].&[2011],
                            [Ship Date].[Fiscal].[Fiscal Quarter].&[2006]&[3],
                            [Ship Date].[Fiscal].[Fiscal Quarter].&[2008]&[3],
                            [Ship Date].[Fiscal].[Month].&[2007]&[5]}", CONSTRAINED),
                1,
                [SDLevel]) ON ROWS

FROM    [Adventure Works]

Note that I had to use Topcount because the order by is descending (I wanted the lowest level of the hierarchy, i.e. the largest level number). The results are:

Hope that might help someone else with the same problem.

Return Bottomcount of hierarchy level in MDX

1 Answers