Reporting Services聚合不适用于父子关系

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

我在Analysis服务维度中有一个父子属性关系,比如[Dim]。[Child]是[Dim]的子项。[Parent]我有一个查询,它返回集合

UNION( [Dim].[Parent].Members * [Dim].[Child].Members, [Dim].[Parent].[ALL] * [Dim].[Child].Members )

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.

在轴1上,在轴0上输出一些。在管理工作室中运行时,查询工作正常。

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.

现在,我正在尝试在Reporting Services中使用[Dim]。[Child]在x轴上创建一个图表,并在Y轴上创建Aggregate(some_output)。我希望在x轴上看到子成员的输出聚合为所有父项(在这种情况下只有一个),即从行{[Dim]。[Parent]。[ALL],[Dim]获取的数据) 。[孩子]。&[TheChildID]}。如上所述,查询确实使用正确的数据返回此行。

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.

但是,在这种情况下,Aggregate函数返回NULL。使用父子属性并将子项放在X轴上时,始终存在此行为。将子项放入Tablix行并在列中添加聚合输出时也会发生同样的情况。似乎报表服务器知道聚合将仅发生在一个成员上,并拒绝执行此操作。

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.

如果我创建另一个相同的查询,只需输入具有不同关系的属性,例如[时间]。[月]和[时间]。[DayOfMonth],聚合工作正常,因为这一天同一天可以属于很多个月并且数据需要聚合。

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.

现在我不知道为什么会发生这种情况 - 报表服务器是否正在查询OLAP数据源以找出关系或什么?或者在查询中是否存在一些返回ALL行的魔法,我不知道这些行?对于工作和不似乎是关系的案件的唯一相关性。

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.

编辑:在玩了更多之后的一些观察:如果我删除行[Parent],则报告有效.Members * [Child]。来自查询的成员,强制它仅检索[Parent] .ALL * [Child]。&[ID行。该图表现在汇总了数据,但显然,我不再拥有个人成员了。

1 个解决方案

#1

Just a couple of observations that I hope will help.

我希望只有几点意见可以提供帮助。

The aggregate function in reporting services requires a "scope" parameter...for example Aggregate(Fields!Child.value, "ParentGroup")

报告服务中的聚合函数需要“范围”参数...例如Aggregate(Fields!Child.value,“ParentGroup”)

Your data provider must support this function. If it doesn't then "Nothing" is returned. See link below...

您的数据提供者必须支持此功能。如果没有,则返回“Nothing”。见下面的链接......

For more help/info check Aggregate function documentation.

有关更多帮助/信息,请查看聚合函数文档。

#1