Reporting Services - 计算表中的行总数/百分比

I am trying to calculate totals for each row as well as a percentage of the overall total.

我试图计算每一行的总数以及总计的百分比。

Right now I have a table like this:

现在我有一个这样的表:

Blah    Col1    Col2    Col3
-----------------------------
ABC      1        1      1
DEF      2        2      3
-----------------------------
Total    3        3      4

And I want it to include totals/percentages like so:

我希望它包括总数/百分比,如下:

Blah    Col1    Col2    Col3    Total    %
--------------------------------------------
ABC      1        1      1        3     30%
DEF      2        2      3        7     70%
--------------------------------------------
Total    3        3      4        10    100%

I know I can do the calculations in the SQL query, but the stored procedure is rather complicated so I'd like to avoid that if possible. So I'm wondering if there's a simple way to achieve this in SSRS.

我知道我可以在SQL查询中进行计算,但存储过程相当复杂,所以我想尽可能避免这种情况。所以我想知道在SSRS中是否有一种简单的方法可以实现这一点。

Right now I just have a row group for each Blah which I use to calculate column totals.

现在我只为每个Blah设置一个行组,用于计算列总数。

5 个解决方案

#1

I added a Totals Row for my matrix, then I referenced the totals textbox (textbox 8 in my case) for the column and I did:

我为我的矩阵添加了一个Totals Row,然后我引用了该列的总计文本框(在我的情况下是文本框8),我做了:

Sum(Fields.FieldName.Value)/ReportItems!Textbox8.Value

I hope this makes sense!

我希望这是有道理的!

#2

To calculate the total, just do a simple sum using the + operator. For the percentage, you can refer to the grand total using ReportItems!ItemName.

要计算总数,只需使用+运算符进行简单求和即可。对于百分比,您可以使用ReportItems!ItemName来引用总计。

#3

You can use aggregate functions in Reporting Services like "SUM" and "AVG" to achieve what you are trying to do. The way it works is "Detail" parts of groups in SSRS tables will list all of the data, while non-detail parts (like headers and footers) of groups can be used for aggregates like:

您可以在Reporting Services中使用聚合函数(如“SUM”和“AVG”)来实现您要执行的操作。它的工作方式是SSRS表中“细节”组的部分将列出所有数据,而组的非细节部分(如页眉和页脚)可用于聚合,如:

=SUM(Fields!TestValue.Value)

http://msdn.microsoft.com/en-us/library/ms159134%28v=sql.90%29.aspx

Let me know if you need any more help.

如果您需要更多帮助,请与我们联系。

#4

Create two groups, one on a column that is the same data for each row, then one on column blah. add a row for the emcompassing group and do a sum there.

创建两个组,一个在列上,每个行的数据相同,然后在列blah上创建一个。为emcompassing组添加一行并在那里进行总结。

#5

you can simply do as following: Sum(CInt(Fields!TestValue.Value)) or Sum(CInt(Fields!DollarAmountOfCheck.Value),"DataSet1") sometime when data is coming through WCF, it does not accept Sum() function. but this works fine in that case.

你可以简单地做如下:Sum(CInt(Fields!TestValue.Value))或Sum(CInt(Fields!DollarAmountOfCheck.Value),“DataSet1”)有时当数据来自WCF时,它不接受Sum()函数。但在这种情况下,这种方法很好。

#1