While working on Script logic in BusinessObjects Planning and Consolidation, at times we might want to perform conditional execution. Say for example, we might want to compare two accounts A and B, post the difference to another account C only when A is greater than B. In such scenarios, instead of writing a custom logic we can use the Validation Rules. These business rules are normally used to check the integrity of accounts, example - to verify if total assets equal to total liabilities and equities in the balance sheet.
Let me now illustrate how these validation rules can be used for conditional execution with an example.
Case
We want to compare the Recoverable Amount and Book Value of an asset, post the difference to Impairments only when Recoverable amount is less than the Book Value. No value should be posted to impairments account in other conditions.
When Recoverable Amount < Book Value
Impairments = Book Value – Recoverable Amount
End When
For this we have to do the following steps:
1) Setup the Subtable type dimension i.e. Flow dimension and add it to the application.
2) Modify the application to contain Validation Rules.
Select your application > Modify Application > Change Application Type > Modify Application > Select the check box for Validation rules > Modify Application.
After this you can see that two tables – “Validation rules” and “Validation rules detail” are added under Business Rules in your application.
The above two steps are generic for using Validation rules in the application. Now coming to the case considered, we need to configure the business rules tables.
3) The table Validation rules is configured as:
Here we specify the fields as follows:
| Field | Value | Description |
| Validation account | I_Impairments | Destination Account |
| Remark | Conditional Execution | A description of the rule |
| Validation operand | > | We can specify the operands : =, <</strong>, >, >=,<=This depends on our requirement |
| Other source dimensions | Blank | A filter criterion for the original data extraction. Used to limit the selection on one dimension. |
| Other destination dimensions | Blank | A forced destination. Used to force a target destination’s dimension. |
| Applicable periods | DEC | Blank or one or more time periods. |
| Validation tolerance | Blank | Used to determine a limitation in the Value. |
4) The table “Validation rules details” is configured as:
Here we specify the fields as follows:
| Field | Value | Description |
| Validation account | I_Impairments | Destination Account |
| Account 1 | I_RecoverableAmt | The member ID of the “left side” account. |
| Flow 1 | CloseBal | Blank or the “left side” member of the FLOW dimension. |
| Sign 1 | 1 | The operator used in the Left part of calculation ( + or - ) |
| Account 2 | I_BookVal | The member ID of the “right side” account. |
| Flow 2 | CloseBal | Blank or the “right side” member of the FLOW dimension |
| Sign 2 | -1 | The operator used in the Right part of calculation ( + or - ) |
| Remark | Conditional Execution | A description for the rule. |
5) Write the following to Default logic in your application (Finance here).
//========================================================
//Logic to call Stored Procedure
//========================================================
*RUN_STORED_PROCEDURE=SPRUNVALID(‘FINANCE’,’ACTUAL’,’USD’,’%SCOPETABLE%’,’%LOGTABLE%’)
*COMMIT
The above logic executes the business rules. It takes the syntax:
RUN_STORED_PROCEDURE=SPRUNVALID(‘application’,’category’,’currency’,’%SCOPETABLE%’,’%LOGTABLE%’)
*COMMIT
6) Now execute the “Default Formulas” package and check the result.
7) The result can be verified from a report. It can be seen that value is posted to Impairments only when recoverable amount is less than book value. In the report shown, we have different asset categories.
Thus we can use validation rules for conditional execution. It can be configured to suit our requirements. This not only simplifies our task but will also help us improve the performance as we are using table based logic instead of custom script logic.
Hope this blog helps you perform conditional execution using Validation rules.
1 Comment
