仅对过滤行中的值求和 [英] Summing Values in filtered rows only

问题描述

我有一个 Report Builder 3.0 报告，该报告使用绑定到参数的组过滤器来显示数据.如何仅在可见行上对这些列求和?

I have a Report Builder 3.0 report that uses a group filter tied to a parameter to display data. How do I sum these columns on only the visible rows?

我需要对 Fields!PY_Dollars.Value、Fields!CY_Dollars.Value 求和，并计算 py-cy 和两者之间的百分比差异

I need to sum Fields!PY_Dollars.Value, Fields!CY_Dollars.Value and also calculate py-cy and the % difference between the two

在 PY 值为零或空值的情况 2 中，我不需要合计 py 值或进行比较行.如果 Parameters!Store.Value=1

In case 2 where PY values are zero or null, I don't need to total the py values or do the comparison rows. I'm hiding those if the Parameters!Store.Value=1

组过滤器[bool 表达式 = TRUE]:

Group Filter[bool expression = TRUE]:

=(
  (Parameters!Store.Value=2) 
  OR 
  (Parameters!Store.Value=1 AND (IsNothing(SUM(Fields!PY_Dollars.Value)) OR (SUM(Fields!PY_Dollars.Value)=0))) 
  OR
  (Parameters!Store.Value=0 AND SUM(Fields!PY_Dollars.Value)>0)
)

推荐答案

好的，我很确定我想出了如何完成这项工作.将 CY 美元值和 PY 美元值的总和移动到构建报告的 sql 查询中.我保持分组相同，使用相同的过滤器组.此处显示了细微更改:

OK I'm pretty sure I figured out how to get this done. Moved the summing of CY dollars value and PY dollars value into the sql query that builds the report. I kept the grouping the same, with the same filter group. The minor changes are shown here:

=(
  (Parameters!Store.Value=2) 
  OR 
  (Parameters!Store.Value=1 AND (IsNothing(Fields!Sum_PY_Dollars.Value) OR (Fields!Sum_PY_Dollars.Value=0))) 
  OR
  (Parameters!Store.Value=0 AND Fields!Sum_PY_Dollars.Value>0)
)

要在报告中显示正确的 CY 值，表达式需要 Switch() 函数.

To get the correct CY values to show through the report, the expression requires the Switch() function.

=SUM(
    Switch(
        (Parameters!Store.Value=2), (Fields!Sum_CY_Dollars.Value) ,  
        (Parameters!Store.Value=1 AND (IsNothing(Fields!Sum_PY_Dollars.Value) OR Fields!Sum_PY_Dollars.Value=0)), (Fields!Sum_CY_Dollars.Value) , 
        (Parameters!Store.Value=0 AND Fields!Sum_PY_Dollars.Value>0), (Fields!Sum_CY_Dollars.Value)
        )
    )

这基本上告诉它只总结过滤器显示的内容.由于 PY 只需要在它存在的地方求和，

This basically tells it to sum only what the filter is showing. Since PY only needs to be summed where it exists,

这篇关于仅对过滤行中的值求和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！