Skip to content

Audit Trails and Reporting

Last updated: 27 May 2025

Audit Trails & Reporting

All ControlWORQ configuration and audit trail data are stored in the underlying ControlWORQ database. This provides users and auditors with rich transaction details behind all entries and adjustments posted to Planning Analytics through ControlWORQ.

Comprehensive Transaction Recording

A detailed record of all changes committed to the Planning Analytics cube as journals and inter-company eliminations are posted is written to the journal database. The record includes:

  • Date and time of transaction
  • User ID performing the action
  • Journal identifier
  • Planning Analytics cube name
  • Planning Analytics element identifiers
  • Old value and new value information

Each change to a journal entry is captured in the database, including:

  • Each change saved (if the entry is saved multiple times during the edit process)
  • Each step through the journal workflow process (Save, Approve, Post)

Database Storage and Access

The journal database is stored in Microsoft SQL Server and is accessed through a secure read/write ODBC connection. The database structure is created during the initial ControlWORQ Server installation process.

⚠️ Important: Backing up the SQL Server database along with Planning Analytics data ensures that consistency and controls will be maintained in the system.

Reporting Capabilities

Since the underlying ControlWORQ database is stored in a relational database, this means that users can generate journal reports using any popular relational reporting tool including:

  • IBM Cognos BI
  • Microsoft SQL Server Reporting Services
  • Crystal Reports
  • Other third-party report writers

Built-in Reports

ControlWORQ includes several standard reports:

  • Journal Entry Summary Report: Provides header information for journal entries
  • Journal Entry Detail Report: Shows detailed line-by-line information for journal entries
  • Journals by Account Report: Summarizes all entries that have impacted each account
  • Intercompany Reconciliation Report: Shows intercompany balance details and elimination status

Custom Reporting Options

Organizations can create custom reports by:

  1. Direct database access: Query the ControlWORQ SQL Server database directly
  2. Planning Analytics integration: Create summary cubes in Planning Analytics based on the relational database data
  3. Third-party tools: Use business intelligence tools to create dashboards and reports

Audit Trail Benefits

The comprehensive audit trail provides several key benefits:

  • Regulatory compliance: Meet financial reporting and audit requirements
  • Change tracking: Complete history of who made what changes and when
  • Data integrity: Ensure all journal entries are properly balanced and authorized
  • Performance monitoring: Track user activity and system usage patterns
  • Troubleshooting: Investigate and resolve data discrepancies

Database Table Structure

The ControlWORQ database includes several categories of tables:

Configuration Tables

  • Cubes and dimension definitions
  • User roles and permissions
  • Journal entry groups and settings

Transaction Tables

  • Journal entry headers and details
  • Line-level transaction data
  • Element selections for each entry

Audit Tables

  • Historical versions of all journal entries
  • Workflow step tracking
  • User action logs

Intercompany Tables

  • Elimination batch tracking
  • Session element selections
  • Hierarchy definitions

📊 Note: For detailed information about the database schema, refer to the Technical Installation Details section of the Administration Guide.