Cognos Controller 10.4.1
CONTRL_name=IBM Cognos Controller
Microsoft Windows 2012 Server

How setup Cognos Controller Web with SSO?

Ensure that SSO is working from your Cognos Controller client first, when you login.

Stop the Controller web services

Start NOTEPAD in ADMINISTRATOR mode, and open up the file C:\Program Files\IBM\cognos\ccr_64\fcmweb\wlp\etc\server.env

Change the JAVA HOME line to
JAVA_HOME=C:/Program Files/IBM/cognos/ccr_64/fcmweb/jre
Save the file.
Start a CMD prompt as ADMINISTRATOR, go to the folder C:\Program Files\IBM\cognos\ccr_64\fcmweb and enter below command:
SyncDBConf.bat     ..\Data     wlp\usr\shared\config\datasources

The number of database updated depend on the number of database you have setup in Cognos Controller
From notepad open file C:\Program Files\IBM\cognos\ccr_64\fcmweb\wlp\usr\servers\fcm.web\

Change the lines to like this;

change win-ca to your controller/cognos analytics server.
Save the file.
Change the memory used by controller web by open from notepad file C:\Program Files\IBM\cognos\ccr_64\fcmweb\wlp\etc\jvm.options
You must test different values to find the best – maybe start with -Xmx12g
# Java Heap size.
# In production, setting min and max to same value can provide the best performance by avoiding heap expansion and contraction.
# But high min value increases startup time, which may be undesired in a dev environment.

Save the file.
Start the Windows service IBM Cognos Controller Web

Open a new document in notepad – and paste in below text

<CRNenv c_cmd=”!/CamLogin”>


<param name=”cam_passport”/>



save the file to C:\Program Files\ibm\cognos\analytics\templates\ps\portal\variables_CCRWeb.xml on your Cognos Analytics server.

On your Controller server open in NOTEPAD this file C:\Program Files\IBM\cognos\ccr_64\frontend\config.js

Change the hostname and publicInterface to your controller servers FQDN name e.g.
Save the file.
Start the Windows service IBM Cognos Controller Web UI

Surf to to get into the Cognos Controller Web

Select your database and login.

Cognos Controller 10.4.1 only supports this versions

You download CA11.0.13 that is part of the Controller package
IBM Cognos Controller 10.4.1 Microsoft Windows Multilingual CC0URML
IBM Cognos Analytics Limited Use 11.0.13 Microsoft Windows Multilingual CNV2WML
IBM Cognos Framework Manager 11.0.13 Microsoft Windows Multilingual CNV2EML
IBM Cognos Analytics Samples 11.0.13 Microsoft Windows Multilingual CNV2LML
Download PA if you are going to use the FAP service
IBM Planning Analytics 2.0.6 Microsoft Windows Multilingual CNWN1ML
IBM Planning Analytics Client 64-bit 2.0.6 Microsoft Windows Multilingual CNWN2ML
Download if you are going to use the PAX plugin to Excel
IBM Planning Analytics Workspace 2.0.6 Microsoft Windows Server 2016 Multilingual CNWN4ML
IBM Planning Analytics for Microsoft Excel 64-bit 2.0.6 Microsoft Windows Multilingual CNWN6ML

How to setup CA11 instructions here

How to setup SSO with CA instructions here

How install CA samples

More Information:

How setup SSL with Controller Web

Cognos Controller 10.4
Microsoft Windows 2016 server

During reporting period when using data entry, one some forms, you get a message that the sheet is protected. When you try to unprotected it in excel you are ask for a password. This is a sign that the form is corrupt and need to be created again.

Error message:


First open the form for edit, and then select standard colors, update layout, save layout.
Exit Excel and Cognos Controller. Then test again if the data entry form works.
In some cases you need to run optimize database from inside Cognos Controller.

If above does not work, then it may be faster for you to create a new data entry form. Go to Maintain – Form Structure – Define.
Create it with a new name, you can reused the rows definition, but most likely need to manually recreate the columns definition under new name. Save it and attach it to the right groups for linked structure. If it works, remove the old not working form, and rename this to the old name (make it easier for the users to use same name as before). You need to update the linked structure again.

More information:

Cognos Controller 10.4
Microsoft Windows 2016 server
Oracle database 12c

How backup and restore a oracle database (to send to cognos support).

To make a backup run this command:
expdp SYSTEM/password@databaseServiceName schemas=FRANGO directory=data_pump_dir dumpfile=controllerlive.dmp logfile=controllerlive.log

Zip the files before sending them to cognos support.
After unzip the files, restore them using this command:
impdp scott/tiger DIRECTORY=data_pump_dir DUMPFILE=expdp_file1.dmp,expdp_file2.dmp,expdp_file3.dmp,expdp_file4.dmp,expdp_file5.dmp,expdp_file6.dmp,expdp_file7.dmp,expdp_file8.dmp logfile=log01.log SCHEMAS=FRANGO

Replace scott tiger with your user/password for your oracle database.
“data_pump_dir” should be your folder where the unzipped files are stored on your oracle server.
Change the name of the dmp file to match the filename you are using.
More information:

Cognos Controller 10.4 local
Microsoft Windows 2016 server
Oracle Database 12c

When try to run the store procedure to load data into Cognos Controller, with use of staging table, you get a error.

Error in SQL developer:
Error report –
ORA-01861: literal does not match format string
ORA-06512: at line 5

Include formatting information in the call to the store procedure for oracle.
Inside Oracle SQL developer enter this command to execute:
xx number;
— Call the function
xx := FRANGO.usp_triggerimportbatchjobs(‘1a’,’EUROIMSP’,’D’,”,’ADM’,1,to_date(‘2019-07-03′ ,’yyyy-mm-dd’));

Replace FRANGO with the schema you want to update.
The 1 say it will run now. Replace 2019-07-03 with today date.
EUROIMSP is the name of the controller import specification, that should be used during the process.

Inside the cognos controller database are some tables that you use to load data into Cognos Controller.
One table is XSTAGEFACT, that is used to load data into Cognos Controller.
Before loading data into the table truncate it with this command:
(replace frango with the schema name of your database)

Prepare a csv/xls file with the same columns as in the table.

To make a easy import from file, ensure the first rows name exactly match the columns names, e.g. ST_PERIOD

Column ST_ID should be a alphanumeric identifier, in our example we use “1a”.
Column BATCH_ID should be blank, as here will Cognos Controller add the batch work number.
Use the wizard in SQL Devloper to import your data from file to the xstagefact table.

Ensure you have “import specification” that works with a csv file first, then you need to update that (make a copy) to be ready for use with Controller Staging Tables. Two of the things you need to do, to make it work for staging tables are;

Change the general settings to “Controller Staging Table”

Go through the fields and set the to origin to “Controller Staging Table” and operation to “None”.

Save the new Controller Import Specification.

Then you need to start the store procedure, for testing direct from SQL developer.
xx number;
— Call the function
xx := FRANGO.usp_triggerimportbatchjobs(‘1a’,’EUROIMSP’,’D’,”,’ADM’,1,to_date(‘2019-07-03′ ,’yyyy-mm-dd’));

The parameters for the usp_triggerimportbatchjobs is:

@pImpId, this is the value in the st_id column identifying the lines are to be imported.

@pImpSpec, this is the NAME of the import specification that is used for this import. This must exist in Cognos Controller.

@pImpSpecType, this is the code to identify the information that is to be loaded, it could be any of the following values: D=Data A=Account R=Currency Rates C=Company 1=Dim 1 2=Dim 2 3=Dim 3 4=Dim 4 . D means values will be read from xstagefact table.

@ImpSepcParams, this is the list of any additional parameters that need to be sent to the import spec, we leave it blank.

@CtrlUser, this is the user ID in Controller to be used for the import, in most cases ADM, even when you use CAM security.

@SchedType, this is the parameter that decides to import immediately or schedule for later date, it can include the following values: 0= Hold 1=Immediately 2=One time only. We use 1.

@ExecTime, this is the date and time for scheduled import to be carried out. The format should match the database server date format.

If you want to test run the SP again, with same data, then you need to clear the BATCH_ID column first with the command:


For other database, you can use command like this to start the controller store procedure:

On the DB2 server, launch ” DB2 Command Window – Administrator
2. Type the following command:

db2 connect to <database_name> user <username> using <password>

NOTE: <username> must be the exact same user (for example ‘fastnet’) that Controller is configured to use. In other words, the same user that is specified inside the ‘database connection’ inside Controller Configuration.

3. Type a command similar to the following:

db2 call usp_triggerimportbatchjobs(‘110′,’#ST_DATA’,’D’,”,’ADM’,1,to_date(‘2007-11-01’));

On Microsoft SQL server:
Execute the stored procedure via the following SQL Script:

EXECUTE db_owner.usp_triggerimportbatchjobs ‘<parameter1>’, ‘<parameter2>’, ….

Next step, is to make the loading of data automatic with some tool. Like Microsoft SSIS, IBM Data Stage or other tool.
Then you from that tool schedule the start of the store procedure to active the batch load of data into Controller database.

More information:

Cognos Controller 10.4
Microsoft Windows 2016 server

How show a nice company background picture inside the Cognos Controller client?

Take your nice team.jpg picture and copy it to the Cognos BI webserver.
Place the file in folder C:\Program Files\ibm\cognos\analytics\webcontent\team.jpg
(that is in most cases the default webserver for CA11)
Start Internet Explorer (IE) from your computer and surf to
Did it work?
Nice, now you have the picture in place.

Now you need to go into Cognos Controller Client.
Click ‘ Maintain – Installation – Local Preferences’
Inside the field ‘Key’ enter the value: startpageurl
Inside the corresponding box ‘Values’ enter the relevant website (i.e. the one that your web designer has created for this purpose), for example:

Please keep the picture small in pixels. You can use a online tool to make the picture less than 1024×768 pixels.
Click save.
Close Cognos Controller client program.
Login again, and now you should have the nice background picture inside Cognos Controller.

The value is stored inside the local file C:\Users\%username%\AppData\Roaming\Cognos\ccr\ccr.config

<add key=”startpageurl” value=”http://ca11servername/ibmcognos/team.jpg” />

If you want to prevent users to have this, you enter this in Cognos Controller Client:

Logon as a super-user (administrative) account
Click ‘Maintain – Configuration – General’
Click on tab ‘Server Preference’
Inside the field ‘Variable Name’ enter the text: ENABLESTARTPAGE
Inside the corresponding box ‘Variable Value’ enter the value: F

This will prevent the use of this function for the Controller users.

More Information:

Cognos Controller 10.4
Microsoft Windows 2016 server
Oracle Database 12c

When a user in a upgrade Cognos Controller installation opens Group – Command Center dialog, they get a error: Problem Occurred…

Error message in log:
17:45:48,191 ERROR [cognos.ccr.integration.ServerIntegrationFacade] Failed when invoking method, Corrupted data in database: Extended dimension member XYZ has a reference to itself
17:45:48,207 INFO [cognos.ccr.integration.ServerIntegrationFacade] Finished inUserRightsModelDAO.load (136 bytes): 266 ms, memory usage [kb]: max=2 097 152 | total=40 128 | free=13 962 | total free=2 070 986
17:46:51,409 INFO [cognos.ccr.integration.ServerIntegrationFacade] Executing inConfigpropertyModelDAO.getLatestSequenceNumber…

Update the database table xkod, by removing the wrong values.
(take a backup of your cognos controller database before you do this steps)
Open Oracle SQL Developer
Connect to your controller database
Enter this commands

select * from xkod where kod = ‘XYZ’;
delete from xkod where kod = ‘XYZ’;
select * from xkodt where kod = ‘XYZ’;
delete from xkodt where kod = ‘XYZ’;

Exit SQL Developer
Go into Cognos Controller Client
Turn into single user mode
Run a optimization of database
Exit Cognos Controller client

Login to Cognos Controller and test again in same database.

More information:

Cognos Controller 10.4
Microsoft Windows 2016 server
Oracle 12c database

How write the date to a text file to keep a log of a batch file. For example we use a SQL COMMAND to optimize the controller database from the outside.

Use the WMIC function in new Windows to get the date, this will work independent on of the region setting on the windows server.

Create a BAT file with this contents:
REM —- Start of file —-
@echo off
setlocal enableextensions disabledelayedexpansion

REM only get the time – date is found in wmic command
REM SET MyDate=%date:~10,4%-%date:~4,2%-%date:~7,2%
set MyTime=%time:~0,2%:%time:~3,2%:%time:~6,2%

REM Get date and time in YYYYMMDDhhmmss format for filename
for /f “tokens=2 delims==.” %%a in (‘wmic OS Get localdatetime /value’) do set “dt=%%a”
REM put underscore in the middle of date and time stamp
set CTRLTIME=%dt:~0,8%_%dt:~8,6%

set MyDate=%dt:~0,8%

set FilePath= D:\script\logs\Controller_%CTRLTIME%.log
echo %MyDate% %MyTime% “Optimization Started” >> %FilePath%

REM below is call on a oracle command, then in the sql statement will call a PRC_ANALYZE_SCHEMA process.
sqlplus /nolog @”D:\script\db_optimize.sql”
REM timeout 10

REM get new time values when it was started
set MyTime=%time:~0,2%:%time:~3,2%:%time:~6,2%

echo %MyDate% %MyTime% “Optimization finished” >> %FilePath%
REM —- END of FILE —-

Run optimize of controller database schema every week.

The SQL file should contain something like this, please test before using;

More information:

Cognos Controller 10.4
Microsoft Windows 2016 server
Planning Analytics 2.0.6

When doing a FAP IP, you get a error in the log tab that say the user does not exist on the server.

In most cases that is for controller users, who are still in the controller rights-user list, but are not included in Cognos Analytics (BI). Check if user can log in into Cognos Connection (CA11 portal). You can have left the company, and are not part of the Active Directory groups, that are linked to the Controller Users group in Cognos Connection.

Add the user to Controller Users in Cognos Connection, to get away from the error.
or inside Cognos Controller remove the user – if he left the company.
or inside Cognos Controller remove the mark for TM1 user, then his account is not copied to TM1.

If you are not set as a IBM TM1 user, then your credential is not copied over to the TM1 server.

If you are a IBM TM1 user in Cognos Controller, but not part of any group in Cognos Connection, this will give above error. The user and credential is copied to the server anyway.

If you are a IBM TM1 User and also part of the Controller Users group (or any other cognos BI group) then your credentials are copyied to the TM1 server, and there is no error in the FAP transactions log. As you are part of a Cognos BI group, you can login, and use TM1 Architect.

More information:
“IBM Cognos TM1 User group members can access Controller data in a published FAP cube in TM1 if they were granted access to the cube. An Initial Publish transfers all active TM1 users to the TM1 server. Inactive TM1 Users and inactive IBM Cognos Controller Users are not transferred to the TM1 Server.”

The following super groups can be created:

Super Group Other – All CCR users need to be included in this group to be able to view anything in TM1 (all users need to belong to at least one group in TM1 to be able to see anything)
UCSuperGroup – All CCR users that are entitled to see all companies will be included in this group
UFSuperGroup – All CCR users that are entitled to see all accounts/forms will be included in this group
UVSuperGroup – All CCR users who have the right to review all closing versions (manual journal types) will be included in this group
U1SuperGroup – All CCR users who have the right to review all elements in the extended dimension 1 will be included in this group
U2SuperGroup – All CCR users who have the right to review all elements in the extended dimension 2 will be included in this group
U3SuperGroup – All CCR users who have the right to review all elements in the extended dimension 3 will be included in this group
U4SuperGroup – All CCR users who have the right to review all elements in the extended dimension 4 will be included in this group

Cognos Controller 10.4
Microsoft Windows 2016 server
Oracle database 12c

New upgraded database from Controller 10.3 to Controller 10.4, then the user can not login in to Cognos Controller 10.4. He get a error message.

Error message:
Error loading Main Menu
System.ArgumentException: Item has already been added. Key in dictionary: ‘6000’ Key being added: ‘6000’ at system.Collections.Hashtable.Insert(Objet key, Object nvalue, Boolean add) at Cognos.Controller.Forms.ControllerMDI.LoadMenu()

Something when wrong in the DBCONV upgrade steps.
Take a new backup of the cognos controller 10.3 database and restore it on the new database server.
Go into Controller Configuration.
Mark the database connection you have restored.
Click on the test icon to ensure you have access.
Click on the Run icon, to start the Database Conversion Utility.
Click on connect.
Click on upgrade.
There should be no errors.
Click close.

Restart the Cognos Controller services to make it know about the database change.
Restart Internet Information server with the dos command: iisreset
Restart the Cognos Controller Components, by going to Control Panel\All Control Panel Items\Administrative Tools. Open Component Services. Expand Component Services – Computers – My Computer – COM+ Applications – IBM Cognos Controller Consolidation.
Right click and select “shut down”. Start it direct thereafter.

Ask the controller users to test to login again.

Cognos Controller 10.4
Microsoft Windows 2016 server

How do i now the number of Administration license i use in my Cognos Controller installation?

There is a text file that list this for each controller database, you can look into this file to find out.
The number of administrators, is not the number of users that you add to the Cognos Controller Administrators role.
Administrative User
A Controller administrative user can have access to all menu items.

Standard User
A Controller Standard User may have access to all menus in Controller with the exception of financial change/write access to menus in the ‘Maintain’ menu.

You can go to this folder on your Controller server to check.
C:\Program Files\ibm\cognos\ccr_64\SLMTagData\default\*.slmtag
This file is only updated on production servers. Default it is updated every 24 hours.

The initial generation of SLMT files is determined by the settings in the Web.config file at C:\Program Files\IBM\cognos\ccr_64\ControllerProxyServer. If a parameter is not specified in the Web.config file, then its default value is used.

SLMTag_PeriodMinuteLength 1440 Frequency of logging SLMT information (StartTime, EndTime interval). Defaults to 24 hours (1440 minutes).

More Information: