SSRS - SUM based on condition

In this article , let us see an example on how to SUM based on condition in SSRS tablix report .

Consider below query is my report dataset :

DECLARE @tmp TABLE (Code VARCHAR(10),Level1 INT,Level2 INT)
INSERT @tmp SELECT 'CHN',5,10
INSERT @tmp SELECT 'KOL',5,15
INSERT @tmp SELECT 'IND',10,25
INSERT @tmp SELECT 'NY',5,10
INSERT @tmp SELECT 'TX',5,15
INSERT @tmp SELECT 'US',10,25
SELECT * FROM @tmp

Consider, I have tablix report as shown in below image :

In the above shown report, If we are required to SUM values in the Columns Level1 and Level2, only for Code = "IND" and Code = "US", then we need to write expression as shown below :




In the Total data row, under Level1 column expression:

=Sum(IIF(Fields!Code.Value="IND" OR Fields!Code.Value="US",Fields!Level1.Value,0))

In the Total data row, under Level2 column expression:

=Sum(IIF(Fields!Code.Value="IND" OR Fields!Code.Value="US",Fields!Level2.Value,0))

Reference : 

• http://technet.microsoft.com/en-us/library/ms159134(v=sql.100).aspx