reporting-services - SSRS 将行百分比添加到 Tablix

Question

我正在尝试将某种计算项（Excel 等效项）添加到我从数据集创建的 tablix 中。

例如

Type       Today        Month       Annual
Sales      1,000        15,000      35,000
GP           200         1,500       5,000

我想在下面添加一行来计算保证金百分比并且也是动态的，所以当数字改变时，保证金百分比也会改变

例如

Type       Today        Month       Annual
Sales      1,000        15,000      35,000
GP           200         1,500       5,000
Margin %     20%           10%        14.3%

标签： reporting-servicesssrs-2012ssrs-tablix

Answer 1

如果这是在页脚中，那么您可以有条件地对字段求和Type，例如对于Today列：

=SUM(IIF(Fields!Type.Value = "GP", Fields!Today.Value, Nothing)) / SUM(IIF(Fields!Type.Value = "Sales", Fields!Today.Value, Nothing)) 

当然，如果 Sales 可以为零，那么您将得到除零错误，因此您需要使表达式更复杂一些，以绕过函数参数的错误生成除零计算：IIF

=IIF(SUM(IIF(Fields!Type.Value = "Sales", Fields!Today.Value, 0.0)) <> 0.0, 
SUM(IIF(Fields!Type.Value = "GP", Fields!Today.Value, Nothing)) / IIF(SUM(IIF(Fields!Type.Value = "Sales", Fields!Today.Value, 0.0)) = 0.0, 1.0, SUM(IIF(Fields!Type.Value = "Sales", Fields!Today.Value, Nothing))),
Nothing)