IBM Cognos Proven Practices: Sample of Report Building using the Excel Add-in Link for IBM Cognos Controller

Nature of Document: Guideline; Product(s): IBM Cognos Controller; Area of Interest: Financial Management

This document will provide samples of reports that can be built using the excel addin link.

Business Analytics Proven Practices Team, Business Analytics Proven Practices Team, IBM

Business Analytics Proven Practices Team



01 December 2010

Also available in Chinese

Introduction

Purpose

This document will provide samples of reports built using the excel add-in link for IBM Cognos Controller.

Applicability

IBM Cognos Controller

Exclusions and Exceptions

There are no known exceptions and exclusions at the time this document was created.


Income Statement Report Sample

Income Statement Report

The income statement report is that can be built using the excel add-in link. However, you need to understand that IBM Cognos Controller stores information as a year-to-date and needs to be broken down into its monthly amount or even quarterly. The important aspect of the report will be the accounts and periods/actualities.

Axis Definition – This is used to load information by rows and columns.

Figure 1 shows the defining of the row and columns
Figure 1 shows the defining of the row and columns

Row Definition – Profit and loss accounts are used as the row definition.

Figure 2 shows the insertion of the accounts. The profit and loss accounts are included in the row definition.
Figure 2 shows the insertion of the accounts. The profit and loss accounts are included in the row definition.

Column Definition is using the time periods. This is where you need to look at what you want to include in the report, i.e. 1 month, quarterly, YTD or Budget and Actual Information.

Figure 3 shows the time period. The report definition is actually doing the last three months with a year-to-date for actual and budget.
Figure 3 shows the time period. The report definition is actually doing the last three months with a year-to-date for actual and budget.
Figure 4 – Sample Income Statement Report
Figure 4 – Sample Income Statement Report

Hint: IBM Cognos Controller has certain information that is retrieve from the IBM Controller database. The information may not be in a format that is suitable for reporting, so you may need to use multiple sheets and excel commands to format the information in the desired format.

Figure 5 shows the spreadsheet tabs
Figure 5 shows the spreadsheet tabs
  • You may want to convert the date from the IBM Cognos Controller format 0812 to something that makes more sense.
  • Currency reference may also need to be converted and formatted in the reporting.
  • You can modify the column headings.
  • The first sheet brings in the accounts. It is good to use multiple sheets to report different views of the data.
  • The key is once the report is pulled into Excel then you have the options to used excel commands to format and build report to your specifications.

Balance Sheet Report Sample

Balance Sheet Report

The Balance Sheet can be built using the excel add-in link. The Balance Sheet report is reflected as at certain period of time.

Axis Definition – Accounts should be included in row definition and the period/actuality in the column definition.

Figure 6 shows the the definition of the report axis
Figure 6 shows the the definition of the report axis

Row Definition – The accounts can be pulled into the general ledger or you can use a Balance sheet form. This example will pull the accounts into the report.

Figure 7 shows the account definition for the Balance Sheet Report
Figure 7 shows the account definition for the Balance Sheet Report

Hint: It is recommended to pull in all the accounts to build the balance sheet report. You may want to do ratios/metrics as part of the report building.

Column Definition - The column definition is where you will define the periods and actualities to be used in the report.

Figure 8 shows the column definition for the Balance Sheet Report
Figure 8 shows the column definition for the Balance Sheet Report
Figure 9 shows Balance Sheet Report
Figure 9 shows Balance Sheet Report
Figure 10 shows the worksheets that can be used as part of formatting and/or for pulling the information together
Figure 10 shows the worksheets that can be used as part of formatting and/or for pulling the information together

Date Conversion Sheet: You can create a spreadsheet that converts the IBM Cognos Controller date format to more desirable format. This conversion of the date can done using concatenate excel function along with column references.

Figure 11 shows date conversion spreadsheet
Figure 11 shows date conversion spreadsheet

Analysis and Commentary

Analysis and other reporting requirements

The document discussed earlier about pulling all the account information into one spreadsheet and then building reports from this information. The account structures will allow you to build income statement and/or balance sheet reports. You may also build other reports in excel like cash flow if you track the movements of specific accounts, like fixed assets, share capital, loans, etc. The movement accounts can be integrated and/or non-integrated. However, if you are using non-integrated accounts then you will need to ensure you have a reconciliation process between the real account and the statistical account. The following example will show how to build some additional reports and analysis using the excel add-in link.

Figure 12 shows the tabs for building additional reports and analysis
Figure 12 shows the tabs for building additional reports and analysis

The axis definition will be same, i.e. Accounts (rows) and Period/Actuality (columns). If you want to use the balance sheet and income statement account information in the same the file then you will need to define the periods correctly in column data pulled.

Balance sheet for the period should reflect as the YTD.

Figure 13 shows the column definition for the balance sheet accounts
Figure 13 shows the column definition for the balance sheet accounts

The income statement account usually want to be reflect by their monthly total or by the year-to-date. So you may have to create column identifier for the income statement period information (i.e. Monthly amounts).

Figure 14 shows the column definition for Income Statement accounts
Figure 14 shows the column definition for Income Statement accounts

If you pull in all the accounts:

  • Income Statement
  • Balance Sheet Accounts
  • Movement accounts/Extension
  • Statistical accounts (headcount, Production stats, days, etc)

You can build reports based on the above information, because you are only pulling information from IBM Cognos Controller via the initial data pull. You are using excel formulas, formatting, design, etc to build reports to your own specification.

Cash Flow Schedule

You can create cash flow schedule using the account structures or you can build a report in the excel link based requirement information. Listed below is a sample Cash Flow Report.

Figure 15 shows sample of cash flow schedule that can be built in IBM Cognos Controller
Figure 15 shows sample of cash flow schedule that can be built in IBM Cognos Controller

Other Analysis

You can build ratios and/or analysis by pulling information from the accounts, statements within the same excel file.

Figure 16 shows some analysis that can be done based on the accounts
Figure 16 shows some analysis that can be done based on the accounts

Hint: You can recycle some analysis and other information in other reports within the excel linked report.


Dimensionality Reporting

Building Reports that used Dimension

The dimensional level of detail in IBM Cognos Controller can be used for additional analysis. However, you may need to build separate reports to include all those accounts that have dimensional level of detail, i.e. Revenue, Cost of Goods Sold, etc.

There are four main dimension in IBM Cognos Controller called Dim1, Dim2, Dim3 and Dim4. So, you will want to review what dimension are active and for what account.

Figure 17 shows the extended dimension structure settings
Figure 17 shows the extended dimension structure settings
Figure 18 shows the account structure and the dimensional levels
Figure 18 shows the account structure and the dimensional levels

Hint: You will need to understand the level of detail for each account and how that relates to the dimension code.

Sample Report

Figure 19 shows reporting with dimension in column and the accounts in the rows
Figure 19 shows reporting with dimension in column and the accounts in the rows
  • Axis – Rows (Accounts) and Columns (Dim 2 – Product)
  • Row only includes accounts with dimensionality
  • Column brings in the complete product dimension

Hint: You may want to review the report design based on either # of accounts and/or dimension being reported.

Figure 20 shows the excel add-in tabs
Figure 20 shows the excel add-in tabs

You can create a data, currency and company tab to modify those headings.


Variance Analysis Reports

Building Reports that show variance analysis

Once you have pulled in the account and period/actuality information into excel sheet then you can do a budget variance analysis. The budget variance analysis column can be set up on how you review the budget variances.

Figure 21 shows the Income Statement with the Budget Variance
Figure 21 shows the Income Statement with the Budget Variance

Hint: You can us existing reports or create a separate variance report. It just depends on your preference and management requirements.

Figure 22 shows the tabs to support the income statement and variance report
Figure 22 shows the tabs to support the income statement and variance report

Download

DescriptionNameSize
Article contained in ZIP fileSamples_of_Report_Building_using_the_Excel_add-in_Link_for_IBM_Cognos_Controller.zip1453KB

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Big data and analytics on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Business analytics, Information Management
ArticleID=593206
ArticleTitle=IBM Cognos Proven Practices: Sample of Report Building using the Excel Add-in Link for IBM Cognos Controller
publish-date=12012010