StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POReporting Services Aggregate not working for parent-child relationships
text
Body
copied!<p>I have a parent-child attribute relationship in an Analysis services dimension, say <code>[Dim].[Child]</code> is a child of <code>[Dim].[Parent]</code> and I have a query, that returns the set </p> <p><code>UNION( [Dim].[Parent].Members * [Dim].[Child].Members, [Dim].[Parent].[ALL] * [Dim].[Child].Members )</code> </p> <p>on axis 1 and some output on axis 0. The query works fine when run in management studio.</p> <p>Now I'm trying to make a chart in Reporting Services with <code>[Dim].[Child]</code> on the x axis and <code>Aggregate(some_output)</code> on the Y axis. I expect to see child members on the x axis with the output aggregated for all parents (which is just one in this case), i.e. the data taken from the line <code>{ [Dim].[Parent].[ALL], [Dim].[Child].&[TheChildID] }</code>. As stated above, the query does indeed return this line with the correct data.</p> <p>However, the <code>Aggregate</code> function in this case returns NULL. This behaviour is always present when using parent-child attributes and putting the child on the X axis. The same also happends when putting the child in a tablix row and adding an aggregated output in a column. It seems like the Reporting Server is aware that the aggregation will be happening for only one member and refuses to do it. </p> <p>If I make another identical query, just put in attributes which have a different relationship, e.g. <code>[Time].[Month]</code> and <code>[Time].[DayOfMonth]</code>, aggregation works fine, since this time the same day can belong to many months and data needs to be aggregated.</p> <p>Now I have no idea why is this happening - does the Reporting Server acctually query the OLAP data source to figure out the relationship or what ? Or is there some magic in the query that returns the ALL rows, which I'm not aware of ? The only correlation for cases which work and which don't seems to be the relationship. </p> <p><strong>EDIT:</strong> some observations after playing around more: the report works if I remove the line <code>[Parent].Members * [Child].Members</code> from the query, forcing it to retrieve only <code>[Parent].ALL * [Child].&[ID]</code> rows. The chart now has data aggregated, but obviously, I don't have individual members anymore.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload