Tablix Report with Expression field shows NaN

Tablix Report with Expression field shows NaN

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

• 

  sboyer

  • Post Options:
  • Link
    Copy
  • Jump To Answer

  Posted 12 September 2017, 2:43 am EST

  Hello,
  
  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))

  Thanks,
  
  Anwar

• 

  sboyer

  • Post Options:
  • Link
    Copy

  Posted 12 September 2017, 2:46 am EST

  NaNExport.zip

• 

  abdias.michael

  

  • Post Options:
  • Link
    Copy

  Posted 14 September 2017, 6:50 am 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.

  Thanks
  
  NaNExport_Modified.zip

• 

  sboyer

  • Post Options:
  • Link
    Copy

  Posted 15 September 2017, 12:50 am EST

  Hi Abdias,
  
  Thanks for the update. I tested your solution and it works,
  
  Thanks,
  
  Anwar

• 

  sboyer

  • Post Options:
  • Link
    Copy

  Posted 19 September 2017, 12:59 am EST

  Abdias,
  
  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.
  
  Thanks,
  
  AnwarNaNExport.zip

• 

  abdias.michael

  

  • Post Options:
  • Link
    Copy

  Posted 22 September 2017, 2:45 am EST

  Anwar,

  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
  	Else
  		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.

• 

  sboyer

  • Post Options:
  • Link
    Copy

  Posted 22 September 2017, 9:11 am EST

  Hello Abdias,

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

  thanks,
  
  Anwar

• 

  abdias.michael

  

  • Post Options:
  • Link
    Copy

  Posted 24 September 2017, 8:47 pm EST

  Sorry for missing the attachment. I’ve attached it now. Please check.NaNExport_Mod2.zip

• 

  sboyer

  • Post Options:
  • Link
    Copy

  Posted 26 September 2017, 11:38 pm 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.
  
  Thanks,
  
  Anwar

• 

  abdias.michael

  

  • Post Options:
  • Link
    Copy

  Posted 27 September 2017, 5:07 pm 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.

• 

  sboyer

  • Post Options:
  • Link
    Copy

  Posted 28 September 2017, 8:33 am 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.

  Thanks,
  
  Anwar

• 

  abdias.michael

  

  • Post Options:
  • Link
    Copy

  Posted 3 October 2017, 5:51 am EST

  Since your entire expression is coming from the db, you would have to split it in the Script using the Split function - https://msdn.microsoft.com/en-us/library/b873y76a(v=vs.110).aspx

  Unfortunately, there’s no alternate solution apart from parsing the expression in code. Sorry for the inconvenience.