Search code examples
reporting-servicesreportvisual-studio-2017-build-tools

How to fix the error dividing by zero in my expression in visual studio reports?

I created report in visual studio.The problem is with dividing by zero in total fields. I have one column with value 00:03:15 and another column with 00:00:00 I want to sum these columns and want to show me average value from first and second column. I used this expression ( formula ) in my case : 

=Format(
    TimeSerial(0,0, 
        Round(iif(sum(Fields!N_INBOUND.Value)=0,0,
        sum(Fields!T_INBOUND.Value/Fields!N_INBOUND.Value))

    +

    iif(sum(Fields!N_INBOUND.Value)=0,0,
    sum(Fields!T_HOLD.Value/Fields!N_INBOUND.Value)

    +iif(first(Fields!HANDLING_TIME_MEASURE_TYPE.Value)=2,
    sum(Fields!N_INBOUND.Value-Fields!N_TRANSFERS_TAKEN.Value),0))

    +iif(sum(Fields!N_INBOUND.Value)=0,0,
    sum(Fields!T_CONSULT.Value/Fields!N_INBOUND.Value)

    +iif(first(Fields!HANDLING_TIME_MEASURE_TYPE.Value)=2,
    sum(Fields!N_CONSULT.Value-Fields!N_TRANSFERS_TAKEN.Value),0)))/count(Fields!PRESENTATION_NAME.Value))
, "HH:mm:ss")

I used this https://sqldusty.com/2011/08/01/ssrs-expression-iif-statement-divide-by-zero-error/ and looks like : 

=Format(
    TimeSerial(0,0, 
        Round(      
        iif(count(Fields!PRESENTATION_NAME.Value)=0,0,      
        (iif(sum(Fields!N_INBOUND.Value)=0,0,
        sum(Fields!T_INBOUND.Value/Fields!N_INBOUND.Value))

    +

    iif(sum(Fields!N_INBOUND.Value)=0,0,
    sum(Fields!T_HOLD.Value/Fields!N_INBOUND.Value)

    +iif(first(Fields!HANDLING_TIME_MEASURE_TYPE.Value)=2,
    sum(Fields!N_INBOUND.Value-Fields!N_TRANSFERS_TAKEN.Value),0))

    +iif(sum(Fields!N_INBOUND.Value)=0,0,
    sum(Fields!T_CONSULT.Value/Fields!N_INBOUND.Value)

    +iif(first(Fields!HANDLING_TIME_MEASURE_TYPE.Value)=2,
    sum(Fields!N_CONSULT.Value-Fields!N_TRANSFERS_TAKEN.Value),0)))/iif(count(Fields!PRESENTATION_NAME.Value)=0,1,count(Fields!PRESENTATION_NAME.Value)) ) ) )
, "HH:mm:ss")

But shows me an ERROR.Anyone who know how to fix that?

Solution

  • Your data is something like below, Correct me if I am wrong. At the Bottom I have added Total for both columns.

    Note: You will have to take care of what type your column (T_INBOUND and N_INBOUND) return values. enter image description here

    Now to create a Total for T_INBOUND and N_INBOUND

    To add totals for a column group In the tablix data region row group area, right-click a cell in the column group area for which you want totals, then point to Add Total, and click Before or After.

    A new column outside the current group is added to the data region, and then a default total is added for each numeric field in the column. Link: https://learn.microsoft.com/en-us/sql/reporting-services/report-design/add-a-total-to-a-group-or-tablix-data-region-report-builder-and-ssrs?view=sql-server-2017

    Once you have Total you can create create a Cell Either next to your Total or below Total and add expression as below

        IIF(IsNothing(Fields!Total_N_BOUND.Value) OR Fields!Total_N_BOUND.Value=0,0,
Fields!Total_T_BOUND.Value/Fields!Total_N_BOUND.Value)

    enter image description here