Search code examples
reporting-servicesformula

Sum two financial fields in an SSRS report

My current forumla is:

=Fields!ag_new_previousentryfeespaid.Value + Fields!ag_new_totalentryfeescollected.Value

Which results in the following: $5$20

instead of $25.

I've tried:

SUM(=Fields!ag_new_previousentryfeespaid.Value) + SUM(Fields!ag_new_totalentryfeescollected.Value)

which results in an error.

Based on another query here, assuming the problem could be that values weren't regarded as an integer, I did this:

=CStr(CInt(Fields!ag_new_previousentryfeespaid.Value) + 
CInt(SUM(Fields!ag_new_totalentryfeescollected.Value)))

Which also gave an error.

There is no null possibility for these fields, so that can't be it either.

I'm sure there's a simple explanation for this - can someone please advise?

Thank you.

Solution

  • Based on the result of your fir5st expression, it looks like the fields you are trying to sum are string with the $ included

    Your last option was the closest in in that case you have tried to sum the string and then convert it to an int, which will not work.

    The easiest method is probably to strip the $ out and then convert the result before summing so something like this should work.

    =VAL(REPLACE(Fields!ag_new_previousentryfeespaid.Value, "$", ""))
+ VAL(REPLACE(Fields!ag_new_totalentryfeescollected.Value, "$", ""))

    All we are doing here is replacing the $ with an empty string and using VAL to give us the numeric representation, then summing the two.

    If this does not work then you might have leading or trailing spaces etc... In which case you will have to replace those too with something like.

    =VAL(REPLACE(REPLACE(Fields!ag_new_previousentryfeespaid.Value, " ", ""), "$", ""))
+ VAL(REPLACE(REPLACE(Fields!ag_new_totalentryfeescollected.Value, " ", ""), "$", ""))