Use a Report Object Variable to Apply Validation Rules in an Excel Object

A report in CDM can contain various data from various sources (OLAP, relational, or an external data source). You can use Excel formulas and object variables to compare different data sources, to determine if the values are what you expect.

In the following example, the same data has been imported into the Excel object from two different sources. One source provided summary level data (Current Assets) and the other provided line item breakdown of the data (Note). The validation compares both sources to determine if the total for Cash is the same. This example creates a validation that uses the rule variable (##R).

To apply validation rules in an Excel object using report object variables:

1. Open the report that you want to work with.
2. Open and check out the Excel object to which you want to apply validation rules.
3. Click the Section View Mode icon in the status bar.
4. Find the data worksheet. The data worksheet is originally named Sheet1, but you can rename it if necessary. Create source object variables (##S) that tag the values you want to validate, as shown in the following table.

   Table 1: Source Object Variables

   Code	Reference	Value
   ##S	CashCrtYer	1,066,510
   ##S	CashCrtYer_1	2,163,539
   ##S	CashCrtYer_2	853,208

5. Save your changes in the source Excel object.
6. Open the Excel object that contains the comparative data you want to use in the validation.
7. In the data worksheet, create destination object variables (##D) to call the source data into the worksheet.
8. In the same data worksheet, ensure that the data is in balance, as shown in the following table.

   Table 2: Destination Object Variables for Validation

   Cash Rule
   Code	Name	Assets	Note	Total
   ##D	CashCrtYer	1,066,510	1,066,510	0
   ##D	CashCrtYer_1	2,163,539	2,136,539	0
   ##D	CashCrtYer_2	853,208	853,208	0

   • Use Excel formulas to subtract Assets from Note.
   • A non-zero balance is displayed in the Total column.
9. In the same data worksheet, create a rule object variable (##R) that tags the result of the validation for Cash.

   Table 3: Rule Object Variables

   Cash rule
   Code	Name	Assets	Note	Total
   ##D	CashCrtYer	1,066,510	1,066,510	0
   ##D	CashCrtYer_1	2,163,539	2,136,539	0
   ##D	CashCrtYer_2	853,208	853,208	0
   		##R	Cash	0

   Set up the source variable in three columns:

   1. The first column with the ##R variable as an identifier.
   2. The second column with a unique variable name for reference (Cash).
   3. The third column with the value for the variable, which was calculated by the formulas used to validate the data.
10. Save your changes in the destination Excel object.
11. Click the Default View Mode icon in the status bar.
12. Save your changes to the report and check in the Excel object.

Report validation can occur in these ways:

• If you want to check validation and generate the report at the same time, the validation process is automatic. Every time you generate a report that contains at least one ##R variable, the report validation process runs automatically. If at least one ##R variable exists in the report, the following events occur:
  • All data refreshes.
  • All Excel validation formulas are resolved.
  • A validation report is generated.
  • An external Microsoft™ Excel file is generated, containing the validation report. This Excel file contains the status (pass or fail) of all report objects that contain a ##R variable. The Excel file also contains other report, date, and time information.
  • If the report contains objects marked as exhibits or supporting documents, the exhibits and supporting documents generate separately along with the validation report and the external Microsoft Excel file.
• If you want to check validation without generating the report, you can use the Quick Report Validation or Full Report Validation command on the ribbon. The quick validation is made against the cache without doing a cache refresh. The latest changes in the report are validated. A full validation refreshes the cache before validation. If at least one ##R variable exists in the report, the following events occur:
  • All data refreshes.
  • All Excel validation formulas are resolved.
  • An external Microsoft Excel file is generated, containing the validation report. This Excel file contains the status (pass or fail) of all report objects that contain a ##R variable. The Excel file also contains other report, date, and time information.

Important: If there are no ##R variables in the report, the validation process does not run in either case.

Validation is successful if the cell display value:

• Has a value of 0.
• Has a value of 0 but the actual value is different (for example, 0.023) and the cell is formatted as Number, Currency, or Accounting, with zero decimal places.

Validation will fail if the cell display value:

• Has a value other than 0 (for example, 1).
• Has a value other than 0 (for example, 0.023) but due to the cell width it is displayed as 0.
• Is formatted as text.
• Is not displayed as 0 (for example, 0.023) and is formatted as accounting.
• Has a value with decimals (for example, 0.00) and an actual value of 0 and the cell is formatted as Number or Currency, with two decimal places.