SSAS creating hierarchy for measure

Question

I have a requirement to display the measures of a cube in hierarchy (browsing in excel or SSRS). Say I have four measures in fact table revenue A, revenue B, expense A. I need to show it as

'+ Profit (calculated measure)

'--+Revenue (calculated measure)

'----+Revenue A (Fact measure)

'----+Revenue B (Fact measure)

'--+expense (calculated measure)

'----+Expense A (Fact measure)

'----+Expense B (Fact measure)

I created the calculated measure however I am unable to solve the hierarchy issue for the measures. I am not able to use Display folders or degenerate dimensions to achieve the result. Can anyone help?

SouravA SouravA · Accepted Answer · 2016-01-13T12:30:16

You don't need to create a hierarchy if the purpose is just to be able to browse it. Create a named set in your cube(if changing cube structure is an option), or a query scoped named set(if it should be temporarily created) and put all the requisite measures into it.

WITH SET MySetOfMeasures AS
{
    Measures.Profit,

    Measures.Revenue,

    Measures.[Revenue A],

    Measures.[Revenue B],

    Measures.expense,

    Measures.[Expense A],

    Measures.[Expense B]
}

Then you can invoke this set in your MDX.

SELECT MySetOfMeasures ON 0 FROM [YourCube]

SSAS creating hierarchy for measure

1 Answers