Data Validations within Cube View

[CCI01]

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

[db_pdx · Accepted solution]

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

[ChristianW]

You can use a confirmation rule in a Form workflow, it forces people to validate the input before they can complete the workflow. If you're looking for an immediate reaction, you can use the save data event handler to prevent the form from saving the input (if it's not fulfilling the requirements).