Using recursive hierarchy SSAS Cube in SSRS

Question

I am trying to create an SSRS report from an SSAS cube with the following format

So what I did is create a database table with recursive hierarchy which we can call here a location dimension (dimLocation) and it has the following layout

Then under SSAS multidimensional model, I have created a self-joined Dimension which generates the following hierarchy

• Americas
  • USA
• Europe
  • France
    • Paris
    • Marseille
  • Germany
• Africa

Next, I created the SSAS cube using my fact table and the self-joined dimension, deployed it and tried to use it in SSRS, but I was not able to create the layout above.

What I have tried so far, is to create a Tablix with a group on columns, set the group properties to group on [Parent_ID.UniqueName] and recursive parent on [Parent_ID.ParentUniqueName]. It almost worked fine except that the groups are being expended horizontally and not vertically as my expected layout.

I am starting this project from scratch, which means I am free to change the table structures etc… Is it possible to achieve such layout with my actual cube/dimensions? Or maybe I should redesign my dimension source table and create another type of hierarchy than a recursive one?

Answer 1

I don't know how your dataset is generated so I guessed a possible query result

Then created a dataset that generates the above results and added a Matrix with the following arrangement.

You should get the following matrix

Maybe this can help you to change your cube structure. I don't think recursive hierarchies can be useful in a cube, taking in account that cube calculations are based on a bunch of joins between dimensions.

Post your query and explain how quantity is related with the recursive hierarchy table for our understanding.