Product:
Cognos Controller 10.4.1 web
Microsoft Windows Server 2016

Problem:
Change to use DNS alias to the cognos controller server, and now can not login to the Controller Web,
“Server is not reachable or still initializing, please refresh the page in a few seconds”
If you surf direct to servername, then controller web works, it is only with dns alias that give the error.

Solution:
You need to change to the DNS alias in the file for Controller Web front end.
Stop the IBM Cognos Controller Web UI service
Start NOTEPAD as administrator
Open file D:\Program Files\ibm\cognos\ccr_64\frontend\config.js
Change the server name at line 25 to be the dns alias

“expressJs”: {
“host”: “dnsalias.domain.com”, //interface used by Controller Web UI Service
“port”: “9080”, //port used by Controller Web UI Service

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

Test to surf to http://dnsalias.domain.com:9080/ to see if Controller Web works.

Now to surf to server name, should not work instead.

More Information:
https://www.ibm.com/support/pages/server-not-reachable-or-still-initializing-please-refresh-page-few-seconds-error-launching-controller-web-caused-incorrect-tcp-port-inside-serverxml
https://render-prd-trops.events.ibm.com/support/pages/troubleshooting-server-not-reachable-or-still-initializing-please-refresh-page-few-seconds-error-launching-controller-web
https://www.ibm.com/support/knowledgecenter/en/SS9S6B_10.4.1/com.ibm.swg.ba.cognos.ctrl_inst.doc/t_contrweb_configure.html

In cmplst.txt you will find this version for controller 10.4.1:

LICENSE_CONTROLLER_version=LICENSE_CONTROLLER-AW64-ML-RTM-10.4.1100.1-0
LICENSE_CONTROLLER_name=IBM Cognos 8 License
SWTAG_CONTROLLER_version=SWTAG_CONTROLLER-AW64-ML-RTM-10.4.1.1-0
SWTAG_CONTROLLER_name=SWTAG CONTROLLER
CONTRL_version=CCR-AW64-ML-RTM-10.4.1100.133-0
CONTRL_name=IBM Cognos Controller

Product:
Cognos Controller 10.4.1
Microsoft Windows 2016 server

Problem:
Controller admin try to update users in Maintain – Rights – User dialog, for a installation where CAM security is used. He can not see the connected CAM users in the list.

Solution:
You must be part of Cognos Connection security group Controller Administrators. Only people in this group can change things inside controller security dialog.

When you open the user dialog, you maybe got a message “The user currently logged in is not authorized to use this method”. That means you are not part of the Controller Administrator group.

Add the person to the Controller Administrator group in Cognos. The Controller Administrator group is not the same as the ibm Cognos Controller administrator authorized user license. This is controlled by access to the Maintain menu.

More Information:

https://www.ibm.com/support/pages/controller-licensing-what-difference-between-administrator-and-non-administrator-user

https://www.ibm.com/support/knowledgecenter/en/SS9S6B_10.4.1/com.ibm.swg.ba.cognos.ctrl_ug.doc/t_usurrrigh.html#usurrrigh

Product:
Cognos Controller 10.4.1 FAP service
Planning Analytics 2.0.6
Microsoft Windows 2016 server

Problem:
You get a error when run first IP in a new setup of FAP and TM1 instance.

Error in log file D:\Program Files\ibm\cognos\ccr_64\Server\FAP\error.log
10:00:08,849 | ERROR [fap.service.schedule.Scheduler] [schedulerThread], Could not logon to TM1
javax.naming.ConfigurationException: The TM1Server fap is neither in BASIC or in CAM mode
at com.ibm.cognos.fap.common.persistence.tm1.TM1Context.login(TM1Context.java:110)
at com.ibm.cognos.fap.service.schedule.Scheduler.updateDatamarts(Scheduler.java:567)
at com.ibm.cognos.fap.service.schedule.Scheduler.run(Scheduler.java:257)
at com.ibm.cognos.fap.service.schedule.Scheduler$1.run(Scheduler.java:162)
at java.lang.Thread.run(Thread.java:812)

Solution:
Change to IntegratedSecurityMode=5 from IntegratedSecurityMode=2 in TM1s.cfg file for the TM1 application.

During setup you have been in mode 2 to be able to add the windows service account that is going to be used by the FAP service to login to the TM1 solution. Keep in mind when you setup the DataMart (TM1 server) you need to specify the user login to TM1 with namespaceid\user name as NamespaceID looks in Cognos Configuration (CA11) and user name as you can find it in Cognos Connection. Something like this:

Domain\Lastname Firstname

The field in Edit Data Mart is case sensitive and space sensitive.

More Information:

https://www-01.ibm.com/support/docview.wss?uid=swg21679216

https://www.ibm.com/support/knowledgecenter/en/SS9S6B_10.4.1/com.ibm.swg.ba.cognos.ctrl_inst.doc/t_win_conf_cam.html

Product:
Cognos Controller 10.4.1
CONTRL_version=CCR-AW64-ML-RTM-10.4.1100.133-0
CONTRL_name=IBM Cognos Controller
Microsoft Windows 2012 Server

Problem:
How setup Cognos Controller Web with SSO?

Solution:
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\com.ibm.cognos.fcm.web.properties

Change the lines to like this;
ccrwsUrl=http://win-ca.company.com/ibmcognos/controllerserver/ccrws.asmx
biUrl=http://win-ca.company.com/ibmcognos/bi/v1/disp
biDispatchEndpoint=http://win-ca.company.com:9300/p2pd/servlet/dispatch
loginMode=CAM

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.
-Xms2g
-Xmx4g


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=”http://win-ca.company.com:9080/#!/CamLogin”>

<cookies>

<param name=”cam_passport”/>

</cookies>

</CRNenv>

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. win-ca.company.com
Save the file.
Start the Windows service IBM Cognos Controller Web UI

Surf to http://win-ca.company.com:9080/ to get into the Cognos Controller Web

Select your database and login.

Cognos Controller 10.4.1 only supports this versions

https://render-prd-trops.events.ibm.com/support/pages/downloading-ibm-cognos-controller-1041

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
https://www.ibm.com/support/knowledgecenter/en/SSEP7J_11.0.0/com.ibm.swg.ba.cognos.inst_cr_winux.doc/c_settinguptheenvironment.html
https://render-prd-trops.events.ibm.com/support/pages/how-install-cognos-analytics-111x

How to setup SSO with CA instructions here
https://www.ibm.com/support/knowledgecenter/en/SSEP7J_11.1.0/com.ibm.swg.ba.cognos.inst_cr_winux.doc/t_stp_sso_active_drctry_remote_user.html

How install CA samples
https://revelwood.com/installing-samples-cognos-analytics/

More Information:
https://www.ibm.com/support/pages/how-configure-controller-web-use-cognos-cam-authentication
https://www.ibm.com/support/knowledgecenter/en/SS9S6B_10.4.1/com.ibm.swg.ba.cognos.ctrl_inst.doc/t_contrweb_configure.html

https://www.ibm.com/support/pages/blank-empty-white-screen-when-launch-controller-web-inside-internet-explorer-typically-directly-application-server-caused-using-compatibility-view

How setup SSL with Controller Web

https://render-prd-trops.events.ibm.com/support/pages/how-configure-controller-web-use-ssl-https

Product:
Cognos Controller 10.4
Microsoft Windows 2016 server

Problem:
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:

Solution:

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:
https://www-01.ibm.com/support/docview.wss?uid=swg21666512
https://www-01.ibm.com/support/docview.wss?uid=swg21666563

Product:
Cognos Controller 10.4
Microsoft Windows 2016 server
Oracle database 12c

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

Solution:
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:
https://www-01.ibm.com/support/docview.wss?uid=swg21347756
https://oracle-base.com/articles/10g/oracle-data-pump-10g#DatabaseExpImp

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

Problem:
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

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

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.

Background:
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:
TRUNCATE TABLE FRANGO.XSTAGEFACT;
(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.

https://www.thatjeffsmith.com/archive/2012/04/how-to-import-from-excel-to-oracle-with-sql-developer/

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. https://www.ibm.com/support/knowledgecenter/en/SS9S6B_10.4.0/com.ibm.swg.ba.cognos.ctrl_web_ug.10.4.0.doc/t_defining_import_spec_web.html

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

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:

UPDATE FRANGO.XSTAGEFACT SET BATCH_ID = NULL;

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:
https://www-01.ibm.com/support/docview.wss?uid=swg21458934
https://www-01.ibm.com/support/docview.wss?uid=swg21370812
https://www-01.ibm.com/support/docview.wss?uid=swg21374718
https://www.ibm.com/support/knowledgecenter/en/SS9S6B_10.4.1/com.ibm.swg.ba.cognos.ctrl_ug.doc/c_ctrl_ug_expimp_importingdatafromanexternalapplication.html#ctrl_ug_expimp_ImportingDatafromanExternalApplication
https://en.wikipedia.org/wiki/SQL_Server_Integration_Services

Product:
Cognos Controller 10.4
Microsoft Windows 2016 server

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

Solution:
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 http://ca11server.domain.com/ibmcognos/team.jpg
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: http://ca11servername.domain.com/ibmcognos/team.jpg


Please keep the picture small in pixels. You can use a online tool to make the picture less than 1024×768 pixels.
https://www.reduceimages.com/
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

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

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:
https://www-01.ibm.com/support/docview.wss?uid=swg21347128
https://www.unitconverters.net/typography/millimeter-to-pixel-x.htm

https://picresize.com/

Product:
Cognos Controller 10.4
Microsoft Windows 2016 server
Oracle Database 12c

Problem:
When a user in a upgrade Cognos Controller installation opens Group – Command Center dialog, they get a error: Problem Occurred… com.ibm.cognos.ccr.common.InvalidContextException.

Error message in log:
17:45:48,191 ERROR [cognos.ccr.integration.ServerIntegrationFacade] Failed when invoking method,
com.ibm.cognos.ccr.common.InvalidDataException: Corrupted data in database: Extended dimension member XYZ has a reference to itself
at com.ibm.cognos.ccr.extdim.internal.DefaultExtendedDimensionMember.initLevel(DefaultExtendedDimensionMember.java:213)
at com.ibm.cognos.ccr.extdim.internal.DefaultExtendedDimensionMember.addMember(DefaultExtendedDimensionMember.java:161)
at com.ibm.cognos.ccr.extdim.internal.DefaultExtendedDimensionsModel.buildMembers(DefaultExtendedDimensionsModel.java:239)
at com.ibm.cognos.ccr.extdim.internal.DefaultExtendedDimensionsModel.buildModel(DefaultExtendedDimensionsModel.java:185)
at com.ibm.cognos.ccr.common.AbstractModel.load(AbstractModel.java:566)
at com.ibm.cognos.ccr.common.AbstractModel.load(AbstractModel.java:551)
at com.ibm.cognos.ccr.common.AbstractModelFactory.getNewModel(AbstractModelFactory.java:81)
at com.ibm.cognos.ccr.common.AbstractModelFactory.getSharedModel(AbstractModelFactory.java:93)
at com.ibm.cognos.ccr.common.DefaultModelContext.prepareContextWithSharedModels(DefaultModelContext.java:221)
at com.ibm.cognos.ccr.integration.ServerIntegrationFacade.prepareContextWithSharedModels(ServerIntegrationFacade.java:278)
at com.ibm.cognos.ccr.integration.ServerIntegrationFacade.execute(ServerIntegrationFacade.java:237)
at com.ibm.cognos.ccr.integration.ServerIntegrationFacade.executeTaskWithoutExceptionHandling(ServerIntegrationFacade.java:130)
at com.ibm.cognos.ccr.integration.ServerIntegrationFacade.executeTask(ServerIntegrationFacade.java:113)
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…

Solution:
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’;
commit;

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:
https://www-01.ibm.com/support/docview.wss?uid=swg21691901

Product:
Cognos Controller 10.4
Microsoft Windows 2016 server
Oracle 12c database

Problem:
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.

Solution:
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;
connect CTRLDBNAME/CTRLDBPASSWORD@TNSALIAS
exec CTRLDBNAME.PRC_ANALYZE_SCHEMA;

More information:
https://ss64.com/nt/wmic.html
https://ss64.com/nt/timeout.html
https://www.ibm.com/support/knowledgecenter/en/SS9S6B_10.2.1/com.ibm.swg.ba.cognos.ctrl_inst.10.2.1.doc/t_run_oracle_batchjob_prc_analyze_schema.html