Rounding function in GetDataCell

[Jacky_C]

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.

[KarlT · Accepted solution]

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

[KarlT · Accepted solution]

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 · Accepted solution]

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]

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]

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.