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 reporting segment.
Does anyone have any suggestions on how I can create a row in the cube view that validates that the rows of a reporting segment equal the total row? I would want this row to be invisible and to have a condition that it would only display if there was a variance.
I'd be looking for something like this:
Row 1: $100
Row 2: $100
Row 3: Total = 200
Variance = 0
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
