群组总数i Reporting Services 2012的平均值(Average on group totals i Reporting Services 2012)

群组总数i Reporting Services 2012的平均值(Average on group totals i Reporting Services 2012)

我试图使用Tablix在SQL Server Reporting Services 2012中对组总数进行平均分析。

似乎是这个问题与之相关的问题......但是没有回应： SQL 2005 Reporting Services - 错误的平均值

我的tablix如下所示：

+-------+---------+-----------+-----------+ | | | Product A | Product B | +-------+---------+-----------+-----------+ | | Average | ??? | ??? | | | | | | | Week1 | | 550 | 175 | | | Day 1 | 250 | 100 | | | Day 2 | 200 | 50 | | | Day 3 | 100 | 25 | | | | | | | Week2 | | 600 | 240 | | | Day 1 | 300 | 200 | | | Day 2 | | 30 | | | Day 3 | | 10 | +-------+---------+-----------+-----------+

平均值应仅计算周总数。 即产品A的平均值应为（550 + 600）/ 2 = 575。

我的平均公式如下：

=Avg(Fields!WeekTotal.Value)

但是这会产生错误的结果 - 我的猜测是它需要几天的考虑？

这个问题越来越难，因为有时我没有Days销售，在这种情况下，WeekTotal将是一个预测

我的数据集来自SQL Server表，包含以下数据：

+---------+------+-----+-----------+----------+ | Product | Week | Day | WeekTotal | DayTotal | +---------+------+-----+-----------+----------+ | A | 1 | 1 | 550 | 250 | | A | 1 | 2 | 550 | 200 | | A | 1 | 3 | 550 | 100 | | B | 1 | 1 | 175 | 100 | | B | 1 | 2 | 175 | 50 | | B | 1 | 3 | 175 | 25 | | A | 2 | 1 | 600 | 300 | | A | 2 | 2 | 600 | NULL | | A | 2 | 3 | 600 | NULL | | … | … | … | … | … | +---------+------+-----+-----------+----------+

任何帮助表示赞赏！

I am trying to make an average on group totals in SQL Server Reporting Services 2012 with a tablix.

Seems to be the same problem that this question is struggeling with...however there are no responses: SQL 2005 Reporting Services - Wrong Average Values

My tablix looks like the following:

+-------+---------+-----------+-----------+ | | | Product A | Product B | +-------+---------+-----------+-----------+ | | Average | ??? | ??? | | | | | | | Week1 | | 550 | 175 | | | Day 1 | 250 | 100 | | | Day 2 | 200 | 50 | | | Day 3 | 100 | 25 | | | | | | | Week2 | | 600 | 240 | | | Day 1 | 300 | 200 | | | Day 2 | | 30 | | | Day 3 | | 10 | +-------+---------+-----------+-----------+

The average should only be calculated for the week-totals. I.e. for Product A the average should be (550+600)/2=575.

My formula for average looks like the following:

=Avg(Fields!WeekTotal.Value)

however this gives a wrong result - my guess is that it takes the days into account aswell?

The question gets harder, because sometimes i do not have the Days sale, in which case the WeekTotal will be a prediction

My dataset, which comes from a SQL Server table, contains the following data:

+---------+------+-----+-----------+----------+ | Product | Week | Day | WeekTotal | DayTotal | +---------+------+-----+-----------+----------+ | A | 1 | 1 | 550 | 250 | | A | 1 | 2 | 550 | 200 | | A | 1 | 3 | 550 | 100 | | B | 1 | 1 | 175 | 100 | | B | 1 | 2 | 175 | 50 | | B | 1 | 3 | 175 | 25 | | A | 2 | 1 | 600 | 300 | | A | 2 | 2 | 600 | NULL | | A | 2 | 3 | 600 | NULL | | … | … | … | … | … | +---------+------+-----+-----------+----------+

Any help is appreciated!

最满意答案

您可以简单地将所有细节值（在日级别）加总，然后除以唯一周数。 这样的东西......

=Sum(Fields!DayTotal.Value)/CountDistinct(Fields!Week.Value)

在您的示例中， CountDistinct(Fields!Week.Value)将返回2

这一切都假设你有一个简单的矩阵，因为你的报告设计是这样的......

表达式是上面的表达式来计算平均值。

请注意，在我的示例数据中，我有更多详细信息，因此我按天和周分组。 在你的情况下，你不需要按天分组（但如果你这样做，它仍然可以工作）

此外，您不需要周总计，因为您可以简单地将星期组总计的总天数相加。

根据OP评论更新：

由于您需要使用数据中的实际周总数（这并不总是我之前想象的总天数之和），我们将不得不编辑数据集并在那里完成一些工作。 下面是用于生成样本数据集的代码。

DECLARE @t TABLE (Product varchar(1), [Week] int, [Day] int, WeekTotal int, DayTotal[int]) INSERT INTO @t VALUES ('A', 1, 1, 550, 250), ('A', 1, 2, 550, 200), ('A', 1, 3, 550, 100), ('B', 1, 1, 175, 100), ('B', 1, 2, 175, 50), ('B', 1, 3, 175, 25), ('A', 2, 1, 600, 300), ('A', 2, 2, 600, NULL), ('A', 2, 3, 600, NULL) SELECT * , CAST(WeekTotal as float) / COUNT(*) OVER(PARTITION BY Product, [Week]) as WeekTotalAverage FROM @t

请注意，我现在返回一个额外的列WeekTotalAverage （我知道，名字很差！）。 这样做是为了给我们每周产品/周的平均值，我们可以简单地用前面的表达式替换前一个表达式中的daytotal。 数据集输出如下所示。

Product Week Day WeekTotal DayTotal WeekTotalAverage A 1 1 550 250 183.333333333333 A 1 2 550 200 183.333333333333 A 1 3 550 100 183.333333333333 A 2 1 600 300 200 A 2 2 600 NULL 200 A 2 3 600 NULL 200 B 1 1 175 100 58.3333333333333 B 1 2 175 50 58.3333333333333 B 1 3 175 25 58.3333333333333

我已将此列的结果转换为浮点数，或者当结果重复出现时，我们最终会丢失数字的一部分。 因此，在这种情况下，产品B第1周有三个条目，因此我们采用周总数（165）并除以3得到58.33重复。

报告设计也有所改变。 它现在看起来像这样。

周总数现在只是[WeekTotal] （以前是[SUM(DayTotal)] ），现在是平均值的表达式..

=Sum(Fields!WeekTotalAverage.Value) / CountDistinct(Fields!Week.Value)

在矩阵中，有两个行组，第一组按Week ，第二组（子组） Day分组。 列组按Product分组。

You can simply sum all the detail values (at day level) then divide by the number of unique weeks. Something like this...

=Sum(Fields!DayTotal.Value)/CountDistinct(Fields!Week.Value)

In your sample CountDistinct(Fields!Week.Value) will return 2

This all assumes you have a simple Matrix as your report design something like this...

The expression is the expression above to calculate the average.

Please note in my sample data I have more detail so I grouped by day as well as week. In your case you don;t need to group by day (but it will still work if you do)

Also, you don't need the week total as you can simply sum the day totals at the week group level.

UPDATE based on OP comments:

As you need to use the actual week totals in your data (which are not always just a sum of the day totals as I thought previously) we will have to edit the dataset and do a bit of the work there. Below is the code is used to generate your sample dataset.

DECLARE @t TABLE (Product varchar(1), [Week] int, [Day] int, WeekTotal int, DayTotal[int]) INSERT INTO @t VALUES ('A', 1, 1, 550, 250), ('A', 1, 2, 550, 200), ('A', 1, 3, 550, 100), ('B', 1, 1, 175, 100), ('B', 1, 2, 175, 50), ('B', 1, 3, 175, 25), ('A', 2, 1, 600, 300), ('A', 2, 2, 600, NULL), ('A', 2, 3, 600, NULL) SELECT * , CAST(WeekTotal as float) / COUNT(*) OVER(PARTITION BY Product, [Week]) as WeekTotalAverage FROM @t

Notice that I now return an extra column WeekTotalAverage (I know, poor name!). What this does is give us an average per product/week of the weektotal, we can then simply replace the daytotal in the previous expression with this field. The dataset output looks like this.

Product Week Day WeekTotal DayTotal WeekTotalAverage A 1 1 550 250 183.333333333333 A 1 2 550 200 183.333333333333 A 1 3 550 100 183.333333333333 A 2 1 600 300 200 A 2 2 600 NULL 200 A 2 3 600 NULL 200 B 1 1 175 100 58.3333333333333 B 1 2 175 50 58.3333333333333 B 1 3 175 25 58.3333333333333

I've CAST the result of this column to a float or we end up losing fractions of the numbers when the result is recurring. So in this case product B week 1 has three entries so we take the week total (165) and divide by 3 to give us 58.33 recurring.

The report design changes a little too. It now looks like this.

The week total is now just [WeekTotal] (previously it was [SUM(DayTotal)]) and the expression for the Average is now..

=Sum(Fields!WeekTotalAverage.Value) / CountDistinct(Fields!Week.Value)

In the matrix, there are two row groups, the first groups by Week and the second (child group) is grouped by Day. The column group is grouped on Product.