I have a parent-child attribute relationship in an Analysis services dimension, say [Dim].[Child] is a child of [Dim].[Parent] and I have a query, that returns the set
UNION(
[Dim].[Parent].Members * [Dim].[Child].Members,
[Dim].[Parent].[ALL] * [Dim].[Child].Members
)
on axis 1 and some output on axis 0. The query works fine when run in management studio.
Now I'm trying to make a chart in Reporting Services with [Dim].[Child] on the x axis and Aggregate(some_output) 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 { [Dim].[Parent].[ALL], [Dim].[Child].&[TheChildID] } . As stated above, the query does indeed return this line with the correct data.
However, the Aggregate 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.
If I make another identical query, just put in attributes which have a different relationship, e.g. [Time].[Month] and [Time].[DayOfMonth], aggregation works fine, since this time the same day can belong to many months and data needs to be aggregated.
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.
EDIT: some observations after playing around more: the report works if I remove the line [Parent].Members * [Child].Members from the query, forcing it to retrieve only [Parent].ALL * [Child].&[ID] rows. The chart now has data aggregated, but obviously, I don't have individual members anymore.