Product:
Planning Analytics 2.0.6
Microsoft Windows 2016 server

Problem:
The vValue need to have 6 decimals in the output and the string vOrigincompany need to have fixed width of 6 characters.

Solution:
In TM1 write TI code like this:

# format to fixed length of 6 decimals
nResult1 = SCAN ( ‘,’ , vValue ) ;
if (nResult1 = 0) ;
sResult1 = vValue | ‘,000000’ ;
else;
# values with more than 6 decimal values is rounded up
vValue = numbertostring( ROUNDP( stringtonumber(vValue) , 6) ) ;
nResult1a = 6 – ( LONG (vValue) – nResult1) ;
sResult1 = FILL (‘0’, nResult1a) ;
sResult1 = vValue | sResult1 ;
endif;

# add spaces to name so we have a fixed width column
sResult2 = FILL(‘ ‘, 6 – LONG (vOriginCompany));
sResult2 = vOriginCompany | sResult2 ;

# There exist other ways to solve this, but this is a suggestion.

textoutput ( ‘c:\temp\filename.txt’ , sResult1 , sResult2 );

More information:
https://exploringtm1.com/fixed-width-file-format-from-tm1/

Product:
Planning Analytics 2.0.6
Microsoft Windows 2016 server

Problem:
How to remove temporary views in TI code?

Solution:

Use a parameter in the TI process, to ask if the views should be removed. pView will have the value Y if they should be removed.

Create the views in Prolog

# pCube: Specify cube name
pCube =’FAPMonthlyCompanyDetails’;

# set the name of the subset and view
# with a time stamped
sView = ‘FAP_Export_’ | TIMST(NOW(), ‘\Y\m\d_\h\i’, 1) ;
sSubset = sView;

Use the views in your code

# Step 1: create View
If( ViewExists( pCube, sView ) = 1 );
ViewDestroy( pCube, sView );
EndIf;
ViewCreate( pCube, sView );

# Step 2: create first subset
If( SubsetExists( pDim1, sSubset ) = 1 );
SubsetDeleteAllElements( pDim1, sSubset );
Else;
SubsetCreate( pDim1, sSubset );
EndIf;

SubsetElementInsert( pDim1, sSubset, pElement1, 1 );

remove the views in Epilog

if (pView @= ‘Y’) ;

# remove the view
ViewDestroy( pCube, sView );

# destroy all subsets
i = 1;
# loop through all dimensions of the cube
while (tabdim(pCube,i) @<> ”);
SubsetDestroy(tabdim(pCube,i), sSubset);
i = i + 1;
end;

endif;

More information:
https://www.bihints.com/creating_temporary_views

http://cognosknowhow.blogspot.com/2014/07/how-to-create-tm1-view-using-ti-script.html

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