Validation Rule on Detailed-By Data Entry Not Matching Total from Another Cube

Question

Hi everyone,

I’m facing an issue with a validation rule on a data entry that uses a detailed by dimension.

I would like to create a validation rule that checks whether the total of this detailed-by data entry is equal to a value coming from another cube within the same layout.

I tried adding the cube directly in the form without detailed by , but in that case the cube does not update at the same time as the data entry, so the validation does not work.

Has anyone found a way to validate totals coming from a detailed-by entry against another cube?

Thanks for your help!

Cube 1 : Amount

Cube2 : 2026

Cube2 : 2027

Cube2 : 2028

result of validation rules

100

30

70

ok

90

60

10

ko

110

90

10

20

ko

Robert Barkaway · Accepted Answer

Hi Sophie, the problem you'll have there is that cell validation is performed on a cell by cell basis, and you're looking for row validation, so the validation would reject values entered into individual cells because the total wouldn't be correct until all  cells had been entered.

What you can do if the cells already total the correct amount and the user is just amending  the values, is to lock the total using the Totals Locked by Block option under Data Entry, that will mean that if the user changes one cell value, the other cell values will automatically update to keep the total the same.

Alternatively you'd probably need to use a custom save procedure to decide whether to commit the changes or roll them back (the save procedure can flow the existing values of the cube into a temp cube before saving data entry and flow them back into the physical cube if it's determined that the newly entered values are invalid).