Data Validations within Cube View
I've been creating cube views and have sometimes found that I may forget to update a row that is part of a reporting segment. This results in the sum of the rows not equaling the total row for the re...
Hi CCI01: this can be accomplished with a calculation and simple row suppression.
- Use a GetDataCell(CVR(Row3) - CVR(Row1) - CVR(Row2)) - which should calculate to 0 if they match
- Within the Data tab of this calculated row, turn on Zero Suppression - that way it will only show if the rows dont' match
- Consider adding formatting to the calculated row so its clearly visible when things dont match as expected
That said, a few additional thoughts:
- Could this check be put into a confirmation rule as part of a workflow? Similar to the classic, 'the balance sheet must balance else we can't close the books.' That might be better from a process standpoint, if applicable.
- Are these related members as part of a standard hierarchy? If so, consider using a member expansion combined with zero suppression. In this way any accounts that are added to the hierarchy are automatically added to your report eliminating the issue of forgetting to update a report. The zero suppression will help keep your report nice and condensed for readability.
Cheers, -DB
