Rounding numbers in Flex Reports - Troubleshooting

Expand / Collapse
 

Rounding numbers in Flex Reports - Troubleshooting


Article ID: 52054 - Last Review: October 28, 2013

INFORMATION

When comparing the data from packaged reports and Flex Reports, you may notice that in some cases averages will not be the same. This is due to the fact that SQL Server, Excel, and .NET all round decimals differently.  In your Flex Reporting, if you use functions such as Floor and Round you can control how the system performs these rounding actions.  As a general rule, Floor will round down to the nearest whole number. (eg: 9.82756 would round to 9.0)

NOTE: Excel expressions, and Flex Reporting expressions are the same.  If you are comfortable working in excel, then the Flex Reporting expressions will be easily grasped.



TROUBLESHOOTING

In order to view how the system will round using different functions, you can create a custom report in Flex Reporting.  Determine which stat you need to test the rounding for, and adjust the below example as needed.

1. Open the base report to be modified in Flex Reporting.
2. Remove as many columns as possible to make it easier to read.
3. Save as ­ Change to something unique.
4. Add the following columns to the report (adjusting the value to be tested as needed): 

­    Queue Total Time To Answer in seconds
Label the column as Time To Answer in Seconds.  Selecting the stat from the options will put this formula in the cell:
=Code.FormatTimeSpanAsHHMMSS(Fields!QueueTimeToAnswer.Value)
Change it to:
=Fields!QueueTimeToAnswer.Value
The total line should be added to look like this:
=Sum(Fields!QueueTimeToAnswer.Value)

     Queue Time To Answer average in seconds
Label the column as Average Time To Answer in Seconds.  Selecting the stat from the options will put this formula in the cell:
=Code.FormatTimeSpanAsHHMMSS(IIF(Fields!QueueAnswered.Value = 0, 0, Fields!QueueTimeToAnswer.Value/Fields!QueueAnswered.Value))
Change it to:
=IIF(Fields!QueueAnswered.Value = 0, 0, Fields!QueueTimeToAnswer.Value/Fields!QueueAnswered.Value)
The total line should look like this:
=IIF(Sum(Fields!QueueAnswered.Value) = 0, 0, Sum(Fields!QueueTimeToAnswer.Value)/Sum(Fields!QueueAnswered.Value))

­    Floor
Label the column as Floor.  This column will show you how the engine with round the Average Time To Answer when it's a float:
=IIF(Fields!QueueAnswered.Value = 0, 0, Floor(Fields!QueueTimeToAnswer.Value/Fields!QueueAnswered.Value))
The total line should look like this:
=IIF(Sum(Fields!QueueAnswered.Value) = 0, 0, Floor(Sum(Fields!QueueTimeToAnswer.Value)/Sum(Fields!QueueAnswered.Value)))

    Floor as time
Label the column as Floor as time.  This column will show you how the engine with round the Average Time To Answer when it's a float:
=Code.FormatTimeSpanAsHHMMSS(IIF(Fields!QueueAnswered.Value = 0, 0, Floor(Fields!QueueTimeToAnswer.Value/Fields!QueueAnswered.Value)))
The total line should look like this:
=Code.FormatTimeSpanAsHHMMSS(IIF(Sum(Fields!QueueAnswered.Value) = 0, 0, Floor(Sum(Fields!QueueTimeToAnswer.Value)/Sum(Fields!QueueAnswered.Value)))

    Round
Label the column as Round.  This column will show you how the engine with round the Average Time To Answer when its a float:
=IIF(Fields!QueueAnswered.Value = 0, 0, Round(Fields!QueueTimeToAnswer.Value/Fields!QueueAnswered.Value))
The total line should look like this:
=IIF(Sum(Fields!QueueAnswered.Value) = 0, 0, Round(Sum(Fields!QueueTimeToAnswer.Value)/Sum(Fields!QueueAnswered.Value)))

    ­Round as Time
Label the column as Round as Time.  This column will show you how the engine with round the Average Time To Answer when it's a float:
=Code.FormatTimeSpanAsHHMMSS(IIF(Fields!QueueAnswered.Value = 0, 0, Round(Fields!QueueTimeToAnswer.Value/Fields!QueueAnswered.Value)))
The total line should look like this:
=Code.FormatTimeSpanAsHHMMSS(IIF(Sum(Fields!QueueAnswered.Value) = 0, 0, Round(Sum(Fields!QueueTimeToAnswer.Value)/Sum(Fields!QueueAnswered.Value))))


5. Understanding the report:
    a. Run the report in the preview tab.
    b. In the above examples, the Total line Average Time To Answer is the data in question.  For this reason Advance to the last page of the report and look at the total line.
    c. See the Average Time To Answer in Seconds in the total line it 90.8786822933164.
    d. Looking at how the Floor and Round column treated the value you can see the FLOOR rounds down and Round rounds up (which it might/might not do if the number is exactly 90.5).
    e. At this point you need to decide how they would treat 90.8786822933164. If they would consider this closer to 91 then they should wrap their expression in Round. If you consider this as 90 (it has not really reached 91 yet) then use Floor.


APPLIES TO

CCM, and Flex Reporting 

Keywords: round rounding average howto



Rate this Article:
     

Add Your Comments


Name: *
Email Address:
Web Address:
Verification Code:
*
 

Details
Last Modified:Monday, October 28, 2013
Last Modified By: andrew_montpetit
Type: HOWTO
Rated 4 stars based on 1 vote
Article has been viewed 9,599 times.
Options