CODEWITHSUNDEEP
reporting-servicesssasmdxssrs-2012
Alex_404
Alex_404

Reputation: 409

Working with levels of hierarchy which might be completely empty

My hierarchy is at most 4 levels deep. Level 4 does not always exist but my SSRS report is using it for some logic (null is handled). This part of my MDX query returns all levels of the hierarchy with some data:

{ DESCENDANTS([Hierarchy].[Hierarchy], [Hierarchy].[Hierarchy].[Level4], LEAVES) } ON ROWS

The report works fine when my result has at least one row for level 4. However, if the user uses some parameters in the report to specify a set of data which does not have level 4 the following error is shown:

The dataset 'Data' contains a definition for the Field 'Level4'.
This field is missing from the returned result set from the data source.

How do I handle this? Basically my report stops working if the users managed to filter out a small subset of data without level 4 nodes.

Edit: I've been doing some testing and for some reason the error is no longer shown. However I still have the same problem as fields for Level4 values are just blank in the report and if I put some IsNothing() logic there I get #Error instead of True/False, so report does not work.

Upvotes: 0

Views: 338

Answers (1)

whytheq
whytheq

Reputation: 35557

Instead of this:

{ DESCENDANTS([Hierarchy].[Hierarchy], [Hierarchy].[Hierarchy].[Level4], LEAVES) } ON ROWS

Do you need to specify Level4? I assume the above returns all the members of the lowest level (4) of your hierarchy - maybe something like the following 'pattern':

[Dimension].[Hierarchy].LEVELS([Dimension].[Hierarchy].Levels.Count-1)

But if you want to stick with the function descendents then maybe this 'pattern':

DESCENDANTS([Dimension].[Hierarchy].[All], ([Dimension].[Hierarchy].Levels.Count - 1), SELF)

But even something like the following might do what you require:

DESCENDANTS([Dimension].[Hierarchy].[All], , LEAVES)

Upvotes: 1

Related Questions