Creating an Aggregation Report For Shared Objects

This procedure creates a sample report that counts the number of unique Ineffective and Effective controls under a business entity.

About this task

Figure 1 shows the association between Entity, Process, Risk, and Control objects in this scenario. Because child Control objects can have multiple parent Risk objects, it is important that the value of a control be counted only once in the report.

Flowchart showing the relationship between entities, processes, risks, and controls — Figure 1. Control Parent-Child Associations

Procedure

Create a new list report.
Create a child query:
1. From the Query Explorer, open the query.
2. In the Properties pane under Miscellaneous, set the name to listMain.
3. Return to the Query Explorer and select the top level folder Queries.
4. Click the Toolbox tab .
5. Drag a Query object next to the listMain query.
6. Select the new query object.
7. In the Properties pane under Miscellaneous, set the Name to listMain1.
To add another child query object to the query object you created in the previous step, complete the following steps.
1. Drag a Query object onto the listMain1 query.
2. Select the new query object.
3. In the Properties pane under Miscellaneous, set the Name to listMain2.
To add query items to the listMain2 query you created in the previous step, complete the following steps.
1. Select the listMain2 query to open it.
2. Click the Source tab in the Insertable Objects pane.
3. Drag the following query items into the Data Items box:
  - DEFAULT|DEFAULT_REL|GRC_OBJECTS|SOXBUSENTITY_FOLDER| [SOXBUSENTITY_GPC]| [SOXBUSENTITY_GPC]|[PEN_NAME00]
  - DEFAULT|DEFAULT_REL|GRC_OBJECTS|SOXBUSENTITY_FOLDER| [SOXBUSENTITY_GPC]| [SOXBUSENTITY_GPC]|ID_FIELDS|[PEN_ENTITY_ID]
  - DEFAULT|DEFAULT_REL|GRC_OBJECTS|SOXBUSENTITY_FOLDER| [SOXBUSENTITY_GPC]| [SOXBUSENTITY_GPC]|ID_FIELDS| [GEN_REPORTING_PERIOD_ID]
  - DEFAULT|DEFAULT_REL|GRC_OBJECTS|SOXCONTROL_FOLDER|[SOXCONTROL]| ID_FIELDS| [CN_CONTROL_ID]
  - DEFAULT|DEFAULT_REL|GRC_OBJECTS|SOXCONTROL_FOLDER|[SOXCONTROL] | ENUMERATION_FIELDS| OPERATING_EFFECTIVENES (ENUMERATION)| [CN_OPERATING_EFFECTIVENES_ID]
Add filters to the report:
1. Select the [GEN_REPORTING_PERIOD_ID] data item you just added and drag it to the Detail Filters pane.
2. In the Expression Definition pane, define the filter expression:
```
[GEN_REPORTING_PERIOD_ID] = -1 
```
3. Click OK.
4. Select the [CN_CONTROL_ID] data item you just added and drag it to the Detail Filters pane.
5. In the Expression Definition pane, define the filter expression:
```
[CN_CONTROL_ID] is not null
```
6. Click the Validate icon to validate the expression and then click OK.
To add a data item to the query object, complete the following steps.
1. Click the Toolbox tab .
2. Drag a Data Item object into the Data Items box of the query.
3. In the Expression Definition pane of the data item, enter the following expression:
```
IF([CN_OPERATING_EFFECTIVENES_ID] = 
   #$SOXCONTROL_OPERATING_EFFECTIVENES_DEFINITION_MAP
      {"Ineffective"}#)THEN ([CN_CONTROL_ID])ELSE (NULL)
```
4. Click the Validate icon to validate the expression and then click OK.
In the Properties pane of the data item, complete the following steps.
1. Change the Name property of the data item to Ineffective Control.
2. Change the Aggregate Function property to None.
To view the tabular data, click Run > View Tabular Data.
Open the listMain1 query subject in the Query Explorer. To count the number of distinct controls for each entity, complete the following steps.
1. Click the Source tab and drag and drop the [PEN_NAME00] query item into the Data Items pane.
2. In the Properties pane under Data Item, set the Aggregate Function to None.
3. Click the Toolbox tab and drag a Data Item object into the Data Items pane.
4. In the Expression Definition box, enter the following expression to count the total number of distinct controls:
```
count(distinct [listMain2].[CN_CONTROL_ID] for [listMain2].[PEN_NAME00])
```
5. Click the Validate icon to validate the expression and then click OK.
In the Properties pane for the data item, complete the following steps.
1. Under Data Item set the Name property to Count of Controls.
2. Set the Aggregate Function property to None.
3. Set the Rollup Aggregate Function property to None.
To count the number of ineffective controls for each entity, complete the following steps.
1. Click the Toolbox tab and drag a Data Item object into the Data Items pane.
2. In the Expression Definition box, enter the following expression to count the total number of distinct controls:
```
count(distinct [listMain2].[Ineffective Control] for 
 [listMain2].[PEN_NAME00])
```
3. Click the Validate icon to validate the expression and then click OK.
In the Properties pane for the data item, complete the following steps.
1. Under Data Item set the Name property to Count of Ineffective Controls.
2. Set the Aggregate Function property to None.
3. Set the Rollup Aggregate Function property to None.
Run the tabular model at this level.
Open the listMain query subject in the Query Explorer and complete the following steps.
1. Drag and drop the following query items into the Data Items pane:
  - [PEN_NAME00]
  - [Count of Controls]
  - [Count of Ineffective Controls]
2. In the Properties pane for each of the previous controls, set the Aggregate Function to None.
From the Page Explorer, navigate to Page1 and complete the following steps.
1. Click the Data Items tab.
2. Drag the following objects from the listMain query into the list:
  - [PEN_NAME00]
  - [Count of Controls]
  - [Count of Ineffective Controls]
Add a business entity prompt as described in Adding a Business Entity Prompt.
Run the report.

Results

In this example, there were four risks that shared two controls. If this method was not used to compute the count of controls, the value would have returned incorrectly as 4.