1

I'm having an issue in my SSRS report. What I'm trying to accomplish is get a correct summary total for a particular group, on field ClaimNumber

How the particular formula works is based on other fields in the record, and on of those values being zero:

=iif(Fields!FieldA.Value=0,0,Fields!FieldA.Value-Fields!FieldB.Value-Fields!FieldC.Value)

So if FieldA is zero, the displayed value is 0, otherwise it displays FieldA-FieldB-FieldC.

This works fine for each individual lineitem, but the summary totals on the grouping on ClaimNumber are incorrect, as in some cases the FieldA value under that particular ClaimNumber is 0, sometimes not. So I'm having trouble summing on the ClaimNumber grouping total, as the summary does not know which records FieldA is 0, and which are not.

There must be an SSRS custom code formula that would work in this situation?

galmeriol
  • 461
  • 4
  • 14
Scott
  • 165
  • 1
  • 3
  • 15

1 Answers1

0

If you wrap a Sum function around your expression, or even around each individual column in the expression, it's only going to evaluate that once. In other words, this describes the issue you're having where it's not accounting for some of the rows having 0 in FieldA. One way to correct this is to make your expression a calculated field on your dataset. Then you can Aggregate the calculated field to get totals and subtotals. Since it is a calculated field, it will be evaluated row by row to get the correct total. So the good news is: no custom code!

StevenWhite
  • 5,907
  • 3
  • 21
  • 46
  • Hi Steve - I get an error when creating a calculated field and trying to do a summation. The calculated field(called "contract amount") within the dataset reads: =iif(Fields!FieldA.Value=0,0,Fields!FieldA.Value-Fields!FieldB.Value-Fields!FieldC.Value). This works fine in each individual row, but when I try to use aggregate summation in the group within the table - using =Sum(Fields!contract_amount.Value, "groupfield") - I get an error- ONLY on a summation which contains a row where FieldA is zero. ALL other summation by group calculate correctly. Very non descriptive "#Error". Any ideas? – Scott May 21 '18 at 19:56
  • Try simply `=Sum(Fields!contract_amount.Value)` – StevenWhite May 21 '18 at 23:30
  • @Scott Values A, B, C are integer or decimal? Try =SUM(iif(Fields!FieldA.Value=0,Cdec(0),Fields!FieldA.Value-Fields!FieldB.Value-Fields!FieldC.Value)) – niktrs May 22 '18 at 11:44
  • Awesome! That worked. Looks like it DOES make a difference, int vs decimal. Thanks to both of you! – Scott May 22 '18 at 14:31