locked
SSRS expression help RRS feed

  • Question

  • iif(Fields!Actded.Value <=0 ,0, iif(Fields!Actded.Value >0 and Fields!Actded.Value < 500001,Fields!Actuals.Value -Fields!Deductible.Value,500000))

    The above expression is used in the detail section in a column. I want to add sum to this in the total section how can i ADD sum in this expression?

    Any help ?


    FM

    Tuesday, April 9, 2013 7:46 PM

Answers

  • Try this.

    Create a hidden column with the expression :

    =RunningValue(iif(Fields!Actded.Value <=0 ,0, iif(Fields!Actded.Value >0 and Fields!Actded.Value < 500001,Fields!Actuals.Value -Fields!Deductible.Value,500000)),sum,nothing)

    In the total textbox use this expression:

    =ReportItems!Textbox.RunningValueTextboxName.Value

    Replace the report textbox name with correct name.

    I assume you do not have any corrupt data (nulls and other issues) otherwise you will have to handle that also.

    - Girija

    • Proposed as answer by Fanny Liu Thursday, April 11, 2013 7:22 AM
    • Marked as answer by Fanny Liu Monday, April 15, 2013 9:14 AM
    Wednesday, April 10, 2013 10:51 PM

All replies

  • Well from what I understand you are trying to SUM the resultant from the iif expression right ? If that is true then change your expression for the Total to be SUM(IIF()).

    Hope this helps..........

    • Proposed as answer by Vinaya Ravi Wednesday, April 10, 2013 12:27 AM
    Tuesday, April 9, 2013 9:37 PM
  • I tried this 

    sum(iif(Fields!Actded.Value <=0 ,0, iif(Fields!Actded.Value >0 and Fields!Actded.Value < 500001,Fields!Actuals.Value -Fields!Deductible.Value,500000)))

    I get #Error when i run the report......................................................... ummm


    FM

    Wednesday, April 10, 2013 7:39 PM
  • Try this in total expression.

    iif(SUM(Fields!Actded.Value) <=0 ,0, iif(SUM(Fields!Actded.Value) >0 and SUM(Fields!Actded.Value) < 500001,SUM(Fields!Actuals.Value) -SUM(Fields!Deductible.Value),500000))

    Wednesday, April 10, 2013 10:16 PM
  • Try this in total expression.

    iif(SUM(Fields!Actded.Value) <=0 ,0, iif(SUM(Fields!Actded.Value) >0 and SUM(Fields!Actded.Value) < 500001,SUM(Fields!Actuals.Value) -SUM(Fields!Deductible.Value),500000))

    Wednesday, April 10, 2013 10:17 PM
  • Try this.

    Create a hidden column with the expression :

    =RunningValue(iif(Fields!Actded.Value <=0 ,0, iif(Fields!Actded.Value >0 and Fields!Actded.Value < 500001,Fields!Actuals.Value -Fields!Deductible.Value,500000)),sum,nothing)

    In the total textbox use this expression:

    =ReportItems!Textbox.RunningValueTextboxName.Value

    Replace the report textbox name with correct name.

    I assume you do not have any corrupt data (nulls and other issues) otherwise you will have to handle that also.

    - Girija

    • Proposed as answer by Fanny Liu Thursday, April 11, 2013 7:22 AM
    • Marked as answer by Fanny Liu Monday, April 15, 2013 9:14 AM
    Wednesday, April 10, 2013 10:51 PM