Product:
Cognos Controller 11
Microsoft Windows 2022 server

Issue:

Error when you try to setup the Cognos Controller web, by running bat command to add the databases.

D:\Program Files\ibm\cognos\ccr_64\fcmweb>SyncDBConf.bat ..\data wlp\user\shared\config\datasources
Found 6 udl files in ..\data

FATAL ERROR: ‘java.io.FileNotFoundException: D:\Program Files\ibm\cognos\ccr_64\fcmweb\wlp\user\shared\config\datasources\datasources.xml (The system cannot find the path specified.)’
:D:\Program Files\ibm\cognos\ccr_64\fcmweb\wlp\user\shared\config\datasources\datasources.xml (The system cannot find the path specified.)
Datasources updated.

Solution:

Try to Find the config.js file in C:\Program Files\IBM\cognos\ccr_64\frontend folder
Open the file in a text editor and make the following changes:

/* IBM SOURCE CODE HEADER START

* =====================================================================

* IBM Confidential

*

* IBM Cognos Products: ccr

*

* © Copyright IBM Corp. 2023

*

* The source code for this program is not published or otherwise

* divested of its trade secrets, irrespective of that has been

* deposited with the U.S. Copyright Office.

* =====================================================================

* IBM SOURCE CODE HEADER END */

var fs = require('fs');




module.exports =

{

"proxies": [{

"urlPath": "fcm.web",  //root context for Controller Web backend

"enabled": true,      //forward requests through UI service




"options": {

"ws": true,




// details for Controller web backend connection

"target": {

"host": "controllerservername.domain.com",

"port": 3000,

"protocol": "http:" //set https: for SSL

},

"secure": true  //set this to false if Controller Web Backend is using Self Signed certificates

}

},

{

"urlPath": "ibmcognos",  //root context for Controller Server Backend

"enabled": true,      //forward requests through UI service




"options": {

"ws": true,




//details for Controller Server connection

"target": {

"host": "controllerservername.domain.com",

"port": 80,

"protocol": "http:" //set https: for SSL

},

"secure": true  //set this to false if Controller Server Backend is using Self Signed certificates

}

}],







//Details for Controller UI service 

"expressJs": {

"host": "controllerservername.domain.com", //interface used by Controller Web UI Service

"port": "9080",              //port used by Controller Web UI Service

"adminContext": "admin",

"enableAdminApp": true,

"enableRemoteExcel": false,

"enableClient": false,

"enableFap": true,

"enableFileSettings": true,

"enableIBMIDAuth": false,

"enableEncryption": false,

"allowedOrigin": '{allowedOrigin}',

"oidcProvider": 'IBMid', //IBMid or google or azure

"options": {




//Add ssl object with path to certificates file below for using HTTPS 

//Note: the protocol in proxies section must also be set to HTTPS

                /*

                "ssl": {

// certificates

"key": fs.readFileSync(__dirname+"/keyfile.key"),  //__dirname points to ccr_64\frontend

                    "cert": fs.readFileSync(__dirname+"/cert.crt"),

                    "passphrase":"changeit"

}

*/

},




//false to enable "real time updates" feature for all the users

//true to disable "real time updates" feature for all the users

"disableRealTimeUpdates": false

},

ibmIDConfig: {

"discoveryEndpoint": "{discoveryEndpoint}",

"client_id": "{client_id}",

"client_secret": "{client_secret}",

"redirect_uris": ["{basePath}/auth/sso/callback"]

},

"entryPoints":

{

"paLink": "http://tm1servername.domain.com:9510/tm1web"

},

}

Reboot the Microsoft Windows Server, and then try to run the SyncDBConf.bat command again.

More Information:

https://www.ibm.com/docs/en/cognos-controller/11.0.1?topic=only-configuring-controller-web

Product:

Cognos Controller 11

Microsoft Windows 2022 server

Issue:

You click on the excel icon in cognos controller to start Excel program. The excel program starts very small.

Solution:

Remove the ccr.exe properties to adjust screen resolution (change high DPI settings)  or

open a excel document first in excel, before you login to cognos controller from excel, and run a report.

You have change the ccr.exe properties to not make cognos controller shrink when you open a java menu inside the cognos controller program. This is done by go to C:\Program Files\IBM\IBM Cognos Controller Local Client folder.
Right click on CCR.exe > Properties > Compatibility > Change high DPI settings > Check Override high DPI scaling behavior. Scaling performed by: System (Enhanced).

https://www.ibm.com/support/pages/controller-screen-shrink-after-opening-java-related-menu-function 

If user need to send report to Excel, they need to have an Excel book open first before run send to Excel. Otherwise the Excel will shrink after apply setting.

More Information:

https://www.ibm.com/support/pages/screen-shrinks-after-opening-java-related-menu-function-caused-limitation-apar-pi94499 

https://www.ibm.com/support/pages/controller-screen-shrink-after-opening-java-related-menu-function 

Product:

Cognos Analytics 11.2.4

Issue:

Can not install CA11 as there are file in use.

Solution:

Stop the ibm cognos service and set it to manual in windows services manager.

Stop the IIS ( World Wide Web Publishing Service ) and set it (W3SVC) to manual.

Reboot the Windows server, to clear the memory.

Login as administrator again.

Go to the folder where you have the CA11 media.

Now start the installation from; a file like analytics-installer-3.7.21-win.exe

https://www.ibm.com/support/pages/ibm-cognos-analytics-1124-fix-pack-2 

Ensure that you have backed up the content store database, and saved the cognos configuration as a text file, before you start the upgrade of Cognos Analytics.

If the setup fail during installation, you may have files to restore in folder C:\Program Files\ibm\cognos\analytics\install_backup

More Information:

https://www.ibm.com/support/pages/how-upgrade-your-version-cognos-analytics

https://docs.oracle.com/en/industries/health-sciences/inform/cognos1124-install/index.html?toc.htm?227090.htm

https://docs.oracle.com/en/industries/life-sciences/inform/cognos1124fp2-install/index.html?toc.htm?227238.htm

The IBM Cognos Analytics installation consists of two components: the installer executable and a repository zip file.

  1. Download the ca_instl_win_3.6.39.exe and ca_srv_win64_11.2.4.zip files from My Oracle Support (MOS) in Patch 23294044.
  2. Right click on ca_instl_win_3.6.39.exe, and select Run as administrator.
  3. Select the language to use for the installation, and click Next.

    The Repository selection page appears.

  4. Click Choose, browse to the server repository where the ca_srv_win64_11.2.4.zip file is located, click Open, and then click Next.

    The install getting started page appears.

  5. Select IBM Cognos Analytics, and click Next.
  6. Review and accept the license agreement, and click Next.
  7. In the Installation location field, browse to or type the drive and destination folder for the Cognos Analytics application (for example E:\cognos\c11), and click Next.

    If the Folder does not exist message appears, click Yes to create the folder.

  8. Select the appropriate installation type and components depending on your server configuration, and click Next. Do not select the Optional Gateway component.

    Server Configuration

    To install…

    Installation type

    Choose components

    Single server

    Content Manager and Report Server

    Custom

    Content Tier (mandatory)

    Application Tier

    Active Content Manager

    Custom

    Content Tier (mandatory)

    Application Tier

    Distributed environment

    Standby Content Manager

    Custom

    Content Tier (mandatory)

    Application Tier (mandatory)

    Report Server

    Custom

    Application Tier (mandatory)

  9. Review the pre-installation summary, and click Install.

    The installation program installs the components that you selected. This takes several minutes. When the component installation is complete, the Complete page appears.

  10. Click Done.
  11. On the Complete page, click Done.
  12. Open the cmplst.txt file in the Cognos installation directory.
  13. Look for the line Manifest=casrv-manifest-11.2.4-2212070924-winx64h.json to confirm that Cognos Analytics 11.2.4 is installed successfully.

 

Upgrade to IBM Cognos Analytics 11.2.4 FP2

Install IBM Cognos Analytics 11.2.4 FP2 software on all the applicable servers:

  1. Download the analytics-installer-3.7.12-win.exe and casrv-11.2.4-2307061628-winx64h.zip files from My Oracle Support (MOS) in patch 23294044.
  2. Stop all IBMCOGNOS and IIS services, set them to manual.
  3. Reboot the Windows server to clear the memory before starting the installation.
  4. Right click on analytics-installer-3.7.12-win.exe, and select Run as administrator.
  5. Select the language to use for the installation, and click Next.

    The Repository selection page appears.

  6. Click Choose, browse to the server repository where the casrv-11.2.4-2307061628-winx64h.zip file is located, click Open, and then click Next.

    The install getting started page appears.

  7. Select IBM Cognos Analytics, and click Next.
  8. Review and accept the license agreement, and click Next.
  9. In the Installation location field, browse to or type the drive and destination folder for the Cognos Analytics application (for example E:\cognos\c11), and click Next.

    A warning appears informing you that you are installing in the same location as a previous location and that files will be overwritten.

  10. Click Yes to agree to choose this location.
  11. Review the pre-installation summary, and click Install.

    A warning appears informing you about failure in pre-action installation step. Click OK to continue.

  12. Click Done.
  13. On the Complete page, click Done.
  14. Open the cmplst.txt file in the Cognos installation directory.
  15. Look for the line casrv-manifest-11.2.4-2307061628-winx64h.json to confirm that Cognos Analytics 11.2.4 FP2 is installed successfully.
  16. Go into Cognos Configuration – check that the environment values are correct – click save.
  17. Change the IBMCOGNOS service to automatic, the same for the WWW service.
  18. Reboot the Windows server.
  19. Wait 20 minutes.
  20. Check that you can login to Cognos Analytics.

 

Problems solved can be found in the Fix List for Cognos Analytics 11.2.4 FP2

Entitled Bundled Customers use this link Cognos Analytics 11.2.4 FP2 to get access to download Cognos Analytics Fix Pack 2.

Product:

Cognos Analytics 11.1.7

Microsoft SQL server 2019

Issue:

Try to connect to content store database from CA11 installation, in Cognos Configuration give you a error message.

Solution:

Error message:

Content Manager failed to connect to the content store. The connection string is “jdbc:sqlserver://192.168.1.77:1433;databaseName=ca11update;applicationName=IBM_C11_CM”. The error encountered is: “Login failed for user ‘cognos’. ClientConnectionId:

Then you have enter the wrong password.

Error message:

Content Manager failed to connect to the content store. The connection string is “jdbc:sqlserver://192.168.1.77:1433;databaseName=ca11update;applicationName=IBM_C11_CM”. The error encountered is: “Cannot open database “ca11update” requested by the login. The login failed. ClientConnectionId

Then you have enter the wrong name of the database.

 

More information:

https://www.ibm.com/support/pages/content-manager-failed-start-because-it-could-not-load-driver-commicrosoftsqlserverjdbcsqlserverdriver-when-starting-ibm-cognos-service

https://community.flexera.com/t5/FlexNet-Manager-Knowledge-Base/Analytics-Cognos-connection-to-SQL-server-fails-when-server-is/ta-p/113351

https://www.ibm.com/support/pages/cm-sys-5003-content-manager-not-configured-listen-tcpip-when-starting-cognos-bi-service

Product:
Planning Analytic in containers (v12)

Issue:

Can i write to a file share from a Tm1 TI process?

Solution:

No, you can not use the old TI command ASCIIOUTPUT

The solution, i guess. is that you need to have a external script (tm1py) that get the data to file by doing HTTP REST API calls to the Planning Analytics installation.

https://github.com/cubewise-code/tm1py

This command only access the tm1 instance data folder in v12 of Planning Analytics.

TurboIntegrator function changes

The following TurboIntegrator file functions now work only with files in the database content directory or its child directories:

  • AsciiDelete
  • AsciiOutput
  • AsciiOutputOpen
  • FileExists
  • SetOutputEscapeDoubleQuote
  • TextOutput
  • WildcardFileSearch

 

Going from v11 to v12, you have to rewrite code that access the file system.

How to do things in the new version of Tm1 is mostly found in the community……

https://community.ibm.com/community/user/businessanalytics/discussion/planning-analytics-engine-drill-through-to-asciioutput-file

https://community.ibm.com/community/user/businessanalytics/blogs/edward-stuart/2023/10/13/executehttp?CommunityKey=8fde0600-e22b-4178-acf5-bf4eda43146b&Tab=groupdetails

More Information:

https://www.linkedin.com/pulse/developers-perspective-navigating-transition-tm1-v12-shane-bethea-2fwke

https://www.ibm.com/docs/en/planning-analytics/2.0.0?topic=engine-changed-features-components-in-planning-analytics

Planning Analytics Engine (v12) does not support the C, Java, .Net, and all internal and experimental versions of the REST API. Only the OData compliant REST API is supported for interaction with the Planning Analytics Engine database.

Because older APIs are no longer supported, clients such as Architect and Perspective, do not work with Planning Analytics Engine.

https://www.ibm.com/docs/en/planning-analytics/2.0.0?topic=engine-deprecated-features-components-in-planning-analytics

The following TurboIntegrator functions are not supported in Planning Analytics v12 Engine:

AddInfoCubeRestriction
AllowExternalRequests
AssignClientPassword
AssociateCAMIDToGroup
BatchCellIncrement
BatchUpdateFinish
BatchUpdateFinishWait
BatchUpdateStart
CGAddPromptValues
CGPromptGetNextMember
CGPromptSize
CreateHierarchyByAttribute
CubeDataReservationAcquire
CubeDataReservationGet
CubeDataReservationGetConflicts
CubeDataReservationRelease
CubeDataReservationReleaseAll
CubeGetLogChanges
CubeSaveData
CubeSetConnParams
CubeSetLogChanges
DisableBulkLoadMode
EnableBatchCellIncrement
EnableBulkLoadMode
ExecuteJavaN
ExecuteJavaS
LockOff LockOn
RefreshMDXHierarchy
RemoveCAMIDAssociation
RemoveCAMIDAssociationFromGroup
SaveDataAll ServerShutdown
SetChoreVerboseMessages
SetOdbcUnicodeInterface
SwapAliasWithPrincipalName

https://www.ibm.com/docs/en/planning-analytics/2.0.0?topic=attf-asciioutput-1

https://www.ibm.com/support/pages/ibm-planning-analytics-20-fix-lists#anchor5

Example of write to PAL using TM1PY

https://gist.github.com/MariusWirtz/751c153a568d0f029c65f701fc6c03c8

https://succeedium.com/teamone/features.html

Product:
Planning Analytics 2.0.9.x
Microsoft Windows 2019 server

Issue:
Can i prevent user from change the transaction log files?

Solution:
That is the default – tm1 admin and users can not change the transactions log file from inside Planning Analytics (Tm1 architect).

You must have direct access to the TM1 server and the logg folder, and you can only change the file when the TM1 instance is stopped.

If you do not allow people access to the windows server disk – then the files can not be tampered with.

The transactions log can be read and search from inside TM1 architect, or PAW.

The following information is recorded in the tm1s.log file:

Date and time the change was made
Name of the client who made the change
Whether the new data is simple data (N) or string data (S)
Value before the change
Value after the change
Name of the cube in which the change was made
Elements that identify the cell that changed

More Information:

  • Transaction logging can be enabled and disabled for specific cubes by setting the LOGGING column in the }CubeProperties cube. Transaction logging is typically needed on cube transactions only during period of manual user data entry. For more information, see Enabling and disabling transaction logging.
  • The current transaction log file is named tm1s.log. The tm1s.log file is locked by the TM1 database and cannot be viewed or edited with a text editor. The current transaction log file is renamed to tm1s<timestamp>.log during a SaveDataAll (SaveDataAll happens automatically on shutdown or restart). An empty tm1s.log file is generated when the TM1 database starts and during a SaveDataAll without a database restart. For more information, see SaveDataAll.
  • The tm1s timestamped files, can be moved to other storage area for secure storage, if they are needed for later analysis.
  • If the server is intentionally shut down without saving the changes, the log file is saved with a timestamp and the extension is changed to .rej. You can process the tm1s<timestamp>.log file through TurboIntegrator to recover the transactions.
  • During an improper server shutdown, the tm1s.log remains as is and is not timestamped during shutdown. When the server restarts, it recognizes the presence of an old tm1s.log file and prompts you to choose between recovering the new log from the old existing log.
  • By default, TM1 logs transactions to all cubes loaded on the server. As the system administrator, you have the option to turn off logging for particular cubes. When you disable logging, TM1 accelerates updating the data but you cannot recover the updates in the event of a system failure.

 

https://exploringtm1.com/using-tm1-log-files/ 

The transactions log – IBM Documentation

Product:

Microsoft Power BI desktop

Issue:

Would like to add a step inside a existing step list in Power BI desktop.

Solution:

Go to Transform Data. Select that Query you want to change steps for.

In the right “applied steps” column, mark the row where you want to insert a blank step below.

Click on FX icon to add a step.

Now you can paste code from other step into the = #”Replaced Value” field, or do the transforms you need.

Save the report.

More Information:

https://learn.microsoft.com/en-us/power-query/applied-steps

https://learn.microsoft.com/en-us/power-bi/

https://learn.microsoft.com/en-us/power-bi/transform-model/dataflows/dataflows-configure-consume 

https://learn.microsoft.com/en-us/power-bi/create-reports/desktop-buttons?tabs=powerbi-desktop

https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-common-query-tasks

Product:
Microsoft Power BI

Issue:

What is Enable Load and Include In Report Refresh in the power bi transform data for each query?

 

Solution:

“Include In Report Refresh” means query is automatically refreshed when you press “Refresh” button on the ribbon. “Enable Load” means query results are available for report builder. Otherwise you may use it in your other queries (for example to merge data), but it is not shown in the report builder.

If you have a query that use a other query, that is unchecked “Include In Report Refresh”, then both query’s are updated when you update the first query.

This works in Power BI Desktop but not in Power BI service at 2022, but can have changed. If you unchecked Include in report refresh – The functionality works only in Power BI Desktop and it will not have any impact in Power BI Service i.e. if you refresh report in Power BI service, this will still refresh all the queries even through Include in report refresh is unchecked.

 

More Information:

https://www.purplefrogsystems.com/2021/04/power-bi-enable-load/ 

Keep The Existing Data In Your Power BI Dataset And Add New Data To It Using Incremental Refresh

 

Product:
Cognos Controller 11.0.1200
Microsoft Windows 2019 server
Microsoft SQL 2016 server

Issue:
How install Cognos Controller 11?

Solution:

This are an example of the steps to install Cognos Controller 11.  (Some information below may be incorrect or not needed)

For best guides follow the IBM documentation.  https://www.ibm.com/docs/en/cognos-controller/11.0.1?topic=icclo-installing-configuring-cognos-controller-one-computer

Before any work with Cognos Controller – take a extra SQL backup of the database.

Download the software from here:

You need to get both the RTM version and any fix pack you need to install.

https://www.ibm.com/support/pages/downloading-ibm-cognos-controller-1101

https://www.ibm.com/support/pages/node/6849235

Install IIS on the server that will run Cognos Controller. Microsoft .NET Framework 4.7.2 must be installed on the Controller Web Services Server computer. Install sqlncli.msi driver version 11.4.7462.6 or later version for SQL server.

Select the following when you add the Web Server (IIS) role to the windows server:

  • .NET Framework 4.5 and 3.5
  • ASP
  • ASP.NET
  • CGI
  • ISAPI Extensions
  • ISAPI Filters
  • .NET Extensibility
  • IIS Management Console
  • IIS Management Scripts and Tools
  • HTTP Activation
  • HTTP Redirection
  • Directory Browsing

Cognos Controller 11 only supports Microsoft SQL server database,  you need to create one database, if you not already have one from an old version of Cognos Controller. Ensure that the database collation sequence is case insensitive, and is the same for both the SQL server (tempdb database) and the Controller database. (Recommended:  SQL_Latin1_General_CP1_CI_AS ) Controller tables owned by dbo are not supported. If you have dbo as the default table owner you must change it, easiest is to create a new database from start in SSMS.

To create a schema named cognos:

IF (NOT EXISTS (SELECT * FROM sys.schemas WHERE name = 'cognos'))
BEGIN
EXEC ('CREATE SCHEMA [cognos] AUTHORIZATION [dbo]')
END

To change schema on one table:

ALTER SCHEMA cognos
TRANSFER dbo.Employees

To change default schema for a user in SQL:

ALTER USER cognos WITH DEFAULT_SCHEMA=cognos EXECUTE AS USER='cognos'

 

If you do a upgrade of Cognos Controller 11 on a old server, you need to uninstall the previous Cognos Controller 10 version first.

Check if the anti-virus software on the Windows server is compatible with you installing of software.

Copy the installation media to the server and unzip it.

Run (as a administrator) the issetup.exe from folder C:\install\cntrl_11.0.1_win_ml.tar\winx64h\ to install Cognos Controller.

Click Next

Mark I agree and Click Next

Mark non-production (if not production server) and click Next

Click Yes

Normally you select all. In some cases the FAP is only installed on your TM1 server. Controller Web is only needed if you going to use it.

Click Next.

Click Next

Click Next

Wait until it is done

Click Finish.

In the log file you can find this messages:
Install component: VFP
Component Version: VFP-AW64-ML-RTM-9.0.2120.0
File “C:\Users\ADMINI~1\AppData\Local\Temp\COG_2\server\vfp9r.dll” successfully extracted.
File “C:\Users\ADMINI~1\AppData\Local\Temp\COG_2\server\vfp9renu.dll” successfully extracted.
File “C:\Users\ADMINI~1\AppData\Local\Temp\COG_2\server\vfp9t.dll” successfully extracted.
File “C:\Users\ADMINI~1\AppData\Local\Temp\COG_2\vers\vfp-winx64h-app-9.0.2120.0-inst.ver” successfully extracted.
File “C:\Users\ADMINI~1\AppData\Local\Temp\COG_2\vers\vfp-winx64h-app-inst.ver” successfully extracted.
Successfully created directory “C:\Windows\system32\server\”.
Successfully copied file “C:\Users\ADMINI~1\AppData\Local\Temp\COG_2\server\vfp9r.dll” to file “C:\Windows\system32\server\vfp9r.dll”.
Self registration of file using command line “C:\Windows\system32\regsvr32.exe /s “C:\Windows\system32\server\vfp9r.dll””.
Error: Failed system command “C:\Windows\system32\regsvr32.exe /s “C:\Windows\system32\server\vfp9r.dll””.
Error: Application has returned exit code “3”.
Successfully deleted file “C:\Users\ADMINI~1\AppData\Local\Temp\COG_2\server\vfp9r.dll”.
Successfully set value “C:\Windows\system32\server\vfp9r.dll” for key “HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\SharedDLLs”.
Successfully copied file “C:\Users\ADMINI~1\AppData\Local\Temp\COG_2\server\vfp9renu.dll” to file “C:\Windows\system32\server\vfp9renu.dll”.
Self registration of file using command line “C:\Windows\system32\regsvr32.exe /s “C:\Windows\system32\server\vfp9renu.dll””.
Error: Failed system command “C:\Windows\system32\regsvr32.exe /s “C:\Windows\system32\server\vfp9renu.dll””.
Error: Application has returned exit code “4”.
Successfully deleted file “C:\Users\ADMINI~1\AppData\Local\Temp\COG_2\server\vfp9renu.dll”.
Successfully set value “C:\Windows\system32\server\vfp9renu.dll” for key “HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\SharedDLLs”.
Successfully copied file “C:\Users\ADMINI~1\AppData\Local\Temp\COG_2\server\vfp9t.dll” to file “C:\Windows\system32\server\vfp9t.dll”.
Self registration of file using command line “C:\Windows\system32\regsvr32.exe /s “C:\Windows\system32\server\vfp9t.dll””.
Error: Failed system command “C:\Windows\system32\regsvr32.exe /s “C:\Windows\system32\server\vfp9t.dll””.
Error: Application has returned exit code “3”.
Successfully deleted file “C:\Users\ADMINI~1\AppData\Local\Temp\COG_2\server\vfp9t.dll”.
Successfully set value “C:\Windows\system32\server\vfp9t.dll” for key “HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\SharedDLLs”.
Successfully deleted file “C:\Users\ADMINI~1\AppData\Local\Temp\COG_2\vers\vfp-winx64h-app-9.0.2120.0-inst.ver”.
Successfully deleted file “C:\Users\ADMINI~1\AppData\Local\Temp\COG_2\vers\vfp-winx64h-app-inst.ver”.
Successfully added value “1” for key “VFP” under section “Component List” in file “C:\Program Files\ibm\cognos\ccr_64\uninstall\uninst.ini”.

Install component: VFPCOM
Component Version: VFPCOM-AW64-ML-RTM-9.0.2123.0
File “C:\Users\ADMINI~1\AppData\Local\Temp\COG_2\server\vfpcom.dll” successfully extracted.
File “C:\Users\ADMINI~1\AppData\Local\Temp\COG_2\vers\vfpcom-winx64h-app-9.0.2123.0-inst.ver” successfully extracted.
File “C:\Users\ADMINI~1\AppData\Local\Temp\COG_2\vers\vfpcom-winx64h-app-inst.ver” successfully extracted.
Successfully copied file “C:\Users\ADMINI~1\AppData\Local\Temp\COG_2\server\vfpcom.dll” to file “C:\Windows\system32\server\vfpcom.dll”.
Self registration of file using command line “C:\Windows\system32\regsvr32.exe /s “C:\Windows\system32\server\vfpcom.dll””.
Error: Failed system command “C:\Windows\system32\regsvr32.exe /s “C:\Windows\system32\server\vfpcom.dll””.
Error: Application has returned exit code “3”.
Successfully deleted file “C:\Users\ADMINI~1\AppData\Local\Temp\COG_2\server\vfpcom.dll”.
Successfully set value “C:\Windows\system32\server\vfpcom.dll” for key “HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\SharedDLLs”.
Successfully deleted file “C:\Users\ADMINI~1\AppData\Local\Temp\COG_2\vers\vfpcom-winx64h-app-9.0.2123.0-inst.ver”.
Successfully deleted file “C:\Users\ADMINI~1\AppData\Local\Temp\COG_2\vers\vfpcom-winx64h-app-inst.ver”.
Successfully added value “1” for key “VFPCOM” under section “Component List” in file “C:\Program Files\ibm\cognos\ccr_64\uninstall\uninst.ini”.

Above error in log file show that the COM objects for Cognos Controller was not installed. Uninstall cognos controller and do the installation again.

 

Error: Failed to retrieve environment variable “JAVA_HOME”.
Error: Processing XML file “C:\Program Files\ibm\cognos\ccr_64\bin\wct\ccrconfigwebserver.xml” returned errors. View the file “C:\Program Files\ibm\cognos\ccr_64\instlog\isconfig-CNTRL-20240315_1517.log” for details.
Successfully added value “1” for key “CCRWEBSERVER_VERC” under section “Component List” in file “C:\Program Files\ibm\cognos\ccr_64\uninstall\uninst.ini”.

Above error is common, but should not affect the software performance.

 

Error: Processing XML file “C:\Program Files\ibm\cognos\ccr_64\frontend\fcmconfigfrontendserver.xml” returned errors. View the file “C:\Program Files\ibm\cognos\ccr_64\instlog\isconfig-CNTRL-20240315_1517.log” for details.
Successfully added value “1” for key “CCRFCMFRONTEND_VERC” under section “Component List” in file “C:\Program Files\ibm\cognos\ccr_64\uninstall\uninst.ini”.

Above error we have no clue what it is.

 

Run the upgrade/fix pack from folder like C:\install\up_cntrl_winx64h_11.0.1200.36_ml.tar\winx64h\issetup.exe

Click Next

Click Next

Select same path as before and click Next

Click Next

Wait

Click Finish.

Reboot the windows server.

Check the cmplst.txt file that the correct controller version is installed:

LICENSE_CONTROLLER_version=LICENSE_CONTROLLER-AW64-ML-RTM-11.0.1.2-0
LICENSE_CONTROLLER_name=IBM Cognos 8 License
SWTAG_NP_CONTROLLER_version=SWTAG_NP_CONTROLLER-AW64-ML-RTM-11.0.1.5-0
SWTAG_NP_CONTROLLER_name=SWTAG CONTROLLER – Non-Production
CONTRL_version=CCR-AW64-ML-RTM-11.0.1.203-0
CONTRL_name=IBM Cognos Controller
LICENSE_FP_CONTROLLER_version=LICENSE_FP_CONTROLLER-AW64-ML-RTM-10.4.2200.4-0
LICENSE_FP_CONTROLLER_name=IBM Cognos License
[Product Update Information]

CONTRL_UPDATE_version=CCR-AW64-ML-RTM-11.0.1200.36-0
CONTRL_UPDATE_name=IBM Cognos Controller Update
[Main Applications]

Copy the sqljdbc42.jar driver to folders like C:\Program Files\ibm\cognos\ccr_64\server\FAP\lib and C:\Program Files\ibm\cognos\ccr_64\server\integration and C:\Program Files\ibm\cognos\ccr_64

Start Cognos Configuration and setup the values needed, start with the database connection to your Cognos Controller database.

See more details in how to setup old cognos controller here https://www.ibm.com/support/pages/node/213339

 

The provider should be SQLNCLI11.1 and data source is the name of the windows database server. You should use a db server name and not ip address.

Click test connection, click save icon, and then click run icon – to start the DBCONVERT program. Here you need to upgrade your Cognos Controller database to the new version.  https://www.ibm.com/support/pages/ibm-cognos-controller-builds-ccr-name-and-database-version

Updater Kit Build FCM Web Client (classic) CCR Version DB Version Comment
11.0.1200.1015 11.0.1200.55 11.0.1200.10 11.0.1200.8 11.0.1.2 IF2 1015
11.0.1200.1002 11.0.1200.37 11.0.1200.10 11.0.1200.8 11.0.1.2 IF1 1015 Available on Passport Adv. (full installation)
11.0.1200.36 11.0.1200.28 11.0.1200.10 11.0.1200.8 11.0.1 FP2 1015 Generally available (FixCentral)
11.0.1000.8 11.0.1000.6 11.0.1000.1 11.0.1000.2 11.0.1 FP1 1013 Generally available (FixCentral)
11.0.1.203 11.0.1.148 11.0.1.144 11.0.1.117 11.0.1 1012 Available on Passport Adv. (full installation)
11.0.0.1009 11.0.0.39 11.0.0.93 11.0.0.22 11.0.0.0 IF1 1011 Generally available (FixCentral)
11.0.0.106 11.0.0.34 11.0.0.85 11.0.0.17 11.0.0.0 RTM 1011 Available on Passport Adv. (full installation)
10.4.2200.222 10.4.2200.228 10.4.2200.79 10.4.2200.77 10.4.2.2 IF9 1010

 

If you have a demand for TLS 1.2 in your Windows environment or run old version of Windows, then you can get a error like below when you click “connect”.

** ERROR: com.microsoft.sqlserver.jdbc.SQLServerException: Drivrutinen kunde inte upprätta en säker anslutning till SQL Server med SSL-kryptering (Secure Sockets Layer). Fel: “Unexpected rethrowing”.

Can it be that SQL Server 2005 that uses 3DES algorithm in TLS connection and with new versions of JRE, this algorithm is disabled, Or the database is an older database (like SQL Server 2014) which does not allow higher security standards like TLS 1.2.

Then You need to start DBCONV program from a bat file with this settings (save it in a text file called DBCONV.BAT and run it) :

https://www.ibm.com/support/pages/how-configure-force-controller-use-tls-12 


"C:\Program Files\IBM\cognos\ccr_64\bin\jre\8.0\bin\java.exe" -cp com.ibm.cognos.ccr.dbconv.ui.DbConvGUI"C:\Program Files\ibm\cognos\ccr_64\bin64\jre\8.0\bin\javaw.exe" -Dcom.ibm.jsse2.overrideDefaultTLS=true -Dcom.ibm.jsse2.overrideDefaultProtocol=TLSv12 -cp "C:\ProgramFiles\ibm\cognos\ccr_64\DbConv.jar";"C:\Program Files\ibm\cognos\ccr_64\server\integration\antlr-3.2.jar";"C:\Program Files\ibm\cognos\ccr_64\server\integration\ccr-common.jar";"C:\Program Files\ibm\cognos\ccr_64\server\integration\ccr-integration-server.jar";"C:\Program Files\ibm\cognos\ccr_64\server\integration\ccr-integration.jar";"C:\Program Files\ibm\cognos\ccr_64\server\integration\ccr-xml.jar";"C:\Program Files\ibm\cognos\ccr_64\server\integration\cglib-nodep-2.1_3.jar";"C:\Program Files\ibm\cognos\ccr_64\server\integration\commons-beanutils-1.8.3.jar";"C:\Program Files\ibm\cognos\ccr_64\server\integration\commons-beanutils-bean-collections-1.8.3.jar";"C:\Program Files\ibm\cognos\ccr_64\server\integration\commons-beanutils-core-1.8.3.jar";"C:\Program Files\ibm\cognos\ccr_64\server\integration\commons-cli-1.1.jar";"C:\Program Files\ibm\cognos\ccr_64\server\integration\commons-codec-1.8.jar";"C:\Program Files\ibm\cognos\ccr_64\server\integration\commons-collections-3.2.1.jar";"C:\Program Files\ibm\cognos\ccr_64\server\integration\commons-dbcp-1.2.2.jar";"C:\Program Files\ibm\cognos\ccr_64\server\integration\commons-io-2.4.jar";"C:\Program Files\ibm\cognos\ccr_64\server\integration\commons-lang-2.3.jar";"C:\Program Files\ibm\cognos\ccr_64\server\integration\commons-logging-1.1.1.jar";"C:\Program Files\ibm\cognos\ccr_64\server\integration\commons-pool-1.3.jar";"C:\Program Files\ibm\cognos\ccr_64\server\integration\db2jcc.jar";"C:\Program Files\ibm\cognos\ccr_64\server\integration\DBConv.jar";"C:\Program Files\ibm\cognos\ccr_64\server\integration\icu4j-4_8_1_1.jar";"C:\Program Files\ibm\cognos\ccr_64\server\integration\jsr173_1.0_api.jar";"C:\Program Files\ibm\cognos\ccr_64\server\integration\jython.jar";"C:\Program Files\ibm\cognos\ccr_64\server\integration\log4j-1.2.8.jar";"C:\Program Files\ibm\cognos\ccr_64\server\integration\ngtm1api.jar";"C:\Program Files\ibm\cognos\ccr_64\server\integration\ojdbc6.jar";"C:\Program Files\ibm\cognos\ccr_64\server\integration\resolver.jar";"C:\Program Files\ibm\cognos\ccr_64\server\integration\spring.jar";"C:\Program Files\ibm\cognos\ccr_64\server\integration\sqljdbc42.jar";"C:\Program Files\ibm\cognos\ccr_64\server\integration\stringtemplate-3.2.jar";"C:\Program Files\ibm\cognos\ccr_64\server\integration\xbean.jar";"C:\Program Files\ibm\cognos\ccr_64\server\integration\xbean_xpath.jar";"C:\Program Files\ibm\cognos\ccr_64\server\integration\xmlbeans-qname.jar";"C:\Program Files\ibm\cognos\ccr_64\server\integration\xmlpublic.jar" com.ibm.cognos.ccr.dbconv.ui.DbConvGUI

 

If this is a new empty database – you need to click Create DB (this will erase the database, and create a empty controller database).

Then do a upgrade of the cognos controller database.

You should get to above picture without errors, if the upgrade of the cognos controller database was successful.

Click Close

 

Setup of IIS Manager:

  1. In the Internet Information Services (IIS) Manager,  select Application Pools.
  2. Create a new Controller application pool and then from the Actions pane, select Advanced Settings.
  3. Set the .Net CLR Version to v.4.0.
  4. Set Enable 32-Bit Applications to False.
  5. Set Identity to LocalSystem and time-out to 600.
  6. Click OK.
  7. If you do not have CA11 installed, you can point the ibmcognos virtual directory to a empty folder like webcontent.
  8. Expand Sites and under your web site, create the following virtual directories as shown in the table. If you are installing on the same machine as IBM Cognos Analytics, create the virtual directories under the ibmcognos virtual directory.

    Alias
    Location
    controller controller_install_location\ccrvdir
    controllerbin controller_install_location\webcontent\ccr
  9. Select the controller virtual directory.
  10. double click on HTTP redirect. 
  11. Select Redirect Requests to this destination and enter the following path:
    /controllerbin/app.publish/CCR.application

  12. Click Apply.
  13. Right-click your web site and click Add Application.
    1. Set Alias to ControllerServer.
    2. Set Application pool to Controller.
    3. In the PhysicalPath field, enter controller_install_location/ControllerProxyServer.

    4. Click OK.
  14. When you only use native login in Cognos Controller, keep the authentication in IIS to Anonymous Authentication.

 

On the Cognos Controller server Open C:\Program Files\IBM\cognos\ccr_64\ControllerProxyServer\Web.config and add

<add key="ccrReports" value="http://[servername]:9082/fcm.reports/report" />

 

Ensure you use space and not tabs inside the config file. Change the localhost settings to the servername in cognos controller configuration, so the client program can find the cognos controller server.

Go to folder C:\Program Files\ibm\cognos\ccr_64\fcmweb\wlp\etc\
Open the file server.env inside notepad++
Add the following line:
JAVA_HOME=C:/Program Files/IBM/cognos/ccr_64/fcmweb/jre

Save the changes to the file.

 

Setup of Cognos Configuration:

Use FQDN server name instead of only the server name.

Above should you not change, it is configured from the report server settings.

Above you must fill out with the full server and domain name.

Start the batch service in Cognos Controller.

https://www.ibm.com/support/pages/summary-description-windows-services-ibm-cognos-controller-typically-found-controller-application-server

 

 

When you run a report in Cognos Controller 11 and get a error like:

HTTP Error 404.0 – Not Found

The resource you are looking for has been removed, had its name changed, or is temporarily unavailable.

The value in cognos controller configuration report server (http://localhost/ibmcognos/cgi-bin/cognos.cgi) is pointing to a not working website server. Try to change it to http://cognosanalyticsserver/ibmcognos/bi/v1/disp and ensure that all IIS functions are installed in the Web Server.

HTTP Error 405.0 – Method Not Allowed

The page you are looking for cannot be displayed because an invalid method (HTTP verb) is being used.

Then you maybe are pointing to a none existent CA11 server in cognos controller configuration report server section. Add the “ccrReports” value in web.config to solve it (this will use the Cognos Controller internal report server).

We can’t open this file

Something went wrong.

 

This can be that we are using a wrong database connection, try to connect to a other SQL version than 2016.

If you do not have a adobe pdf viewer installed on your laptop. Add the value <add key=”useInternalPdfViewer” value=”true” /> to the servers web.config file. Then cognos controller should use its internal pdfviewer.

You have to reboot the windows server, for the service FCMREPORTS to get any changes to the web.config file to take affect.

You get above error when you run a report in Cognos Controller, when the java is not correct point out in the server.env file for IBM Cognos Controller Reports.

 

Setup of connection to Cognos Analytics:

If you use CA11 for authentication, then in cognos configuration enter the Report Server to http://CAservername.domain.com/ibmcognos/bi/v1/disp and Dispatcher URI to http://CAservername.domain.com:9300/p2pd/servlet/dispatch

You need to create the roles Controller Administrators and Controller Users in CA11 if it is a new installation, then add the users that should use cognos controller to above roles. Follow IBM documentation for more exact steps.

And The Controller Administrators role must be a member of the Controller Users role in Cognos Analytics.

Setup of the Cognos Controller Client:

To install the cognos controller client copy the file ccrlocalclient64.msi to a local folder (c:\install) from C:\Program Files\ibm\cognos\ccr_64\webcontent\ccr folder on the the windows server.

Create a setupccr.bat file with this content:

msiexec.exe /qb /i CCRLocalClient64.msi WSSURL=”http://controllerservername.domain.com/ibmcognos/controllerserver”

Run the bat file as administrator to install the cognos controller client silent.

After you login to Cognos Controller client, you can check the version in help menu – system info.

If you get a error like WebView is missing, then you must install WebView on the client from https://developer.microsoft.com/en-us/microsoft-edge/webview2/?form=MA13LH#download  to get the Help menu to work properly inside cognos controller client.

Under Evergreen Standalone Installer, click the x64 download button.

Download and run the installer file MicrosoftEdgeWebView2RuntimeInstallerX64.exe

If you when you test menu – maintain – jobs – define, get a error like this:

com.ibm.cognos.ccr.integration.IntegrationFacadeException: org.eclipse.core.runtime.adaptor.EclipseStarter failed!
at com.ibm.cognos.ccr.integration.IntegrationServiceAdapter$MyEclipseStarter.run(IntegrationServiceAdapter.java:168)
at java.lang.Thread.run(Thread.java:825)
Caused by: com.ibm.cognos.ccr.common.FatalException: Failed to launch!
at com.ibm.cognos.ccr.integration.application.internal.IntegrationApplication.start(Unknown Source)
at org.eclipse.equinox.internal.app.EclipseAppHandle.run(EclipseAppHandle.java:196)
at org.eclipse.core.runtime.internal.adaptor.EclipseAppLauncher.runApplication(EclipseAppLauncher.java:134)
at org.eclipse.core.runtime.internal.adaptor.EclipseAppLauncher.start(EclipseAppLauncher.java:104)
at org.eclipse.core.runtime.adaptor.EclipseStarter.run(EclipseStarter.java:388)
at com.ibm.cognos.ccr.integration.IntegrationServiceAdapter$MyEclipseStarter.run(IntegrationServiceAdapter.java:166)
… 1 more
Caused by: com.ibm.cognos.ccr.common.NoSuchModelException: Unable to find instance of model of type: configproperty[ConfigPropertyModel]

Check if it works better if you use a newer SQL server version, that can communication with the TLS security that the java parts need.

You can try to setup all this:

https://www.ibm.com/support/pages/how-configure-force-controller-use-tls-12

 

To setup Controller FAP, there are a few new steps, that is outlined here that you must follow:

https://www.ibm.com/docs/en/cognos-controller/11.0.1?topic=administration-fap-web-configuration

 

More Information:

https://www.ibm.com/docs/en/cognos-controller/11.0.1?topic=icclo-installing-configuring-cognos-controller-one-computer

https://www.ibm.com/support/pages/which-sql-collations-are-supported-controller

https://www.ibm.com/support/pages/how-enable-local-pdf-rendering-when-running-standard-reports-enabling-pdfviewer-using-parameter-useinternalpdfviewer

https://simonlearningsqlserver.wordpress.com/tag/changetable/

https://www.ibm.com/support/pages/server-not-reachable-or-still-initializing-faulting-application-path-cprogram-filesibmcognosccr64frontendprunsrvexe-launching-controller-web-caused-corrupt-configjs-file

https://github.com/microsoft/mssql-jdbc/releases/tag/v10.2.0

https://www.ibm.com/support/pages/how-configure-force-controller-use-tls-12

https://www.ibm.com/support/pages/how-install-deploy-controller-client-silently-without-user-input

 

 

Product:
Microsoft Power BI desktop

Issue:

How select all data from a year, when you have a date column in your table?

Solution:

Inside PowerBI you can enter formulas like this;

to select last year

= Table.SelectRows(#"Changed Type1", each Date.IsInPreviousYear([Datum]))

 

to select specific year

= Table.SelectRows(#"Changed Type1", each Date.Year([Datum])=2022)

 

to select all dates before 2022


= Table.SelectRows(#"Changed Type1", each [Datum] < #date(2022, 1, 1))

 

 

More Information:

https://learn.microsoft.com/en-us/powerquery-m/date-isinpreviousyear

How to Calculate Year to Date and Prior YTD in Power BI

https://thedatalabs.org/how-to-get-the-same-period-last-year-to-date-in-power-bi/