by ·Comments Off on Remove all data from table for a year

Product:
Microsoft SQL Azure

Issue:

How remove all rows of data from table when the date column is INT and contain both year and date?

Suggested solution:

In SQL SSMS write like this (to erase all rows with year 2024) :

DELETE FROM [DM].[dbtablename]
where 1=1
and SUBSTRING (( CAST ( [key_dimdate] AS varchar) ), 1 , 4 ) = '2024'

if the key_dimdate format is ‘20240112’ as a INT

 

More Information:

https://www.w3schools.com/sql/func_sqlserver_cast.asp 

https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver16 

https://learn.microsoft.com/en-us/sql/t-sql/functions/substring-transact-sql?view=sql-server-ver16

by ·Comments Off on Install CA12

Product:
Cognos Analytics 12.0.4
Microsoft Windows 2022 server

kit_version=12.0.4-2501300500
Manifest=casrv-manifest-12.0.4-2501300500-winx64h.json
Installer=analytics-installer-3.7.38-win.exe

Issue:

How setup CA12 only for CAM authenticate to be used with Cognos Controller?

Solution:

(This doc describe partly the steps you need to do – please read the IBM documentation for more information)

Install IIS with roles like, Web Server, Application Development, NET extensibility, ASP.NET 4.8 etc

Download and install URL Rewrite Module 2.1 https://www.iis.net/downloads/microsoft/url-rewrite

Download and install ARR 3.0 manually https://www.iis.net/downloads/microsoft/application-request-routing

Create a SQL server database for the content store, with a SQL login as DB-owner; if you are not upgrading a old CA database.

Download the CA12 from https://www.ibm.com/support/pages/downloading-ibm-cognos-analytics-1204

Whats new in CA12 is: https://lodestarsolutions.com/tag/ibm-cognos/

Setup CA on a single server: https://www.ibm.com/docs/en/cognos-analytics/12.0.0?topic=analytics-single-server-installation

If you do a upgrade, add this lines to the file preserve.txt in folder install_location/configuration/preserve.

#TM1 files
templates/ps/portal/variables_TM1.xml
templates/ps/portal/variables_plan.xml
templates/ps/portal/icon_active_application.gif
webcontent/planning.html
webcontent/PMHub.html
webcontent/tm1/web/tm1web.html

#CA files
templates/ps/system.xml
templates/ps/portal/system.xml
templates/ps/portal/variables_CCRWeb.xml

Run the analytics-installer-3.7.38-win.exe to install CA 12.0.4

Select language and click next

Click next

Mark “i accept the terms.. ” and click next

Ensure you install to correct drive and click next

Click Yes

Leave the 3 selected and click next

Click install

Click done

Copy the SQL driver sqljdbc42.jar to folder C:\Program Files\ibm\cognos\analytics\drivers

Start Cognos Configuration and change to FQDN (full qualified domain names) in this places for the server:

 

  • Environment
    • Gateway URI
    • External dispatcher URI
    • Internal dispatcher URI
    • Dispatcher URI for external applications
    • Content Manager URIs
  • Environment > Configuration Group
    • Group contact host
    • Member coordination host
  • Security > Cryptography > Cognos
    • Server common name
    • Subject Alternative Name > DNS names
    • Subject Alternative Name > IP addresses

 

 

Setup the SQL server database connection for Content Store:
Right Click Content Manager and select Delete. Confirm deletion.
Right Click Content Manager > New Resource > Database.
Set the Name; Content store.
Set the Type (Group); Microsoft SQL server database
Set the Database Server and Port number (1433).
Set the User ID and Password for the database.
Set the Database name.

Save the configuration and start IBM Cognos service from Cognos Configuration.

Browse to  http://yourservername:9300/p2pd/servlet  to see if it is up.

If it is a new installation, use the CA_IIS_Config script to create the needed values in IIS.

Open the C:\Program Files\ibm\cognos\analytics\cgi-bin\templates\IIS\CA_IIS_Config.bat in Notepad++

Edit the servername to FQDN

Change SSO to True, and save the file.

Run the CA_IIS_Config.bat script from a DOS prompt as administrator.

https://www.ibm.com/docs/en/cognos-analytics/12.0.0?topic=services-configuring-iis-in-cognos-analytics

Press Y, then check that the URL rewrite looks like they should.

(*) Always use FQDN (not NetBIOS name or IP address) values when configuring the relevant server name.
(*) Always use lowercase characters for all of your URLs (website addresses), for example Rewrite URLs.

Browse to http://yourservername/ibmcognos/  to check that it works.

 

Configure the Authentication Provider.
Right click Authentication Source > New Resource > Namespace.
Set the name. Name should be the same as the domain name.
Set the Type (Group), Active Directory is Default.
Set the Type, leave as blank, the default.

https://www.ibm.com/docs/en/cognos-analytics/12.0.x?topic=server-configuring-active-directory-namespace

In the Explorer window, under Security > Authentication, and select the Active Directory namespace.
Click in the Value column for Advanced properties and then click the edit icon.
In the Value – Advanced properties dialog box, click Add.
In the Name column, type singleSignonOption
In the Value column, type IdentityMapping.
Click OK.

Before you get SSO to work, you need to change “allow anonymous access” to False, inside Cognos Configuration at Security – Authentication – Cognos.

Before you do more adjustments to CA, install the latest Cognos Analytics Fix Pack.

Get the latest fix pack https://www.ibm.com/support/pages/ibm%C2%AE-cognos-analytics-fix-lists

Entitled Bundled Customers use this link Cognos Analytics 12.0.4 IF2 to get access to download Cognos Analytics 12.0.4 Interim Fix 2.

Cognos Analytics Customers use the Fix Central link below.

IBM Cognos Analytics 64-bit 12.0.4 IF2 Windows 04 Feb 2025 Language Independent 17.31 GB FC

Setup of Cognos Fix Pack, should be similar to this steps:

Stop Internet Information Services (IIS) Manager (the Default Website).
Stop all IBM Cognos services through the Services Manager if they are active.
Set all IBM Cognos windows service to manual.
Reboot Windows server.
Back up the content store database.
If your IBM Cognos environment is customized, back up the entire IBM Cognos location.
Go to the location where you downloaded the files.
Run the analytics-installer-3.7.38-win.exe file, to start install of fix pack.
Follow the directions in the installation wizard, installing in the same location as your existing IBM Cognos server components if already present.
Open IBM Cognos Configuration, save the configuration.
Set all IBM Cognos service to automatic. Except below service if they exist on the server:
IBM Cognos Controller Consolidation
IBM Cognos Controller Java Proxy
IBM Cognos Controller User Manager
IBM Cognos FAP Service
Start the Internet Information Services (IIS) Manager (the Default Website).
Reboot the server.
Check that the IBM Cognos service is running, before you try to browse to: http://yourservername/ibmcognos/

 

If you start with server hostnames and later change to FQDN in Cognos Configuration you get error like this:

ERROR com.ibm.bi.rest.RESTClient [Default Executor-thread-12] NA Certificate for <WIN2022PAL> doesn’t match any of the subject alternative names: [192.168.1.106, win2022pal.pacman.local]
javax.net.ssl.SSLPeerUnverifiedException:

You must use FQDN names from the beginning in the Cognos Configuration.

You must in CA create the two user roles, and add the users that should run Cognos Controller to them.

Go to Manage – People – Accounts, select Cognos namespace, and click on new role.

Enter the name Controller Administrators, and create one more role called Controller Users.

For Controller Users click on dots and select properties. Under Members add the users that should be running the Cognos Controller program.

 

Cognos Controller need two roles called ‘Controller Users’ and ‘Controller Administrators’ in CA. It is considered best practice to ensure that all users in Controller are attached to the ‘Controller Users’ role and those defined within Controller as ‘Controller Administrators’ being to the role of the same name. Additionally add the ‘Controller Administrators’ role to the list of members in the ‘Controller Users’ role to ensure that you don’t have to add the administrative users to the ‘Controller Users’ role.

If you still do not get the Roles in CA for Cognos Controller to work (to give you SSO into cognos controller client), try to restore a old Cognos Content store that already contain this roles, so you get the correct role setup.

To add the Cognos Controller parts to IIS, after you have installed CA11.

  1. In the Internet Information Services (IIS) Manager, expand the node with your server name, and select Application Pools.
  2. Select DefaultAppPool 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.
  6. Set Idle Time-Out to 600 minutes.
  7. Click OK.
  8. Expand Sites and under your web site, create the following virtual directories as shown in the table, if they not already exist.
    Alias
    Location
    IBMCognos controller_install_location\webcontent or C:\Program Files\ibm\cognos\analytics\webcontent
    IBMCognos/controller controller_install_location\ccrvdir  (e.g. C:\Program Files\ibm\cognos\ccr_64\ccrvdir )
    IBMCognos/controllerbin controller_install_location\webcontent\ccr ( C:\Program Files\ibm\cognos\ccr_64\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 parent virtual directory (ibmcognos) and click Add Application.
    1. Set Alias to ControllerServer.
    2. Set Application pool to DefaultAppPool.
    3. In the PhysicalPath field, enter controller_install_location/ControllerProxyServer. (C:\Program Files\ibm\cognos\ccr_64\ControllerProxyServer)
    4. Click OK.
  14. Click Apply and click OK.

To make cognos controller talk to CA, you need to change in Cognos Controller configuration;

  • Start IBM Cognos® Controller Configuration using the Run as administrator option.
  • Under Web Services Server > Report Server point the URI to the addresses.

    Configure the following addresses:

    Server
    Value
    Report server URI for Cognos Analytics http://CA_server/bi/v1/disp
    Dispatcher URI for Cognos Analytics http://CA_Server:9300/p2pd/servlet/dispatch

Save and go to Server Authentication and change to CAM Authentication.

Save and go to Client Distribution Server Configuration – this need to be updated, the WSSurl is used by the client program.

Enter the FQDN to the controller server and save.

Then to get Cognos Controller web to use Active Directory from CA, you need to do:

  • In the Cognos BI installation folder, <BI_installation_folder>/templates/ps/portal/, create a file with the name variables_CCRWeb.xml.
  • The content of the file variables_CCRWeb.xml must be as follows: 
    <CRNenv c_cmd="http://{host_name}:{port_number}/#!/CamLogin">
   <cookies> 
      <param name="cam_passport"/>
   </cookies>
</CRNenv>
  • Locate the file com.ibm.cognos.fcm.web.properties in the C:\Program Files\IBM\cognos\ccr_64\fcmweb\wlp\usr\servers\fcm.web folder.
  • Open the file com.ibm.cognos.fcm.web.properties and set the following properties:
    1. biUrl: the URL that the user must go to when CAM authentication is needed. The default location is http://servername:80/ibmcognos/bi/v1/disp.
    2. biDispatchEndpoint: the endpoint to which Controller Web connects to validate CAM users and CAM passports. The default location is
      http://servername:9300/p2pd/servlet/dispatch      .
    3. loginMode: the authentication type. Set it to CAM

Save and reboot the server, to ensure that all works as it should.

(You must also do all the other settings that is for Cognos Controller Web, to make it work, above is only to get the SSO to use CA.)

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

More information:

https://www.ibm.com/docs/en/cognos-analytics/12.0.0?topic=gccs-suggested-settings-creating-content-store-in-microsoft-sql-server

https://www.ibm.com/support/pages/how-can-i-change-collation-controller-database

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

https://www.ibm.com/docs/en/controller/11.1.0?topic=web-cam-authentication

https://www.ibm.com/docs/en/cognos-analytics/12.0.0?topic=essbadscc-enabling-single-signon-between-active-directory-server-cognos-components-use-remote-user

https://www.ibm.com/docs/en/cognos-analytics/12.0.0?topic=gateway-configure-cognos-analytics-your-web-server

https://www.ibm.com/docs/en/cognos-analytics/12.0.x?topic=essbadscc-enabling-single-signon-between-active-directory-server-cognos-components-use-remote-user

https://www.ibm.com/support/pages/how-configure-sso-single-sign-controller-cognos-analytics

https://www.ibm.com/docs/en/controller/11.1.0?topic=web-cam-authentication

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

by ·Comments Off on How change data load to use different gateway?

Product:
Microsoft Power BI service Dataflow

Issue:

How change on-prem gateway from prod to dev?

 

Solution:

You have to click on EDIT for the dataflow.

Then click on EDIT TABLE.

Then on the HOME ribbon find OPTIONS.

Scroll down to Data Load, and select the on-prem gateway you should use in this dataflow.

 

Changing the gateway

To showcase how to change the gateway in a dataflow project, this article uses a query that connects to a local folder as an example.

This query previously used a gateway named “Gateway A” to connect to the folder. But “Gateway A” no longer has access to the folder due to new company policies. A new gateway named “Gateway B” is registered and now has access to the folder that the query requires. The goal is to change the gateway used in this dataflow project so it uses the new “Gateway B.”

Screenshot of a query that has an error message related to the data gateway being unreachable or offline.

To change the gateway:

  1. From the Home tab in Power Query, select Options.

    Screenshot of Options icon and selection in Power Query Home tab.

  2. In the Options dialog box, select Data load, and then select the gateway to use for your project, in this case, Gateway B.

    Screenshot of Project options dialog box with the drop-down menu listing None, Gateway A, and Gateway B.

     Tip

    If there were recent changes to your gateways, select the small refresh icon to the right of the drop-down menu to update the list of available gateways.

  3. After selecting the correct gateway for the project, in this case, Gateway B, select OK to go back to the Power Query editor.

 

To change the dataflow to use a different SQL view for the table of data, go to the navigation icon in the right list, and select a different view from the SQL database, from the list of view in the databases.

 

More Information:

https://learn.microsoft.com/en-us/power-query/change-gateway-dataflow 

by ·Comments Off on List users and there roles in SQL

Product:

Microsoft SQL Azure

Issue:

What user have the db_owner role?

Solution:

Start SSMS and login to your SQL azure database, and run below script (found on internet)

 

SELECT CASE princ.[type]
WHEN 'S' THEN
princ.[name]
END AS [UserName],
CASE princ.[type]
WHEN 'S' THEN
'SQL User'
WHEN 'U' THEN
'Windows User'
END AS [UserType],
princ.[name] AS [DatabaseUserName],
NULL AS [Role],
perm.permission_name AS [PermissionType],
perm.state_desc AS [PermissionState],
obj.type_desc AS [ObjectType], --perm.[class_desc], 
OBJECT_NAME(perm.major_id) AS [ObjectName],
col.[name] AS [ColumnName]
FROM
--database user
sys.database_principals AS princ
LEFT JOIN
--Permissions
sys.database_permissions AS perm
ON perm.grantee_principal_id = princ.[principal_id]
LEFT JOIN
--Table columns
sys.columns AS col
ON col.[object_id] = perm.major_id
AND col.column_id = perm.minor_id
LEFT JOIN sys.objects AS obj
ON perm.major_id = obj.[object_id]
WHERE princ.[type] IN ( 'S', 'U' )
UNION
--List all access provisioned to a sql user or windows user/group through a database or application role
SELECT CASE memberprinc.[type]
WHEN 'S' THEN
memberprinc.[name]
END AS [UserName],
CASE memberprinc.[type]
WHEN 'S' THEN
'SQL User'
WHEN 'U' THEN
'Windows User'
END AS [UserType],
memberprinc.[name] AS [DatabaseUserName],
roleprinc.[name] AS [Role],
perm.permission_name AS [PermissionType],
perm.state_desc AS [PermissionState],
obj.type_desc AS [ObjectType], --perm.[class_desc], 
OBJECT_NAME(perm.major_id) AS [ObjectName],
col.[name] AS [ColumnName]
FROM
--Role/member associations
sys.database_role_members AS members
JOIN
--Roles
sys.database_principals AS roleprinc
ON roleprinc.[principal_id] = members.role_principal_id
JOIN
--Role members (database users)
sys.database_principals AS memberprinc
ON memberprinc.[principal_id] = members.member_principal_id
LEFT JOIN
--Permissions
sys.database_permissions AS perm
ON perm.grantee_principal_id = roleprinc.[principal_id]
LEFT JOIN
--Table columns
sys.columns AS col
ON col.[object_id] = perm.major_id
AND col.column_id = perm.minor_id
LEFT JOIN sys.objects AS obj
ON perm.major_id = obj.[object_id]
UNION
--List all access provisioned to the public role, which everyone gets by default
SELECT '{All Users}' AS [UserName],
'{All Users}' AS [UserType],
'{All Users}' AS [DatabaseUserName],
roleprinc.[name] AS [Role],
perm.permission_name AS [PermissionType],
perm.state_desc AS [PermissionState],
obj.type_desc AS [ObjectType], --perm.[class_desc], 
OBJECT_NAME(perm.major_id) AS [ObjectName],
col.[name] AS [ColumnName]
FROM
--Roles
sys.database_principals AS roleprinc
LEFT JOIN
--Role permissions
sys.database_permissions AS perm
ON perm.grantee_principal_id = roleprinc.[principal_id]
LEFT JOIN
--Table columns
sys.columns AS col
ON col.[object_id] = perm.major_id
AND col.column_id = perm.minor_id
JOIN
--All objects 
sys.objects AS obj
ON obj.[object_id] = perm.major_id
WHERE
--Only roles
roleprinc.[type] = 'R'
AND
--Only public role
roleprinc.[name] = 'public'
AND
--Only objects of ours, not the MS objects
obj.is_ms_shipped = 0
ORDER BY princ.[name],
OBJECT_NAME(perm.major_id),
col.[name],
perm.permission_name,
perm.state_desc,
obj.type_desc; --perm.[class_desc]

More information:

https://www.scarydba.com/ 

by ·Comments Off on weather widget

Product:
Planning Analytics Workspace 98

Microsoft Windows 2019 server

Issue:

How do i get a weather report inside my book?

Solution:

Use one of the free sites that provide plug-ins for things like weather to your website.

Booked.net – book hotel online

Create a crafted picture that is run and updated on there site.
Enter that code to a standalone webpage, and use that URL to the webpage inside PAW.

You have to enter the location, and then grab the code for a picture.

Place the code in a html page, you then reference inside PAW url like:  https://tm1.dk/weather/amsterdam.html

www.booked.net

https://tm1.dk/weather/newyork.html

https://tm1.dk/weather/tokyo.html

https://tm1.dk/weather/losangeles.html

More information:

https://weather.tomorrow.io/widget/

https://weatherwidget.org/

https://weatherwidget.io/

by ·Comments Off on IBM Planning Analytics Local 2.1.9 setup on Windows

Product:
IBM Planning Analytics Local 2.1.9  tm1server = 11.8.02900.8
Microsoft Windows 2022 server

Issue:

How install PAL 2.1.9?

Solution:

Download the latest version from IBM

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

Download only the Windows installation files from the different parts.

IBM Pafe x86 is for you if you have a 32 bit version of excel installed (this the most companies use)

IPA workspace win 2022 is the installation package for PAW for Windows 2022 server.

Before uninstall previous version, take a backup of changed files, like certificate store and samples in C:\Program Files\ibm\cognos\tm1_64\samples\tm1\ folder.

Unzip the packages to get the installation media out, and copy it to a folder on the server.

Run the install from file: analytics-installer-4.0.5-win.exe

Select language and click next

Click next

Click next

Verify the hard drive you are going to install to and click next

Click Yes

Click next

Click Install

Click done

 

To install the TM1 admin service, you must go to a CMD prompt, and move to the bin64 folder:

C:\Program Files\ibm\tm1_64\bin64  and enter command: Tm1admsd -install

Before you install the TM1 instances, check the tm1s.cfg file to have the correct values for: (some must be unique for each application)

IntegratedSecurityMode=5

UseSSL=T

ServerName=Planning Sample

DataBaseDirectory="d:\TM1Data\PlanSamp\data"

LoggingDirectory="d:\TM1Data\PlanSamp\Logfiles"

PortNumber=12345

HTTPPortNumber=12354

ServerCAMURI=http://caservername.domain.com:9300/p2pd/servlet/dispatch

ClientCAMURI=http://caservername.domain.com/ibmcognos/bi/v1/disp

 

https://www.ibm.com/docs/en/planning-analytics/2.1.0?topic=local-planning-analytics-database-configuration-parameters

 

To install a instance to be in Windows service, enter:

tm1sd.exe -install -n "nameofapplication" -z "D:\TM1Data\SData\Config"

Where nameofapplication should be the same as you have entered as ServerName=nameofapplication

Think about the naming of the application will affect how it look in Windows services. Do not use non-english letters in the name.

Click on each TM1 service, and go to properties, and change the settings to Automatic. This to make them start when the server reboots.
To stop a TM1 instance, you have now go to Windows services and right click and select stop. Be aware that big TM1 instances, may crash during stop, and need to recover from log files when started.

If you have enter the wrong name, remove the TM1 instance with command tm1sd.exe -remove

 

Backup all TM1WEB configuration files, before doing any installation.

Run the installation of TM1WEB with file analytics-installer-3.7.37-win.exe

Select language and click next

Click next

Click next

Check the hard drive and click next (note; that tm1web install to the old folder)

Click yes

Click install

Click done

The service is automatic registered as: tm1web  – right click and start all TM1 instances and TM1WEB.

You change the port tm1web are listen to in file : C:\Program Files\ibm\cognos\tm1web\wlp\usr\servers\tm1web\server.xml

Default port is 9510.

You update tuning values in file : C:\Program Files\ibm\cognos\tm1web\webapps\tm1web\WEB-INF\configuration\tm1web_config.xml

Change the values, from your notes of previous installation, and save the file and restart the service.

When you get it to work – it should look like above.  Browse to http://servername:9510/tm1web

The version of the installed TM1 program is now in other txt files than before.

 

(You must install PAW on a separate server, to be able to work with your TM1 instances.)

Please note that Planning Analytics for Microsoft Excel require that the client (laptop) have Microsoft .NET Framework 4.8 installed.

PAL 2.1.9 only support this versions of Windows server e.g. 2022.

PAW 2.1.9 demands Mirantis runtime on Windows to work.

On Linux there is other demands.

More Information:

https://www.pantarhei.at/wp-content/uploads/2024/09/IBM-Planning-Analytics-2.1_Installation-and-Configuration.pdf

https://www.ibm.com/docs/en/planning-analytics/2.1.0?topic=configuration-upgrading-planning-analytics-local-21

https://endoflife.date/windows-server

https://www.ibm.com/docs/en/planning-analytics/2.1.0?topic=rtso-setting-up-tm1-server-run-as-windows-service

https://www.ibm.com/docs/en/planning-analytics/2.1.0?topic=parameters-planning-analytics-spreadsheet-servicestm1-web-configuration

https://www.ibm.com/docs/en/planning-analytics/2.1.0?topic=parameters-configuring-custom-homepage-tm1-web

https://exploringtm1.com/configure-planning-analytics-spreadsheet-services-ssl-certificate/

https://www.ibm.com/docs/en/planning-analytics/2.1.0?topic=parameters-setting-tm1-web-session-timeout

https://www.ibm.com/docs/en/planning-analytics/2.1.0?topic=web-configuring-browsers-cognos-tm1

https://www.ibm.com/docs/en/planning-analytics/2.1.0?topic=security-cognos

https://www.ibm.com/docs/en/planning-analytics/2.1.0?topic=security-configuring-tm1-web-use-cognos

https://www.ibm.com/docs/en/planning-analytics/2.1.0?topic=upal2-upgrading-planning-analytics-workspace-local-from-version-20-21

 

by ·Comments Off on install cc client

Product:
IBM Cognos Controller CONTRL_UPDATE_version=CCR-AW64-ML-RTM-11.1.1002.5-0

Microsoft Windows 2019 server

Issue:

How upgrade the client?

Suggested solution:

This are the steps we do on a laptop to upgrade the IBM cognos controller client program.

In excel the add-in is installed as this:

Some values can be found in the registry at Computer\HKEY_CURRENT_USER\SOFTWARE\Microsoft\Office\Excel

Uninstall the previous version first, from windows control panel. That is in folder C:\Program Files\IBM\IBM Cognos Controller Local Client.

After you have uninstall Cognos Controller, you get this message when you start Excel.exe.  The above folder is gone and the files in it.

When you go into the excel options add-ins you find this message, it looks like the add-in is installed – but it is not.

Should this be removed before installation of the new Cognos Controller client? If the installation works as it should, it will handle it as is.

Run the installation of the cognos controller client from CCRLocalClient64.msi file.

Click Next

Enter the FQDN name to the cognos controller server here and press Next.

Click Next (notice the new folder to the program)

Click Install

Click Finish

The new IBM controller client is installed.

Start the IBM controller program from the start menu.

Select your database and enter your name and password if needed.

When logged in try to only start Excel from inside Cognos Controller client program, by press the excel icon.

And excel start without any controller tool bar – then the com add-in have been disabled.

You need to go to the options – add-ins – com add-ins and mark the above controller link for microsoft excel and press ok.

Restart Excel and Cognos Controller program.

Go into cognos controller and press excel icon – it should start up the Excel with this controller options.

If you look inside Excel options – add-ins it should look like this.

There should only be two controller link for microsoft excel, and they should point to the correct folder.

In many cases when above does not work, it can be error in REG VALUE

[HKEY_CURRENT_USER\SOFTWARE\Microsoft\Office\16.0\Excel\Options]

“OPEN”=”/R \”C:\\Program Files\\IBM\\IBM Controller Local Client\\adxloader64.Controller.ExcelLink.dll\””

Check the registry from a working installation, and compare to the not working installation of Excel.

If you have issues with the Cognos Controller link to excel, do a reinstall of your Microsoft Office program first.

If the installation is done correct, the files should have the same date. There is 282 files in the Controller client folder for version 11.1

 

If you get the error “Cannot run the macro ‘cc.CompareDb’. The macro may not…” when try to open Data Entry form, it is caused by multiple Controller clients being installed. Check that you do not have installed both the old and new ccr.exe on the citrix server. Uninstall all Cognos Controller clients from the computer, and ensure it is clean, before you install the correct version of IBM controller software.

You can in rare circumstance need to search the registry (after you uninstalled cognos controller client) for references to the old version at “IBM Cognos Controller Local Client”. Clean the registry before you install the correct version.

 

If you get a error like this: (then the database connection is not working and the sql username and password is not correct)

System.Web.Services.Protocols.SoapException: Server was unable to process request. —> System.ArgumentException: Login failed for user ‘cognos’.
at Microsoft.VisualBasic.ErrObject.Raise(Int32 Number, Object Source, Object Description, Object HelpFile, Object HelpContext)
at ControllerServerCommon.RaiseErrSrv.RaiseError(String sUser, Int32 lErrNo, String sErrSource, String sErrDesc, String sErrHelpFile, Int32 lErrHelpContext)
at FrServerB.ServerInfo.GetDBRelease(String sGuid, String sUser)
at Cognos.Controller.Proxy.CCRWS.ServerInfo_GetDBRelease(String sGuid, String sUser)
— End of inner exception stack trace —

If you get a error like this when you try to run a report: (You have to go into Excel – options – add-ins and COM add-ins, and mark the controller link for Microsoft Excel, to make it active).

If you try to remove the excel add-in you should get a message like this – only admins can remove the add-in.

If both the excel add- ins are inactive, then nothing happens in the excel. You need to go into both Excel add-ins and COM add-ins and active the controller link for Microsoft Excel.

If you get a error in the Excel add-in dialog like this; (then you have deactivated the add-ins in wrong order)

Exception from HRESULT: 0x800AC472
at Microsoft.Office.Interop.Excel._Application.set_EnableEvents(Boolean RHS)
at Cognos.Controller.ControllerForms12.clsDataEntry.DoLogoff()
at Cognos.Controller.XL.ControllerXL.xllController.ExitXll(Boolean LoggingOff)
at Cognos.Controller.XL.ControllerXL.xllController.DoLogout()

 

More Information:

  • Your client computer needs this:
  • Microsoft Excel for Microsoft 365.
  • Microsoft Edge Runtime.
  • Microsoft .Net 4.5 or later.

https://www.ibm.com/docs/en/controller/11.1.0?topic=installers-installing-stand-alone-controller-client

https://greenshot.org/

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

by ·Comments Off on Unable to load requested view. Displaying home view instead.

Product:
IBM Cognos Controller 11.1 CONTRL_UPDATE_version=CCR-AW64-ML-RTM-11.1.1002.5-0
Microsoft Windows 2022 server

Issue:

When testing Controller Web in a new installation – you get error: Unable to load requested view. Displaying home view instead.  This is when using Native security in Cognos Controller, and installed on a single server.

Solution:

The new version of IBM Controller Web use a different port for the end user, browse to http://controllerservername.domain.com:9085/

then it will work.

You may also need to update the config files, before it will work. The steps to setup controller web are from IBM this

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

 

 

This steps are similar to previous version of Controller Web setup.

Go to the C:\Program Files\ibm\cognos\ccr_64\fcmweb\wlp\etc and open server.env in notepad++

change the line to

JAVA_HOME=C:/Program Files/IBM/cognos/ccr_64/fcmweb/jre

save the file.

Open CMD and go to folder C:\Program Files\ibm\cognos\ccr_64\fcmweb

Run command:

SyncDBConf.bat ..\Data wlp\usr\shared\config\datasources

Go to folder C:\Program Files\ibm\cognos\ccr_64\fcmweb\wlp\usr\servers\fcm.web

Open file com.ibm.fcm.web.properties in Notepad++  ( please note the name: com.ibm.fcm.web.properties )

Add the lines

ccrwsUrl=http://win2022pal/ibmcognos/controllerserver/ccrws.asmx
loginMode=NATIVE

Save the file.

(adjust the path/url to your server setup).

Stop the IBM controller services  – and start them for the changes to take affect.

But you need to do this changes to:

Go to folder C:\Program Files\ibm\cognos\ccr_64\frontend

Open config.js in notepad++

Enter the servername url in this 3 places, use FQDN instead of only servername as I do in the example.

Then further down enter the servername (fqdn), and change the port number on line 71 from 9086 to 9085.

Keep the Apostrophe sign as is.

Go to folder C:\Program Files\ibm\cognos\ccr_64\fcmweb\wlp\usr\servers\fcm.glass

Open server.env in notepad++

Normally you only need to change the c: to d: if you have installed the cognos controller program to the d: drive with default paths.

Save the file.

Open bootstrap.properties in notepad++ if you have change any other files, to use a different port.

Normally you do not need to change above file.

Restart the Windows server and see if it works.

Log files can be found in a folder like C:\Program Files\ibm\cognos\ccr_64\fcmweb\wlp\usr\servers\fcm.web\logs

 

If you get error like: Not Found
The application or context root for this request has not been found: / you are going to the wrong port. The User should browse to this address:

http://[servername]:9085/

From IBM documentation:

 

 

When it works – it should look like this:

More Information:

https://www.ibm.com/docs/en/controller/11.1.0?topic=options-configuring-controller-embedded-report-library 

https://en.wikipedia.org/wiki/List_of_typographical_symbols_and_punctuation_marks 

 

by ·Comments Off on Why does the report pdf also open in adobe?

Product:

IBM Cognos Controller 11.1
Microsoft Windows 2019 server

Issue:

New installation of IBM Controller 11.1, when the client run a standard report in Cognos Controller client program. The EDGE or adobe acrobat is also started and show the PDF file – even the PDF file is show inside the Controller program dialog.

 

Solution:

1. Ensure no one is logged into cognos controller client.

2. Logon to the Controller application server

3. Browse to the folder ‘ControllerProxyServer’. Can be C:\Program Files\ibm\cognos\ccr_64\ControllerProxyServer

4. Create a backup copy of the file: web.config

5. Use Notepad++ to edit the file web.config

6. Locate the section <appSettings>

7. Add the following line into that section:

<add key="useInternalPdfViewer" value="true" />

8. Save the file and restart the service.

 

More information:

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

by ·Comments Off on Unsupported protocolSSL_TLSv2

Product:
Planning Analytics 2.1.7 TM1_version=TM1-AW64-ML-RTM-11.0.7.1-0
Microsoft Windows 2022 server
IBM Planning Analytics Spreadsheet Services

Issue:

After upgrade of tm1web, when start up the Tm1web page in the browser, you get a error:

Unsupported protocolSSL_TLSv2

Solution:

Stop the tm1web service (IBM Planning Analytics Spreadsheet Services)

Go to folder D:\Program Files\ibm\cognos\tm1web\wlp\usr\servers\tm1web

Rename jvm.options to jvm.options.old

Rename jvm.options.new to jvm.options

Start the tm1web service.

 

 

More Information:

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

The parameter -Dcom.ibm.jsse2.overrideDefaultProtocol=TLSv1.2 is needed.