Search code examples
sqlsql-serverreporting-servicesreportbuilder3.0reportbuilder

Using Round/Ceiling/Floor function with IIF in SSRS expression

In my, SSRS report the values for Success and Fail fields are supposed to together add up to 100. But as seen below the sum of the Success and Fail fields is falling short of 100 due to some round-off error. IMAGE

I have used this expression to show the result for Success.

    =IIF(Fields!CountNearlyAlwaysOnSuccess.Value +Fields!CountNearlyAlwaysOnFail.Value =0, 
    100,Fields!NearlyAlways_On_SLO_Success.Value) & "%," & Fields!CountNearlyAlwaysOnSuccess.Value

The expression for Fail is below.

    =(Fields!NearlyAlways_On_SLO_Fail.Value) & "%," & Fields!CountNearlyAlwaysOnFail.Value

I have tried the following approaches to no avail.

  1. From text box properties, try to set the value of percentage decimal points to 2.

  2. Adding the Round/Ceiling/Floor function to the value of Fields!NearlyAlways_On_SLO_Success.Value.

Any help is truly appreciated.

Solution

  • Because Success and Fail must necessarily partition the data, try displaying the FAIL percent as the remainder from the SUCCESS percent

    = IIF(Fields!CountNearlyAlwaysOnSuccess.Value 
   + Fields!CountNearlyAlwaysOnFail.Value = 0
   , 0, 100 - Fields!NearlyAlways_On_SLO_Success.Value
  ) & "%," & Fields!CountNearlyAlwaysOnFail.Value

    If that doesn't work, you can try adjusting how you calculate NearlyAlways_On_SLO_Fail, but try this first and report back

    EDIT: Responding to comment from @OrcaZep I'm adding a more complex formula that calculates the percentages from the COUNT values so we have control of rounding in SSRS instead of SQL.

    Note that this way we have to provide updated formulas for both SUCCESS and FAIL, otherwise they might not total 100. And the formula for SUCCESS must be reproduced inside the formula for FAIL, we can't rely on the passed in calculations other than COUNT.

    Formula for SUCCESS (locally calculated with CEILING)

    = IIF(Fields!CountNearlyAlwaysOnSuccess.Value 
      + Fields!CountNearlyAlwaysOnFail.Value = 0
   , 100
   , Ceiling(100*Fields!CountNearlyAlwaysOnSuccess.Value / (Fields!CountNearlyAlwaysOnSuccess.Value 
      + Fields!CountNearlyAlwaysOnFail.Value))
   )

    Formula for FAIL (Calculated from local SUCCESS)

    = IIF(Fields!CountNearlyAlwaysOnSuccess.Value 
      + Fields!CountNearlyAlwaysOnFail.Value = 0
   , 0
   , 100 - Ceiling(100 * Fields!CountNearlyAlwaysOnSuccess.Value / (Fields!CountNearlyAlwaysOnSuccess.Value 
      + Fields!CountNearlyAlwaysOnFail.Value))
   )

    Here's the raw data for my test

    with cteTest as (
   SELECT * FROM (VALUES ('T01',7,8)
      , ('T02',8, 7), ('T03',0, 0)
      , ('T04',51, 50), ('T05',50, 51)
   ) as Smpl(RowName, CountNearlyAlwaysOnSuccess
      , CountNearlyAlwaysOnFail)
) SELECT * FROM cteTest

    The output looks like this. Note that I increased the decimal precision to show it was discarding the decimal part of the percentage, this works equally well with decimal precision of 0 decimal places.

    Name Success Fail RawSuccess RawFail CalcSuccess CalcFail
    T01 7 8 0.4667 0.5333 47.00 53.00
    T02 8 7 0.5333 0.4667 54.00 46.00
    T03 0 0 #DIV/0! #DIV/0! 100.00 0.00
    T04 51 50 0.5050 0.4950 51.00 49.00
    T05 50 51 0.4950 0.5050 50.00 50.00