Carrying out a SUMIF like operation using SQL Server Report Builder

sql-server reporting-services reportbuilder
Daniel Neal picture Daniel Neal · Jun 14, 2012 · Viewed 54.2k times · Source

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

My expression looks like this: 

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

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

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

with 

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?

Answer

praveen picture praveen · Jun 14, 2012

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))

Related questions

How do I show a qr code in SSRS?

I am in the quest to build a report in ssrs/report builder that contains a qr code, is it possible to achieve this? I was thinking in implementing a class using c#/vb.net that codes any url/mail …

Read More
SSRS Report Builder 2.0 - Use with SQL 2005

I'm starting to look into SSRS Report Builder 2.0, and I see in the documentation that it supports connections to SQL 2005. Does anyone know if the reports made against SQL 2005 can then be upload to the SSRS 2005 website and ran from …

Read More
SQL variable to hold list of integers

I'm trying to debug someone else's SQL reports and have placed the underlying reports query into a query windows of SQL 2012. One of the parameters the report asks for is a list of integers. This is achieved on the report …

Read More