Ragged Hierarchies in RS2005

Hi...

I am connecting to Analysis Sercices 2000 from Reporting Services 2005.

When building a dataset, just in the data pane graphical designer, I have dragged 5 levels of the dimension 'Organisation' into the data pane.

The dimension has members at level 4 that have no children. But the dimenension also has members at level 5 WITH children, meaning that the dimension is ragged. If I only select levels 1-4 on the data pane, I see all level 4 children. However, if I also drag level 5 to the data pane, I loose any level 4 members that do not have children.

This means I loose vallid members that have data. Does anyone know of any way around this?

Thanks

Jeremy

Does anyone have any experience on getting this to work?

I have managed to display a ragged hierarchy by hacking the MDX, with the use of the DrillDownMember function.

Unfortunatlely, altough the ragged hierarchy does now display correctly in the data pane, it does not display in a table or matrix. So I have the situation where by my result set in the data pane does not match my result set in the actual report.

If anyone can offer any assistance it would be appreciated,

Thank you

Jeremy

|||

I have now managed to get this to work.

As I was using an AS2000 cube with the Adomd client, which according to Microsoft is not supported. If you want to use an AS2000 cube, then you have to use the OLE DB data provider.

Unfortunately I still had to use the DillDownMember function, this seems to be the only way I can get ragged hierachies to work correctly.

|||

Sorry I had not seen your post before.

Considering that the returned cellset gets flattened into a result set, if you put your set of members on the ROWS axis of the MDX query, you will actually end up with a separate column per level in you result set.

So if you structure your query like this

SELECT
{[Measures.[x]]} ON COLUMNS,
{Descendants([YourDimension].[All], [YourDimension].[Level 5], SELF_BEFORE_AFTER)} ON ROWS
FROM [YourCube]

The returned dataset should have these columns

Level_1, Level_2, Level_3, Level_4, Level_5, x

This should cater for your ragged hierarchies requirement.

sql