Forum Discussion

Irina's avatar
Irina
New Contributor III
3 months ago
Solved

Rounding in cube views

Hi there, We need to see cube view figures which are rounded to an integer. Could you please advise how to round figures in cube view?  
Cube View
  • Paulo_Thrige's avatar
    Paulo_Thrige
    2 months ago

    This is a functionality that a lot of organizations are looking for, as the reconciliation process of rounding numbers in related tables in external reports takes up a lot of manual resources or complex coding. I've seen a solution some years ago in a competing product, that could do the calculations and stored it as a separate layer in a UD. As I understand, it's algebra math and settling where to put the rounding discrepancies so it sill makes sense.
    I think it would be a great business opportunity for the consulting community.

Irina's avatar
Irina
New Contributor III
3 months ago

Joakim, 

Thank you! I see now that visually in cube view figures are rounded to the nearest whole number. However, when I open cube view in Excel, I see in cells that figures contain decimals. Could you please advise is it possible to round these figures to the full numbers in Excel (as if we apply to each figure ROUND(,0) function)?

  • JoakimK's avatar
    JoakimK
    Contributor
    3 months ago

    Irina,

    In the same fashion, in the format section of the row/column, change the ExcelNumberFormat to be what you want. In my example it could for example be set to this: "ExcelNumberFormat = [#,##0]"

    Regards, Joakim

  • rhankey's avatar
    rhankey
    Contributor
    3 months ago

    Be aware that presentation layer (such as in a Cube View) "rounding" using formatting can result in numbers that do not appear to add up.  For example, consider two children each containing 1.4 resulting in a parent of 2.8.  When rounded at the presentation layer, you will have 1 + 1 = 3.  That seldom makes for a happy accountant.  If they are ok with that, then great, as this is certainly the easiest way to get the job done.

    If you need the rounded numbers to add up and match across different reports regardless of reporting dimensionality, then you need to perform the rounding with stored calculations in the cube and potentially provide the user a facility to enter rounding adjustments - depending on whether they want to see 1 + 1 = 2, 1 + 2 = 3 or 2 + 1 = 3 (using the above example).

    • Paulo_Thrige's avatar
      Paulo_Thrige
      New Contributor III
      2 months ago

      This is a functionality that a lot of organizations are looking for, as the reconciliation process of rounding numbers in related tables in external reports takes up a lot of manual resources or complex coding. I've seen a solution some years ago in a competing product, that could do the calculations and stored it as a separate layer in a UD. As I understand, it's algebra math and settling where to put the rounding discrepancies so it sill makes sense.
      I think it would be a great business opportunity for the consulting community.