Tag Archives: ssrs parent child charts

Charts for Parent Child MDX Datasets

SSRS handles parent child datasets dynamically through configuration of the tablix control, setting the grouping and advanced options for the table.  A good article on how to achieve this is outlined here at Davy Knuysen Parent Child. To pass the right data to the table, you will have something like the following in the rows component of your MDX query.

DESCENDANTS(StrToSet(@paramParentChild)

Whilst this offers intuitive drilldown of the parent child hierarchy in a grid or table (without configuring the regular visibility/grouping toggle options), pinning a chart to the same dataset may produce misleading visuals such as repeating totals for upper levels of the hierarchy.

To avoid confusing users, try creating a second dataset for your chart leaving the first dataset for what it is required for (i.e. feeding the table a hierarchy).  The second MDX datasets is pretty much a copy of the first, but you change the DESCENDANTS function to exclude the upper umbrella levels of the hierarchy that could cause these repeating totals in the chart categories.

DESCENDANTS(StrToSet(@paramParentChild),[Dimension].[ParentChildHierarchy],LEAVES)

This will ensure only leaf levels are visualised in the chart, not the umbrella levels, and hopefully avoid any confusion that comes from misinterpreting leaf and parent levels.

Another option is to differentiate the parent levels by setting an expression on the Color property of the chart series e.g.

=Iif(Fields!Level.Value<5,"Red","Blue")

The level field is derived when you add the following code to the ROWS component of your MDX query …

DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME, PARENT_UNIQUE_NAME, LEVEL_NUMBER ON ROWS