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?
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!