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

发布于 2024-07-22 07:51:52 字数 1115 浏览 8 评论 0原文

我在分析服务维度中有父子属性关系，例如 [Dim].[Child] 是 [Dim].[Parent] 的子级，并且我有一个查询，返回集合

UNION( [暗淡].[父].成员 * [暗淡].[子].成员, [暗淡].[父].[全部] * [暗淡].[子].成员 )

位于轴 1 上，一些输出位于轴 0 上。该查询在 Management Studio 中运行时工作正常。

现在，我尝试在 Reporting Services 中制作一个图表，其中 x 轴为 [Dim].[Child] ，Y 轴为 Aggregate(some_output) 。我希望在 x 轴上看到子成员，并为所有父级（在本例中只是一个）聚合输出，即从行 { [Dim].[Parent].[ALL], [Dim].[Child].&[TheChildID] }。如上所述，查询确实返回了包含正确数据的这一行。

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

如果我进行另一个相同的查询，只需放入具有不同关系的属性，例如 [Time].[Month] 和 [Time].[DayOfMonth]，聚合就可以工作很好，因为此时同一天可能属于多个月份，并且需要聚合数据。

现在我不知道为什么会发生这种情况 - 报告服务器是否确实查询 OLAP 数据源以找出关系还是什么？或者查询中是否存在一些我不知道的返回所有行的魔法？有效和无效案例的唯一相关性似乎是关系。

编辑：尝试更多后的一些观察结果：如果我从查询中删除行 [Parent].Members * [Child].Members ，则报告会起作用，强制它检索仅 [Parent].ALL * [Child].&[ID] 行。图表现在已经汇总了数据，但显然，我不再有个人成员了。

原文

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.

分享到QQ

分享到微博