Divide By Zero Error

[Mike]

I am using this to catch divide by zero errors and it's not working.  What am I doing wrong with this expression in the report?

 

=iif(Fields.Price2 <>0,(Fields.Price1 /Fields.Price2) * 1000,0)

[Steve]

Hello Mike,

Can you explain what do you mean by "it's not working"? I've just tried your expression within a sample report and it works fine on my end (sample report attached). Simply change a record for the UnitPrice to return 0.

Regards,
Steve
the Telerik team

Check out Telerik Trainer, the state of the art learning tool for Telerik products.

[Mike]

I use that formula and it returns a red box with the message:

"An error occured while processing TextBox "TextBoxPrice":
 Attempt to deivide by zero.

[Steve]

Hello Mike,

I've attached a sample report in my previous post and would appreciate it if you modify it to replicate the error, so that we can investigate.

All the best,
Steve
the Telerik team

Check out Telerik Trainer, the state of the art learning tool for Telerik products.

[Mike]

Steve,
It looks like both values are coming back zero! darn it! 

=iif(Fields.Price1 <> 0, (iif(Fields.Price2<> 0, Fields.Price2, 1)/iif(Fields.Price1 <> 0, Fields.Price1 , 1) * 1000), 0)

[Jean]

It seems that telerik evalues the truepart and the falsepart whatever the condition (and not the false part if condition is false, the true part if condition is true). And if one of the part generate an exception (access member of null value, divide by zero, ...), there is an error message.

For my example =IIf(ClassLibrary1.Test.CheckIsNull(Fields.Agent)=1,"Unknown !!", Fields.Agent.FirstName)
=> The expression contains object 'FirstName' that is not defined in the current context on row where Fields.Agent is null.

[Steve]

Hi Fabrice,

The experienced behavior is correct and by design. As you can see IIF() is a function and you need to prepare the parameters prior to executing the function (as most of the programming languages work).

That's why the reporting engine first evaluates the parameters and then pass the result values to the function. Checking for null values can be done either on database level or using our built-in IsNull() function.
Regards,
Steve
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Check out the tips for optimizing your support resource searches.

[Jean]

I understand why but there is no workaround (except by code .net of course) for me because Fields.Agent.FirstName always generates an exception when Fields.Agent is null because it  is always evaluated.

[Mike]

Here is what I have done to keep from using the iif and have more control over the data I am showing in the reports.

Code Behind of the report.aspx.cs page.

	private void RunReport()
	{
	ReportDetail thisReport = new ReportDetail();
	thisReport.GetReport(Convert.ToDateTime(RadDatePickerStartDate.SelectedDate),
	Convert.ToDateTime(RadDatePickerEndDate.SelectedDate),
	Convert.ToString(RadComboBoxUser.SelectedValue));
	ReportViewer1.Report = thisReport ;
	}

Code Behind for the ReportDetail.cs which is in a separate Reports Project....

	using System;
	using System.Data;
	using System.Collections.Generic;
	using System.Text;
	using System.Data;
	using System.Web.UI;
	public partial class ReportDetail : Telerik.Reporting.Report
	{
	#region Fields
	private ReportBLL _report = new ReportBLL();  // My Report Business Object in a seperate project
	#endregion
	/// <summary>
	/// Initializes a new instance of the <see cref="ReportDetail"/> class.
	/// </summary>
	public ReportDetail ()
	{
	/// <summary>
	/// Required for telerik Reporting designer support
	/// </summary>
	InitializeComponent();
	}
	public void GetReport(DateTime StartDate, DateTime EndDate, int UserId)
	{
	_refreshList.StartDate = Convert.ToDateTime(StartDate);
	_refreshList.EndDate = Convert.ToDateTime(EndDate);
	_refreshList.UserId = Convert.ToInt(UserId);
	DataTable dt = _report.UserActivity;
	Report.DataSource = GetReportData(dt);
	}
	private List<ReportMain> GetReportData(DataTable dt)
	{
	IList<ReportMain> data = new List<ReportMain>();
	foreach (DataRow dr in dt.Rows)
	{
	// We had some bad test data (-12826613928.3934) so CheckInt32 is now used for Value1
	string Value1 = ReportMain.CheckInt32(Convert.ToString(dr["Sales"]));
	ReportMain reportData = new ReportMain();
	reportData.Old = Convert.ToInt32(dr["OLD"]);
	reportData.New = Convert.ToInt32(dr["NEW"]);
	reportData.CalculatedValue = ReportMain.DivideValues(Convert.ToInt32(Value1), Convert.ToInt32(dr["Value2"]));
	data.Add(reportData);
	}
	return data as List<ReportMain>;
	}

I just decided to make a ReportMain.cs class which has private fields, public properties public properties and statics for division.  The public properties are what I bind to the report.  Here is just a sample of that class and I use the "New" as a field in the report.  I also have the Stored Procedure use the same names so there is no confusion when creating the report.

	#region Fields
	private int _new;
	#endregion
	#region Properties
	public int New
	{
	get { return _new; }
	set { _new = value; }
	}
	#endregion
	public static decimal DivideValues(decimal Value1, decimal Value2)
	{
	try
	{
	return (Convert.ToDecimal(Value1) / Convert.ToDecimal(Value2));
	}
	catch
	{
	return 0;
	}
	}

[Ryan]

If you want a less robust and more "Hacky" workaround, just make each expression evaluate without an error.

=iif(Fields.Balance1 = 0,'NA',(((Fields.Balance2-Fields.Balance1)/iif(Fields.Balance1=0,1,Fields.Balance1))))

[Vitrum]

=Fields.Price1 / iif(Fields.Price2<>0, Fields.Price2, Null)

[Javier]

Thanks Vitrum,

 

I just modify it a little bit to return 0 instead of null

 =iif(Fields.New_customers<>0,Fields.adCost/ iif(Fields.New_customers<>0,Fields.New_customers,null),0)

 

Thanks again

[Nasko]

Hello Javier,

The latest version of Telerik Reporting - Q1 2015 SP1 introduced logical operators allowing only the true or false part to be evaluated:

• The conditional operator "?:" returns one of the two values depending on the value of a Boolean expression. Usage: = Fields.B <> 0 ? Fields.A / Fields.B : "N/A"
  
  
• Null-coalescing operator "??". It returns the left-hand operand if the operand is not null, otherwise it returns the right hand operand. Usage: = Fields.A ?? "N/A"

Regards,
Nasko
Telerik

[Bryant]

works great thanks for the help!!

[Javier]

Thanks Nasko.

 

Works like a charm

[Rod]

Thank you! You just saved me a bunch of time.