Adding Microcharts

A microchart is a miniature version of a chart that can be inserted in a crosstab or a list. They operate in the same manner that a master-detail report functions in that the microchart is a detailed view of the master data.

Procedure

  1. Create a new list report.
  2. Add a business entity prompt as described in Adding a Business Entity Prompt. Use GEN_ENTITY_ID as the field for the source of the data. Your crosstab query should have the following filter after creating the prompt: 
    
[DEFAULT_REL].[SOXBUSENTITY_GPC].[GEN_ENTITY_ID] = ?Entity ID?
  3. From the Insertable Objects pane, drag and drop the following data items into the list (remember that what is loaded in your system will be different from what you see here):
    • DEFAULT|[DEFAULT_REL]|GRC_OBJECTS|SOXBUSENTITY_FOLDER| [SOXBUSENTITY_GPC]|[PEN_NAME00]
    • DEFAULT|[DEFAULT_REL]|GRC_OBJECTS|SOXBUSENTITY_FOLDER| [SOXBUSENTITY_GPC]|ID_FIELDS| [PEN_ENTITY_ID]
    • DEFAULT|[DEFAULT_REL]|GRC_OBJECTS|[SOXCONTROL] ENUMERATION_FIELDS| OPERATING_EFFECTIVENES(ENUMERATION)| [CN_OPERATING_EFFECTIVENES]
  4. Cut the PEN_ENTITY_ID field and the CN_OPERATING_EFFECTIVENES field from the list.
  5. Go to the Query Explorer and add the following filters to the query:
    • [DEFAULT_REL].[SOXBUSENTITY_GPC].[GEN_REPORTING_PERIOD_ID] = -1
    • [CN_OPERATING_EFFECTIVENES] is not null
  6. In the Query Explorer, rename the query used by the list to listMain.
  7. Validate the report.
  8. Go to the main report page and complete the following steps.
    1. Sort PEN_NAME00 ascending.
    2. Set the label for PEN_NAME00 to Business Entity.
    3. From the Insertable Objects pane Toolbox tab Toolbox icon, select a Chart and drop it into the last column of the existing list. The list must be locked.
    4. Select Microchart and a Column bar chart.
  9. In the new chart, drag and drop the following data items:
    • DEFAULT|[DEFAULT_REL]|GRC_OBJECTS|[SOXCONTROL]|ID_FIELDS| [CN_CONTROL_ID]
    • DEFAULT|[DEFAULT_REL]|GRC_OBJECTS|[SOXCONTROL] ENUMERATION_FIELDS| OPERATING_EFFECTIVENES(ENUMERATION)| [CN_OPERATING_EFFECTIVENES]
  10. In the Query Explorer, rename the query used by the list to microChart.
  11. Go to the Query Explorer and add the following filters to the query:
    • [DEFAULT_REL].[SOXBUSENTITY_GPC].[GEN_REPORTING_PERIOD_ID] = -1
    • [CN_OPERATING_EFFECTIVENES] is not null
  12. From the Insertable Objects pane drag and drop the following data item: 
    DEFAULT|[DEFAULT_REL]|GRC_OBJECTS|SOXBUSENTITY_FOLDER| 
    [SOXBUSENTITY_GPC]|ID_FIELDS|[PEN_ENTITY_ID]
  13. Select the CN_CONTROL_ID data item and in the Properties pane, complete the following steps.
    1. Under the Data Item category, set the Aggregate Function property to Count Distinct.
    2. Under the Data Item category, set the Rollup Aggregate Function to Automatic.
  14. On the main report page, rename the chart column to Operating Effectiveness.
  15. From the Data menu on the main toolbar, select Master Detail Relationships, and complete the following steps.
    1. Create a join between PEN_ENTITY_ID in the listMain and the PEN_ENTITY_ID in the microchart.
    2. Click OK.
  16. Select the microchart background and complete the following steps.
    1. In the Properties pane under the category Positioning, set the Size & Overflow property to a height of 100 px.
    2. Set the width to 150 px.
  17. Select the y axis of the microchart and in the Properties pane under the General category, set the Axis Labels property to Show.
  18. In the Condition Explorer, select variables and complete the following steps.
    1. Add a new string variable.
    2. In the expression editor, add [microChart].[CN_OPERATING_EFFECTIVENES]
    3. Click OK.
    4. Add three values: Effective, Ineffective, and Not Determined.
    5. In the Properties pane under Miscellaneous, set the Name property to Operating Effectiveness
  19. On the main report page, select the microchart and in the Properties pane under Color & Background, select the Conditional Palette and complete the following steps.
    1. For the Variable, select Operating Effectiveness.
    2. For Effective set the color to lime.
    3. For Ineffective set the color to red.
    4. For Not Determined set the color to blue.
    5. Click OK.
  20. Select the Series bar. In the Properties pane under Chart Labels, set the Values property to Show.
  21. Run the report.

Results

There is now a comparison of control Operating Effectiveness across different business entities to compare progress in how they are getting their process under control.