Search code examples
filtersumreportbuilderrdl

rdl report Filter sums

How can i write the expression in rdl report for the following : 

Sum(Fields!Amount.Value) where AccountType='Income' - SUM(Fields!Amount.Value) where AccountType='Cost of Goods Sold')

Am Using only 1 dataset with the following columns AccountType AccountName Amount

Solution

  • I don't know if there is any other way but this is the only way I found .

    create 2 variables in the rdl report .

    e.g

    1st variable IncomeTotal I set the expression to

    =SUM(iif(Fields!AccountType.Value="Income",CDbl(Fields!Amount.Value),CDbl(0.00)),"YourDatasetName")

    2nd variable CostOfGoodsSoldTotal I set the expression to 

    =SUM(iif(Fields!AccountType.Value="Cost of Goods Sold",CDbl(Fields!Amount.Value),CDbl(0.00)),"YourDatasetName")

    And then I subtract the 2 variables where appropriate with the following expression

    =Variables!IncomeTotal.Value - Variables!CostOfGoodsSoldTotal.Value