John is hired by a company called Acme, Inc. as a consultant to design data forms and implement data validation rules that enforce some of the company policies. He is asked to implement a validation rule that flags Actual amounts in red if the Total Cost in actuals exceeds the budgeted amount. This test must be repeated for each year and time period in the application. John designs the data form and adds a data validation rule at cell level using a cross-dimension member, as shown in the following figures.
Data Form Layout at Design Time:
Data Validation Rule at Design Time:
Data Form at Data Entry Time with Data Validations Applied:
John can split Total Cost into its own segment and apply the data validation rule at that segment for a slight performance gain. However, this would increase maintenance as new accounts and scenarios were added to the data form.
If the requirements changed such that only the YearTotal Period in Actual had to be flagged in red, John would have two options. The best option would be to add an IF entry to check if the Period member is YearTotal. Another option would be to split the YearTotal member into a separate column for better performance. However, this would break the spreading logic, the column header for Year would repeat, and the data form would be harder to maintain as new years were added.
For additional scenarios, see Data Validation Rule Scenarios.