Aramar
  • About Us
  • Our Team
  • Join the Team
  • News & Events
  • Contact
  • IBM Business Analytics
    • IBM Business Analytics Software.

      • IBM Planning Analytics
      • IBM Controller
      • IBM Cognos Analytics
      • Fast Financials
      • Lease16
      • Supporting Software
      • IBM Planning Analytics.

        IBM Planning Analytics is business performance management software that combines budgeting and AI-enhanced forecasting with intuitive analysis, reporting and workflows.

      • IBM Controller

        IBM Controller delivers accurate, fully auditable financial results, a guided period close user experience, and automatic consolidation and reporting.

      • IBM Cognos Analytics

        IBM Cognos Analytics is infused with AI and offers an intuitive and user-friendly experience.

      • Fast Financials

        IBM Planning Analytics models are traditionally built from scratch but with Fast Financials we have created a solution that offers a different approach.

      • Lease16

        Lease16 is our off-the-shelf accounting IFRS 16 Lease management software solution which allows Organisations to easily manage leasing obligations in full compliance with the IFRS 16 Lease accounting standards.

      • Supporting Software.

        The team at Aramar have been working with Cognos products for many years and have a wealth of experience in implementing, developing and supporting Cognos products.

    • IBM Planning Analytics
    • IBM Controller
    • IBM Cognos Analytics
    • Fast Financials
    • Lease-16
    • Supporting Software
  • IBM watsonx
    • IBM watsonx.

      • watsonx.ai
      • watsonx.data
      • watsonx.governance
      • watsonx Assistant
      • watsonx Orchestrate
      • watsonx.ai

        Train, validate, tune and deploy AI models

      • watsonx.data

        Scale AI workloads, for all your data, anywhere

      • watsonx.governance

        Enable responsible, transparent and explainable data and AI workflows

      • watsonx Assistant

        Conversational AI for fast and friendly interaction

      • watsonx Orchestrate

        Get more time back for things that matter

    • watsonx Orchestrate
    • watsonx.governance
    • watsonx.ai
    • watsonx.data
    • watsonx Assistant
  • IBM Envizi
    • IBM Envizi.

      • IBM Envizi
      • IBM Envizi

        Fully managed platform that simplifies the collection, consolidation, management, reporting and analysis of diverse ESG data sources.

    • IBM Envizi
  • Customer Success
    • Customer Success.

      • Customer Success Programme
      • Customer Stories
      • Customer Success Programme

        All our customers join our success program.

      • Customer Stories

        Don’t just take our word for it…

    • Customer Success Programme
    • Customer Success
  • Services
    • Services.

      • Implementation and Development
      • Cloud Migration
      • Support
      • IBM Licensing
      • Knowledge Centre
      • Implementation and Development

        From practical, hands-on development through to strategic guidance, our team of specialists is here to help.

      • Cloud Migration

        We have unrivalled experience in helping customers migrate their Business Analytics installations to the Cloud and helping them to take advantage of all IBM SaaS has to offer.

      • Support

        We are proud of our dedicated, friendly support desk, providing IBM Certified support and application support.

      • IBM Licensing

        As an Gold IBM partner, Aramar are able to negotiate the best terms with IBM on software licences and can provide wrap-around support to help you get the best from the products you have purchased.

      • Knowledge Centre

        Got a question? Explore the articles in our Knowledge Centre and find the answers that you are looking for.

    • Implementation and Development
    • IBM Cloud Migrations
    • Support
    • IBM Licensing
    • Knowledge Centre
  • Solutions
    • Solutions.

      • Financial Planning and Analysis
      • Financial Reporting
      • Lease Accounting
      • Strategic Workforce Planning
      • Supply Chain Planning
      • Marketing Planning
      • Group Financial Consolidation
      • Financial Planning and Analysis

        Planning Analytics addresses all the traditional problems inherent in spreadsheet models such as the lack of scalability, model fragility, and formulae errors.

      • Financial Reporting

        Reporting can cover Profit & Loss, Balance Sheet, Cashflow, sales analysis, working capital, capital expenditure.

      • Lease Accounting

        Lease16 is our off-the-shelf accounting IFRS 16 Lease management software solution which allows Organisations to easily manage leasing obligations in full compliance with the IFRS 16 Lease accounting standards.

      • Strategic Workforce Planning

        Workforce, headcount and salary planning models built with IBM Planning Analytics are able to meet the needs of both Operations, who are focused on the resources needed to achieve targets, and of Human Resources, who need to balance the demand for resourcing at the corporate level.

      • Supply Chain Planning

        IBM Planning Analytics helps organisations to optimise their supply chain by presenting a single source of truth, unified across inventory management, demand planning, marketing, sales, operations, and logistics.

      • Marketing Planning

        Organisations that use IBM Planning Analytics as the central platform for their marketing plans are able to integrate all their disparate data into a single source of the truth to create a comprehensive view of marketing spend, targets and plans across the organisation.

      • Group Financial Consolidation

        IBM Controller is globally proven across all industries and enterprise sizes to overcome the technical challenges of transforming disparate data into uniform and coherent information needed to produce consolidated financial statements.

    • Financial Planning and Analysis Use Case
    • Financial Reporting Used Cases
    • Lease Accounting
    • Workforce Planning Use Case
    • Supply Chain Planning Use Case
    • Marketing Planning Use Case
    • Group Financial Consolidation
    • Business Intelligence
  • About Us
  • Our Team
  • Join the Team
  • News & Events
  • Contact

Knowledge Centre

IBM Planning Analytics

  • How to: Change the display row limit in Planning Analytics for Excel
  • How to: Mapping a CSV data source directory in IBM Planning Analytics on Cloud
  • How to: Checking whether a Dimension exists in Planning Analytics
  • How to: Change maximum login attempts to Planning Analytics on Cloud
  • How to: Mapping a Network Drive between Live and Development Environments in Planning Analytics on Cloud
  • Aramar Tips: Moving Databases in Planning Analytics
  • How to: Change a Dimension Element Weight in Planning Analytics via TI process
  • How to: Using the While statement Turbo Integrator Process in IBM Planning Analytics
  • How To: Investigating issues using the TM1 Server Log
  • How to: Create Subsets driven by another Dimension in Planning Analytics
  • Aramar Tips: Creating documentation IBM Planning Analytics
  • Aramar Tips: Checking your parameters with a Planning Analytics TI Process
  • Aramar Tips: Using PowerShell in Planning Analytics
  • Fundamentals: Feeders in IBM Planning Analytics
  • Fundamentals: Cube Viewer in Planning Analytics for Excel
  • Fundamentals: DBRW in Planning Analytics
  • Aramar Tips: Analysing Data in IBM Planning Analytics for Excel
  • How to: Create a Dimension in IBM Planning Analytics
  • Fundamentals: What are Dimensions in IBM Planning Analytics?
  • How to: Edit Dimensions in IBM Planning Analytics
  • How to: Create a Cube in IBM Planning Analytics
  • How to: Add a new Element to an existing Dimension in IBM Planning Analytics
  • How To: Resolve ODBC error ‘unable to open connection to database’ in IBM Planning Analytics 
  • Aramar Tips: Working with Subsets in IBM Planning Analytics
  • How to: Check the Security of a Dimension in IBM Planning Analytics
  • How To: Check the version of IBM Planning Analytics for Excel
  • How to: Check and manage your IBM Planning Analytics licence usage vs. your licence entitlement
  • How To: Set up Inactivity Timeout Sessions in IBM Planning Analytics
  • How To: Install Planning Analytics for Excel (PAfE)
  • How to: Find out your Planning Analytics Version using Remote Desktop (Planning Analytics on Cloud)
  • How To: Subscribe to IBM Security Bulletins for Planning Analytics, Cognos Analytics and Controller
  • How To: Find out your Planning Analytics Version using Workbench (Planning Analytics on Cloud)
  • How To: Create a simple hierarchy in Planning Analytics

IBM Cognos Analytics

  • How To: Subscribe to IBM Security Bulletins for Planning Analytics, Cognos Analytics and Controller

IBM Cognos Controller

  • How to: Change the reporting currency belonging to a local subsidiary (or reporting entity) in IBM Cognos Controller
  • How to: Change the reporting currency belonging to the group consolidation in IBM Cognos Controller
  • How to: Effectively structure user security in IBM Cognos Controller
  • How to: Run an internal database optimise in IBM Cognos Controller
  • How to: Schedule an internal optimise in IBM Cognos Controller
  • How to: Check currency translation using a standard report in IBM Cognos Controller
  • How to: Audit the Licenced Number of Controller Users
  • How to: Improve your Controller Excel Report Performance
  • How to: When Does the FAP Publish Client from Controller Need Updating?
  • How to: Drill Data within Cognos Controller
  • How To: Change the method of translation applied to the P&L statement, or create different P&L translation views from the same source data in IBM Cognos Controller
  • Aramar Tips: The Benefits of Using IBM Cognos Controller Web for Data Import via Import Specifications
  • How to: Check when a Company was last updated (and by who) from within IBM Cognos Controller Web
  • How to: Reconcile Data by Company in IBM Controller Web
  • How to: Consolidating a Submission in IBM Cognos Controller Web
  • How To: Fix the Intercompany button not appearing in IBM Cognos Controller
  • How To: Interrogate the backend SQL table using IBM Cognos Controller
  • How To: Log into Citrix for the first time using IBM Cognos Controller on Cloud
  • How to: Check the size of your IBM Cognos Controller database
  • How to: Clear the local cache within Cognos Controller
  • Fundamentals: What are the differences between IBM Controller Web and IBM Controller Classic?
  • How to: Navigate the Backend SQL tables within IBM Controller
  • How To: Subscribe to IBM Security Bulletins for Planning Analytics, Cognos Analytics and Controller

General

  • Aramar Tips: 5 Easy tips to consider when adding commentary to your financial reports 
  • How To: Subscribe to IBM Security Bulletins for Planning Analytics, Cognos Analytics and Controller
View Categories
  • Home
  • knowledge-share
  • IBM Cognos Controller
  • How to: Navigate the Backend SQL tables within IBM Controller

How to: Navigate the Backend SQL tables within IBM Controller

3 min read

The information in this article should be used in conjunction with Knowledge Share article “How to: Interrogate the Backend SQL Table using IBM Controller” 

The Period Table  

The most widely used data table for SQL query is the Period Table. This is presented in the ID format “XDBxx”.  Here the “xx” represents the year of the period being reported against.  For example, data posted to period 2406AC would sit on period table XDB24. 

The following table summarises the purpose or description belonging to each of the columns within this Period Table. 

Field Name  Description 
BELOPP  Amount 
BOL  Company 
BTYP  Journal Type 
DIM1  Dimension 1 
DIM2  Dimension 2 
DIM3  Dimension 3 
DIM4  Dimension 4 
ETYP  Automatic Journal Type 
INO  Automatic Identification Number 
KONTO  Account Code 
KTYPKONC  The group type and group to which values from the elimination of the acquisition calculations are booked. 
MOTBOL  Counter Company used for intercompany balances and shareholdings. 
MOTDIM  Counter Dimension used for eliminations of intercompany balances. This is only valid for dimension 1. 
PERAKT  Period and Actuality 
TRAVKD  Transaction Currency Code . 
TRBELOPP  Transaction Currency Amount 
URSBOL  Original Company (is different from BOL after consolidation), which reported the intercompany balance. 
VERNR  Journal Number 
VKOD  Currency Code 
VTYP  Currency Type, where the field indicates if a journal has been entered in another currency than the local currency. 

Table Operators or Test Selections  #

To navigate within the likes of this Period Table within SQL, you will need to use operators, as either selected or typed into the Test Field. 

As illustrated, there are several different selections that can be used, each of which is described as follows:

= Shows all values or strings equal to the defined criteria. For example, konto = ‘1390’ only displays the account 1390 in the result window.
<> Shows all values or strings separated from the defined criteria.
< Shows all values or strings less than the defined criteria.
<= Shows all values or strings less than or equal to the defined criteria.
> Shows all values or strings greater than the defined criteria.
>= Shows all values or strings greater than or equal to the defined criteria.
IN Shows all values or strings you specify. The test values are entered within parentheses and single citation marks. When entering several test values, they are separated by a comma (,). For example, konto IN (‘1310′,’2081’) will display both account 1310 and account 2081.
NOT IN Shows all values or strings apart from the ones you specify. The test values are entered within parentheses and single citation marks. When entering several test values, they are separated by a comma (,) For example, konto NOT IN (‘1310′,’2081’) shows all accounts except 1310 and 2081.
BETWEEN Shows all test values within the specified range. Separate the start value and the end value with AND. For example, konto BETWEEN ‘1310’ AND ‘2081’ shows all accounts between the accounts 1310 and 2081
LIKE Shows all test values within the specified criteria. Here you can use wild cards to search for similar strings or values. For example, konto LIKE ‘131_’ shows all four character accounts starting with 131. Another example, fkod LIKE ‘FR%’ shows all dimension codes starting with FR.
NOT LIKE Shows all test values except the specified criteria. Here you can use wild cards to search for similar strings or values. For example, fkod NOT LIKE ‘FR%’ shows all dimension codes except the ones starting with FR


The latest IBM Controller production documentation can be found on the IBM website – here

What are your Feelings
Share This Article :
  • Facebook
  • X
  • LinkedIn
  • Pinterest
Still stuck? How can we help?

How can we help?

Updated on September 6, 2024
How to: Change the reporting currency belonging to the group consolidation in IBM Cognos Controller

Powered by BetterDocs

Table of Contents
  • Table Operators or Test Selections 

Our Accreditations and Partners

Aramar works with customers of all sizes, from a range of industries helping them with Business Analytics, Business Process, Sustainability and AI solutions using IBMs leading software solutions.

email contactus@aramar.co.uk

  • LinkedIn
  • YouTube

Registered Address:
124 City Road,
London,
EC1V 2NX

© Aramar 2025. All Rights Reserved.

Terms of use Privacy Policy

Website and Marketing by Unity Online