=Sum(Fields!balance_due.Value)

Customer   Name    Balance Due
1001    Bob      100.99
1002    Jim      10.95
1003    Kim      -12.67
1004    Lisa      13.23
    Total        $137.84

It should be like this:

Customer   Name    Balance Due
1001    Bob      100.99
1002    Jim      10.95
1003    Kim      -12.67
1004    Lisa      13.23
    Total        112.5

How can I get SSRS to add and subtracted numbers.

• Edited by M Kennedy Monday, April 20, 2015 5:09 PM

What version of SSRS do you have and what CU or SP are you patched up to?

Windows Server 2008 R2 Standard SP1
SQL 2008 R2 SP3
SSRS Report Builder 3.0

Is balance_due a calculated value like (credits-debits)? I don't know why, but if it is SUM doesn't work sometimes when it is negative.  Replace =Sum(Fields!balance_due.Value) with =Sum({balance_due formula})

  

I get an "Syntax Error" - "Expression expected"

Hi M Kennedy,

According to your description, the sum() function doesnt subtracting negative numbers.

As we tested in our environment(SQL Server 2008 R2), the sum() function could subtracting negative numbers. In your scenario, you could specify the expression like below to get corresponding total values.
=sum(IIF(Fields!Balance_Due.Value<0,(-Abs(Fields!Balance_Due.Value)),Fields!Balance_Due.Value))

Also please check if "-12.67" is a negative value in your report. Please refer to screenshots below:

If you have any question, please feel free to ask.

Best regards,
Qiuyun Yu

When I said "=Sum({balance_due formula})" I meant replace {balance_due formula} with the formula used in balance_due, ie =Sum(Fields!Credits.Value-Fields!Debits.Value)" .

I tested it again and here is the output:

Customer	Name	Balance Due	
1001	Bob  	100.99	Positive
1002	Jim  	10.95	Positive
1004	Lisa 	13.23	Positive

Negative numbers didn't even show up?

But when I run the query in SSMS

Customer	Name	Balance Due
1001	Bob  	100.99
1002	Jim  	10.95
1003	Kim	-12.67
1004	Lisa 	13.23
		112.5

Why don't it show up SSRS?

Same output as before.

I thank because it do not see Negative numbers. :-(

I rewrote this report from scratch and it is now adding and subtracting like it should.

Now I just need to figure out how to get it to display Positive Balance only.

Thanks