Tablix Report with Expression field shows NaN

Posted by: sboyer on 12 September 2017, 11:43 am EST

  • Posted 12 September 2017, 11:43 am EST

    I am using AR 11 version 11.1.9726.0. I am using Page Report which has tablix control with expression field. When i generate the report the expression field shows the value as 'NaN' (because divide by zero).
    I want to replace the 'NaN' value with 0, can you provide me a solution how to do this.

    Please run the attached sample project and notice the 'Expr' column for 'NaN' values.
    Where the expression value is
    "=SUM(Fields.Item("Inv Total Charge").Value)/SUM(Fields.Item("Inv Num").Value)"

    Note: This report is generated dynamically and the field information(including expression) coming from SQL database.

    Replacing above expression with below expression i get the expected results without 'NaN'.

    "=IIF((IsNothing(SUM(Fields.Item("Inv Num").Value))=True), ToDouble("0").ToString("#,##0.00"), SUM(Fields.Item("Inv Total Charge").Value)/SUM(Fields.Item("Inv Num").Value))"

    But the problem is i have to parse the expression and find the denominator field or fields and add expression validations. Is there any alternate solution handle the 'NaN' values like this. Using this value still shows 'NaN' value.
    =IIF(IsNothing(SUM(Fields.Item("Inv Total Charge").Value)/SUM(Fields.Item("Inv Num").Value))=True, ToDouble("0").ToString("#,##0.00"), SUM(Fields.Item("Inv Total Charge").Value)/SUM(Fields.Item("Inv Num").Value))


  • Replied 12 September 2017, 11:46 am EST
  • Marked as Answer

    Replied 14 September 2017, 3:50 pm EST

    Hi Anwar,

    Please use the following expression to display 0 instead of NaN in the Expr column:

    =IIF(Double.IsNaN(SUM(Fields.Item("Inv Total Charge").Value)/SUM(Fields.Item("Inv Num").Value)), 0, (SUM(Fields.Item("Inv Total Charge").Value)/SUM(Fields.Item("Inv Num").Value)))

    I've attached the modified application for reference.

  • Replied 15 September 2017, 9:50 am EST

    Hi Abdias,
    Thanks for the update. I tested your solution and it works,
  • Replied 19 September 2017, 9:59 am EST

    I found one more scenario where the Double.IsNaN and IsNothing is not working correctly on expression fields. Can you please take a look the attached project (TestReport.rdlx) and let me know the solution.
    Expected result is 'Expression' cell value need to show '0' for row 'HRM' and column group 'Group 3'. And also 'Expression' RowTotal for the same row. See the attached image.
  • Replied 22 September 2017, 11:45 am EST


    The reason for this is that it is not divide by zero but divide by nothing. To handle this, instead of using the IIF operator I would suggest you to write a small function in the Script tab of the report and use that to return 0 if the divisor is nothing or 0 as below:

    Public Function Divider (ByVal Dividend, ByVal Divisor)
    If IsNothing(Divisor) Or Divisor = 0
    Return 0
    Return Dividend / Divisor
    End If
    End Function

    You can then use this function in the Expression as below:

    =Code.Divider((SUM(Fields.Item("Pmt Amt 99213").Value)+SUM(Fields.Item("Pmt Amt 99199").Value)), SUM(Fields.Item("Pmt Amt 93010").Value))

    I've attached the modified sample for your reference. Please check.

  • Replied 22 September 2017, 6:11 pm EST

    Hello Abdias,

    Thanks for the update. Looks like you missed attaching your sample project. Can you please upload the sample project.

  • Replied 25 September 2017, 5:47 am EST

    Sorry for missing the attachment. I've attached it now. Please
  • Replied 27 September 2017, 8:38 am EST

    Hello Abdias,
    Thanks for the solution and it works if i know the Dividend and Divisor values.
    My problem was, i am generating the .rdlx report dynamically and the fields, expression information is coming from database. To get the Dividend and Divisor values from the expression i have to parse the expression.
    Is there any alternate solution other than parsing the expression and get the expected results.
  • Replied 28 September 2017, 2:07 am EST

    I'm not sure if I understood you correctly, but in the modified report (Test Report.rdlx), it's only the name of the Fields that are passed to the function from the Expression (which was already written in the report you sent). The function is generic and you can pass whatever field you want to be validated and displayed as 0.0 instead of NaN. Please run the application I sent earlier and check if it works correctly at your end.
  • Replied 28 September 2017, 5:33 pm EST

    Hi Abdias,
    I ran your application and i see the 0.0 instead of NaN. The field value is using below function call. Where you parsed(manually) my original expression value in to two parts and pass to the function.

    =Code.Divider((SUM(Fields.Item("Pmt Amt 99213").Value)+SUM(Fields.Item("Pmt Amt 99199").Value)), SUM(Fields.Item("Pmt Amt 93010").Value))

    My original expression value : (SUM(Fields.Item("Pmt Amt 99213").Value)+SUM(Fields.Item("Pmt Amt 99199").Value))/SUM(Fields.Item("Pmt Amt 93010").Value)

    Dividend - (SUM(Fields.Item("Pmt Amt 99213").Value)+SUM(Fields.Item("Pmt Amt 99199").Value))

    Divisor - SUM(Fields.Item("Pmt Amt 93010").Value)

    My question is, to get the Dividend and Divisor values from my expression, i have to parse the expression programatically (because i am generating the report(.rdlx) dynamically and report fields are stored in database) and pass to =Code.Divider(...) function.

    Is there an alternate solution other than parsing the expression to get Dividend and Divisor values or write different function by passing my original expression to get the 0.0 value.

  • Replied 3 October 2017, 2:51 pm EST

    Since your entire expression is coming from the db, you would have to split it in the Script using the Split function -

    Unfortunately, there's no alternate solution apart from parsing the expression in code. Sorry for the inconvenience.
Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels