Grand total for an SSRS calculated field

0
votes

i have a column in the report in which it stores calculated values depending on a set of iif statements, and calculate subtotals:

=sum(iif(Fields!EDLCode.Value = "X", Fields!Amount.Value, 0)/3 + iif(Fields!EDLCode.Value = XXX, Fields!SubjectAmt.Value, 0) - iif(InStr(Fields!Description1.Value, "YYY"),Fields!SubjectAmt.Value,0))

Question is how to show a grand total for that column in the report? Now it looks like that: enter image description here

enter image description here

2
reporting-services

2 Answers

0
votes

There are a couple of problems with your expression

  1. No quotes around XXX
  2. InStr returns a value but you do not do anything with it

I have assume for the InStr function you are checking if "YYY" appears in the string, if not then modify the comparison to suit your needs..

Try this

=SUM(
    IIF(Fields!EDLCode.Value = "X", Fields!Amount.Value, 0)/3 + 
    IIF(Fields!EDLCode.Value = "XXX", Fields!SubjectAmt.Value, 0) - 
    IIF(InStr(Fields!Description1.Value, "YYY") >0, Fields!SubjectAmt.Value, 0)
    )
0
votes

Figured it out. I had to set CDec(0) instead of just 0.

=sum(
iif(Fields!EDLCode.Value = "X", Fields!Amount.Value, CDec(0))/3 + 
iif(Fields!EDLCode.Value = XXX, Fields!SubjectAmt.Value, CDec(0)) - 
iif(InStr(Fields!Description1.Value, "YYY"),Fields!SubjectAmt.Value,CDec(0)))

This is working now.