Planning

Get Involved. Join the Conversation.

Topic

    Brian Dunnells
    Rounding without Truncating in Financial Reporting Studio...
    Topic posted August 20, 2019 by Brian DunnellsGreen Ribbon: 100+ Points, tagged Financial Planning, Reports 
    47 Views, 9 Comments
    Title:
    Rounding without Truncating in Financial Reporting Studio (FRS)
    Summary:
    How can we scale values without truncating them when running FR reports in Excel?
    Content:

    We have multiple reports that are reported in millions with one decimal place. From a presentation standpoint this is fine, but when users run these reports in excel and attempt to calculate the displayed ratios using the scaled data they are slightly off (due to truncation vs. rounding). Is there any way to build an FR report that only displays one decimal but still contains the full value to support this desired functionality?

     

    Thanks,

     

    Brian

    Version:
    August Release

    Comment

     

    • Mark Rinaldi

      Are you using the "Scale Value By" option in the Grid formatting?  See the Formatting Numbers in Grids section of the FR Web Studio Designer's Guide for more information.

      • Brian Dunnells

        We are, and that's what causes the issue. When you select "Scale Value By" and select "Millions" for example, it truncates the value regardless of whether it's run in PDF, HTML, or Excel. We are trying to replicate standard Excel functionality where you can type in a value, set the decimal to 1, but when you click in the cell you still see the full value.

        For example, if a value is scaled to millions in a report using FRS displaying 1 decimal place, 1,523,500 becomes 1.5. If you run this report in Excel and click on the cell, it still shows just 1.5. However, if you were to open an Excel workbook and just type in 1,523,500 and scale to one decimal place it would display as 1.5 but clicking on the cell would show the full value of 1.523500. The truncation that occurs with the "Scale Value By" function in FRS is what is causing our issue but I don't know any way around it.

         

        Thanks,

        Brian

        • Mark Rinaldi

          Does Management Reporting handle it as you would expect (I'm on vacation, so trying not to dig too deeply and test for myself)? 

          As a possible workaround, have you considered a Dynamically Calculated Currency that scales for you so the 1,523,500 displays as 1.5 but has the full 1.5235 value?

          • Brian Dunnells

            Mark,

            I definitely appreciate the response on vacation! 

            I don't believe we have Management Reporting, unless that's something we can access through EPBCS now, so I don't have a way to test that either.

            I've done a similar calculation to the one you're suggesting in the past, but I believe you still run into the same issue when you set the decimal place to 1 in the report. It will correctly display the one decimal place, but then truncates everything else

            The only sure solution I know is to build the report to display multiple decimals, then run it in Excel and manually edit the cells to only display one decimal. That will maintain the whole value but only display the one decimal. However, it's not a remotely ideal situation, especially for 60+ reports.

    • Dave Roberts

      Hi Brian,

      FR exports the scaled value to Excel, as its displayed in the FR report, without the unscaled extra decimal places as the underlying value.  The scaled values should be rounded and not truncated, please see the attached screenshot (2nd row scaled by thousands with one decimal).  I don't know of any workarounds other than exporting using the query ready format, which is unformatted and adding the Excel calcs from there.

      Regards,

      Dave

      • Brian Dunnells

        Thanks Dave, that confirms what I have seen as well. Perhaps my use of the word "truncated" is a bit misleading. FR definitely does round as opposed to truncating to the decimal, it just loses the additional values that follow it once it's rounded as you've suggested. 

      • Mark Rinaldi

        Yeah, what he said!  ;-)