使用 SQL Server 报表生成器执行类似 SUMIF 的操作 [英] Carrying out a SUMIF like operation using SQL Server Report Builder

问题描述

我正在尝试在 SQL Server Report Builder 3.0 中生成条件总和.

I'm trying to produce a conditional sum in SQL Server Report Builder 3.0.

我的表情是这样的:

=Sum(Iif(Fields!ProjectTypeID.Value=2,Fields!kWp.Value,0))

我希望这个表达式能够得出所有类型 2 项目的 kWp 总和.

I'd hoped that this expression would produce a sum of the kWp of all projects of type 2.

不幸的是，事实并非如此.我似乎无法弄清楚为什么.它只返回 0 结果，即使我知道 kWp 列中有非零值，并且该列不包含空值.

Unfortunately, it is not to be. And I can't seem to work out why. It just returns a 0 result, even though I know that there are non-zero values in the kWp column, and the column does not contain nulls.

一位同事确实通过更换

A colleague did manage to get a positive result by replacing the

Fields!kWp.Value 

与

1 * Fields!kWp.Value

但我们不知道为什么会这样，因此不能真正相信答案.

But we have no idea why this works, and therefore, can't really trust the answer.

我怎样才能让这个条件总和自己表现出来?

How can I get this conditional sum to behave itself?

推荐答案

列 'kWp' 的数据类型是 Decimal，因此您需要将默认值转换为 0.00 或将列转换为 double

The data type of the column 'kWp' is Decimal so you need to either convert the default value to 0.00 or cast the column to double

 SUM(iif(Fields!ProjectTypeID.Value = 2,cdbl(Fields!kWp.Value),0.00))

这篇关于使用 SQL Server 报表生成器执行类似 SUMIF 的操作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！