0

I've got an expression that displays the summation of the values from my field: total

="Harvest Total: " &  CStr(Format(Sum(Fields!total.Value),"#,##0"))

But I want to subtract from this total the total summation for a specific value in another field: WeaponType

So I've tried to rewrite the expression in the following way:

="Harvest Total: " &  CStr (Format((Sum(Fields!total.Value) - IIF(Fields!WeaponType.Value="Archery",SUM(Fields!total.Value),Nothing)),"#,##0"))

Unfortunately, this expression does not give me an error and when I run the report, it returns a value of 0.

And I tried to reverse the options after the IIF:

="Harvest Total: " &  CStr (Format((Sum(Fields!total.Value) - IIF(Fields!WeaponType.Value="Archery",Nothing,SUM(Fields!total.Value))),"#,##0"))

This reversal does not have an affect either as it is returning the same value as my original expression.

Any ideas as to what I am doing wrong with this expression?

MISNole
  • 992
  • 1
  • 22
  • 48
  • 1
    Not sure if this is the right answer, but maybe try and reverse the IIF and the Sum on the new part: =`"Harvest Total: " & CStr (Format((Sum(Fields!total.Value) - Sum(IIF(Fields!WeaponType.Value="Archery",Fields!total.Value, 0))),"#,##0"))` – Alicia Sep 27 '17 at 07:25
  • The 2nd part of the expression should be `Sum(IIF(Fields!WeaponType.Value="Archery",Fields!total.Value‌​, Nothing))` I think. Use Nothing in place of 0. This should work. If it does not, then please provide a sample of data from your dataset and a screenshot of the report design/preview. – Alan Schofield Sep 27 '17 at 10:35
  • Both comments were helpful and led me to the correct answer but I cannot mark either as correct as comments - What I did was to reverse the IFF & SUM based on the suggestions and this was my final expression that worked: `="Harvest Total: " & CStr(Format(SUM(Fields!total.Value) - SUM(IIF(Fields!WeaponType.Value="Archery",Fields!total.Value,Nothing)),"#,##0"))` – MISNole Sep 27 '17 at 15:13
  • I'll leave it until tomorrow in case Alicia wants to supply an answer, I'm not bothered about getting the rep but it's better close answered questions for others that are searching for solutions. – Alan Schofield Sep 27 '17 at 16:12

0 Answers0