SSRS - SUM based on condition

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