Forum Discussion

Jacky_C's avatar
Jacky_C
New Contributor III
8 months ago

Rounding function in GetDataCell

Hi all,

Currently I have a cube view with GetDataCell formula for ad hoc calculation, may I know is there any rounding off function in the GetDataCell formula? For example, we would like to have a formula like Round(A#100) - Round(A#200). Thanks a lot for any possible solutions.

Cube View
  • KarlT's avatar
    KarlT
    8 months ago

    I don't know a way to achieve this within the GetDataCell but the result could be achieved like this:

    Dim dataCell1 As DataCell = api.Data.GetDataCell("A#Account1")
Dim dataCell2 As DataCell = api.Data.GetDataCell("A#Account2")
Dim result As Decimal = Math.Round(dataCell1.CellAmount,0) - Math.Round(dataCell2.CellAmount,0)
Return result

    This requires the creation of a dynamic calc member however

  • Jacky_C's avatar
    Jacky_C
    8 months ago

    Hi KarlT, thanks for your suggestion on creating a dynamic calc member. I will try to create one if there is no other way to apply rounding function within the GetDataCell formula. Thank you so much.

  • KarlT's avatar
    KarlT
    Contributor III
    8 months ago

    I don't know a way to achieve this within the GetDataCell but the result could be achieved like this:

    Dim dataCell1 As DataCell = api.Data.GetDataCell("A#Account1")
Dim dataCell2 As DataCell = api.Data.GetDataCell("A#Account2")
Dim result As Decimal = Math.Round(dataCell1.CellAmount,0) - Math.Round(dataCell2.CellAmount,0)
Return result

    This requires the creation of a dynamic calc member however

    • Jacky_C's avatar
      Jacky_C
      New Contributor III
      8 months ago

      Hi KarlT, thanks for your suggestion on creating a dynamic calc member. I will try to create one if there is no other way to apply rounding function within the GetDataCell formula. Thank you so much.

  • rachael_thorley's avatar
    rachael_thorley
    New Contributor II
    8 months ago

    Hi Jacky, in our Cube Views we use the Cell Format option to apply number formatting to round balances, including calculations. However this is not within the calculation itself so may not be exactly what you're looking to do. The values display on the cube view rounded.

    Outcome: 

     

    • Jacky_C's avatar
      Jacky_C
      New Contributor III
      8 months ago

      Hi Rachael, thanks for your reply. I think the number format only affects the numbers shown in the cube view, it won't round off the numbers in the calculation. But thanks for the suggestion, I will take a note on the specific number format as shown in your example.