by ·Comments Off on How skip elements that are part of a subset

Product:

TM1

Microsoft Windows 2019 server

Issue:

How do i skip elements in a list/view in the data tab from a subset?

Solution:

If you have a subset named “DonaldsCoffe” that contain the stores-name that you want not to be transferred used in your data tab, in the TM1 TI process.
Use below statement to check if the element exist in the subset, and if it does skip that data.

IF ( DIMIX ('DonaldsCoffe',  vElementName) <> 0); 

Itemskip;

ENDIF;

In case you use =0 in above IF statement, that is that you will skip all elements that are not in the list.

DIMIX returns the position of the element in the subset list. 0 (zero) means the element is not in the list.

The comparison operators compare values in the formula portion of a rule calculation statement.

Operator

Meaning

>

Greater than

<

Less than

>=

Greater than or equal to

<=

Less than or equal to

=

Equal to

<>

Not equal to

To compare two string values, insert the @ symbol before the comparison operator, as in the following example:

IF (‘A’ @= ‘B’,0,1) yields the number 1.

 

More information:

http://tm1sir.blogspot.com/

https://www.tm1forum.com/viewtopic.php?t=15927

https://www.tm1forum.com/viewtopic.php?t=11902

https://quebit.com/askquebit/IBM/ibm-planning-analytics-dimix-function-when-and-how-to-use-it/

https://www.ibm.com/docs/en/planning-analytics/2.0.0?topic=functions-dimix

https://exploringtm1.com/operators-tm1-ti-if-statements/

https://exploringtm1.com/if-tm1-ti-function-use-syntax/

by ·Comments Off on How add compare to Notepad++

Product:
Notepad++
Microsoft Windows 2019

Issue:

How do i add compare function to Notepad++?

Solution:

Start Notepad++

Go to plugin menu and select Plugin Admin.

Enter Compare to search for the plug in. Mark Compare in the list and click on install icon.

Click Yes, and Notepad++ will restart after it have downloaded from internet the compare files and put them in folder C:\Program Files\Notepad++\plugins\ComparePlugin

If the server does not have access to internet, copy above 3 files from a working installation, and add them to the plugins folder of your Notepad++ installation.

ComparePlugin.dll
\ComparePlugin\sqlite3.dll
\ComparePlugin\git2.dll

Then you can in Notepad++ use Plugins – Compare – Compare, to get a quick listing of the different rows between the two latest (on the right) open files.

If you use TM1, you can add support for TM1 syntax with download of a tm1.zip file , and unzip it and place the tm1.xml in folder C:\temp. Then from inside Notepad++ go to Languages – User Defined Language -Define your language.
https://npp-user-manual.org/docs/user-defined-language-system/

In the dialog, click import, and select the above tm1.xml file. Then restart Notepad++.

You should now have a new selection TM1 at the bottom of the languages menu.

You can create yourself what words should highlight or be suggested in the User Defined Language dialog.

 

More information:

https://www.wimgielis.com/tm1_tm1andnotepad++_EN.htm

https://www.tm1forum.com/viewtopic.php?t=15927

https://www.tm1forum.com/viewtopic.php?f=3&t=7540

https://www.tm1forum.com/viewtopic.php?t=4248

https://exploringtm1.com/notepad-for-tm1-rules-ti-updated/

by ·Comments Off on Copy only folders

Product:
Microsoft Windows 2019

Issue:

How copy only a folder structure and not the files therein?

Solution:

Use robocopy:

robocopy "source"  "destination" /e /xf *

Above will copy folder structure from source to destination folder, without the security.

/E copy subdirectories, including Empty ones.
/XF file [file]… eXclude Files matching given names/paths/wildcards.

Add /SEC and the security for the file is also copied.

/SEC Copy files with SECurity (equivalent to /COPY:DATS). Useful if you just want to copy over security changes.

More Information:

https://learn.microsoft.com/en-us/windows-server/administration/windows-commands/robocopy

https://social.technet.microsoft.com/wiki/contents/articles/1073.robocopy-and-a-few-examples.aspx

https://www.pdq.com/blog/hitchhikers-guide-to-robocopy/

https://plataformaremota.wordpress.com/support/use-robocopy-to-migrate-windows-shares/

by ·Comments Off on How add user who run SP to a table

Product:

Microsoft SQL server 2019

Issue:

How create a store procedure, than when run will add username and date to a row?

Suggested solution:

Paste below code in SQL management studio and edit it to your needs:

CREATE PROCEDURE [dbo].[A_AddDataToTable] 
AS
BEGIN
---- create the variables
DECLARE @TableName as nvarchar(50)
DECLARE @SqlCode as nvarchar(200)
DECLARE @TimeValue as nvarchar(50)
DECLARE @Userman as nvarchar(50)

---- enter the name of your table below instead of Donald
SET @TableName = 'Table_'+'Donald'

BEGIN
---- check if the table exist, then not create it
IF NOT EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_NAME = @TableName) 

---- fill variable with the SQL code to create the table
set @SqlCode=''
set @SqlCode= @SqlCode + 'CREATE TABLE ' + @TableName + ' '
set @SqlCode= @SqlCode + ' (Rowid int IDENTITY(1000, 1) PRIMARY KEY, '
set @SqlCode= @SqlCode + ' ProUser nvarchar(50) , '
set @SqlCode= @SqlCode + ' ProDate nvarchar(50) ) ;'


---- execute the code in the string
EXEC sp_executesql @SqlCode

END
---- every time the processes is run, do below
---- add a row with date and username

---- get the values for date
SELECT @TimeValue = convert(varchar, getdate(), 0)
---- get the name of the user who run the process
SELECT @Userman = CURRENT_USER

---- build the string of values
set @SqlCode=''
set @SqlCode= @SqlCode + 'INSERT INTO '+ QUOTENAME(@TableName) +' (ProUser,ProDate) ' 
set @SqlCode= @SqlCode + 'VALUES ( ''' + (@UserMan) + ''' , ''' + (@TimeValue) + ''' ) '

-- execute the code in the string
EXEC sp_executesql @SqlCode

END


The result in the table will be like this, after a few run of the SP.

 

More Information:

https://www.sqlshack.com/introduction-to-sp_executesql-stored-procedure-with-examples/

https://codingsight.com/10-sp_executesql-gotchas-to-avoid-for-better-dynamic-sql/

by ·Comments Off on Can not find /content/package[@name=’Frango on Collinsd’]/model

Product:
Cognos Controller 10.4.2
Microsoft Windows 2012 Server

Issue:

When run a standard report, the BI session start, but you get a error that you do not have rights to the package.

QE-DEF-0157

/messageString></message></messages> http://www.ibm.com/xmlns/prod/cognos/reportService/202004/.sessionRSV-SRV-0042 Spåra bakåt:RSReportService.cpp(734): QFException: CCL_CAUGHT: RSReportService::processImpl()RSReportServiceMethod.cpp(254): QFException: CCL_RETHROW: RSReportServiceMethod::process(): asynchRunSpecification_RequestRSASyncExecutionThread.cpp(887): QFException: RSASyncExecutionThread::checkExceptionRSASyncExecutionThread.cpp(331): QFException: CCL_CAUGHT: RSASyncExecutionThread::runImpl(): asynchRunSpecification_RequestRSASyncExecutionThread.cpp(932): QFException: CCL_RETHROW: RSASyncExecutionThread::processCommand(): asynchRunSpecification_RequestRSRequest.cpp(1656): QFException: CCL_THROW: RSRequest::executeInteractivePrompting()RSQueryMgr.cpp(840): QSModelNotFound: CCL_RETHROW: RSQueryMgr::getListIteratorRSQueryMgr.cpp(912): QSModelNotFound: CCL_RETHROW: RSQueryMgr::getResultSetIteratorRSQueryMgr.cpp(1020): QSModelNotFound: CCL_RETHROW: RSQueryMgr::createIteratorRSQueryMgrBasic.cpp(296): QSModelNotFound: CCL_RETHROW: RSQueryMgrBasic::executeRsapiCommandRSQueryMgrBasic.cpp(669): QSModelNotFound: CCL_THROW: RSQueryMgrBasic::processExceptionRSQueryMgrExecutionHandlerImpl.cpp(167): QSModelNotFound: CCL_RETHROW: RSQueryMgrExecutionHandlerImpl::execute()RSFaultHandler.cpp(193): QSModelNotFound: RSFaultHandler::handleNonCriticalExceptionQFSSession.cpp(1190): QSModelNotFound: CCL_RETHROW: QFSSession::ProcessDoRequest()QFSSession.cpp(1188): QSModelNotFound: CCL_CAUGHT: QFSSession::ProcessDoRequest()QFSSession.cpp(723): QSModelNotFound: CCL_RETHROW: QFSSession::ProcessDoRequest()QFSXQEModelChecker.cpp(90): QSModelNotFound: CCL_RETHROW: QFSXQEModelChecker::IsModelPointingToTheNewStack()Source/QECMAuthentication_4.cpp(6149): QSModelNotFound: CCL_THROW: QE

Solution:

The Cognos Controller server have lost the knowledge of that the package name is “Controller”, go into the Cognos Controller configuration.

Go to report server tab.

Ensure the package name is Controller.

And press SAVE anyway to ensure the settings are saved.

Exit Cognos Controller Configuration.

Start Cognos Controller Client and test the standard report again.

More Information:

https://www.ibm.com/support/pages/qe-def-0157-model-abcdefgh-does-not-exist-when-trying-run-standard-report-caused-renaming-package-controller-configuration

 

by ·Comments Off on Adobe open and tell the pdf is not a readable file

Product:
Cognos Controller 10.4.2 fix pack 2
CONTRL_UPDATE_version=CCR-AW64-ML-RTM-10.4.2200.70-0
CONTRL_UPDATE_name=IBM Cognos Controller Update
Microsoft Windows 2019 server

Issue:

When run a standard report in Cognos Controller program, the adobe program starts up and give an error message.

or error like this when open the pdf report. That is if the report is shown both in cognos controller and in a separate adobe reader.

 

Solution:

Change the Cognos Controller server to use the internal pdf viewer.

Steps:
1. Obtain a short amount of downtime (nobody logged onto Controller)
2. Logon to the Controller application server
3. Browse to the folder C:\Program Files\ibm\cognos\ccr_64\ControllerProxyServer
4. As a precaution, 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" />
Save the file, and restart the server to test again.
More Information:

by ·Comments Off on How show standard report in HTML?

Product:
Cognos Controller 10.4.2 fix pack 2
CONTRL_UPDATE_version=CCR-AW64-ML-RTM-10.4.2200.70-0
CONTRL_UPDATE_name=IBM Cognos Controller Update
Microsoft Windows 2019 server

Issue:

After the upgrade to the controller fix pack, how do i change to get standard reports to show in HTML as they did before?

Before it looked like this:

after upgrade it look like this:

Solution:

You don’t. As the use of WebView2 from Cognos Controller 10.4.2 fp1 there is only possible to see reports in PDF or XLS.

 

Other information:

Change to use Cognos Controller own pdf viewer:

Steps:
1. Obtain a short amount of downtime (nobody logged onto Controller)
2. Logon to the Controller application server
3. Browse to the folder ‘ControllerProxyServer’
  • TIP: By default this is located here: C:\Program Files\ibm\cognos\ccr_64\ControllerProxyServer
4. As a precaution, 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” />
To change the order of report generation in Cognos Controller client:
The following must be done on a per-user basis:

  1. Launch Controller
  2. Click ‘Maintain – Installation – Local Preferences
  3. Add a new key called ‘StandardReportPreviewFormat‘, with a value of either ‘html‘ or ‘pdf‘ or ‘xls‘ or ‘xml‘
  4. Click ‘save’

NOTE: If you change the setting so that the default report is ‘pdf’, then be aware that the report will take longer to view (longer to initially display). This is because there is more workload that the Controller application/report server must perform, in order to generate a ‘pdf’ report (compared with the default ‘html’ format). In other words, the end user(s) may perceive this as a performance loss.

Use the undocumented local preference ‘StandardReportBackProc‘ which will stop the PDF rendition in Cognos Controller client.

Steps:
Each individual end user must perform the following themselves:

1. Launch Controller and logon as normal
2. Click ‘Maintain – Installation – Local Preferences‘
3. Inside the ‘key‘ column, create a new entry called: StandardReportBackProc
4. Click ‘Save’
5. Exit Controller
6. Re-launch Controller and test
  • Inside the corresponding ‘value‘ column, create a new entry: false

https://www.ibm.com/support/pages/how-disable-pdf-generation-standard-reports

https://www.ibm.com/support/pages/standard-report-pdf-output-very-small-full-report-shown-small-portion-top-left-corner-mainly-white-display

Adobe Reader X . to make the pdf show up inside the cognos controller report window:

1. Launch Adobe Reader
2. Click “Edit – Preferences”
3. Click on “Internet” section
4. TICK “Display PDF in browser“

https://www.ibm.com/support/pages/how-change-adobe-reader-client-display-pdf-files-insideoutside-web-browser-use-controller-standard-reports

The Adobe Reader client installs both the ‘main’ PDF reader tool (the full client application) and also a smaller ‘web client’.

  • This Adobe Reader web client is installed as a plugin in Microsoft Internet Explorer
  • By default, this ‘web client’ is what gets activated inside the Cognos report display box.

Unfortunately, in some environments this ‘web client’ suffers from an inability to save more than one PDF file.

https://www.ibm.com/support/pages/cannot-save-more-one-pdf-file-standard-reports-system-reports

https://www.ibm.com/support/pages/ibm-cognos-controller-builds-ccr-name-and-database-version

by ·Comments Off on The server selected protocol version TLS10 is not accepted by client preferences [TLS12]

Product:

Cognos Controller 10.4.2 fix pack 2
CONTRL_UPDATE_version=CCR-AW64-ML-RTM-10.4.2200.70-0
CONTRL_UPDATE_name=IBM Cognos Controller Update
Microsoft Windows 2019 server

Issue:

Inside Cognos Controller Client, when using java menu like jobs – define or command center, you get a error.

Caused by: com.ibm.cognos.ccr.common.FatalException: Failed to launch!

When try to connect to database in DBconv program, you get a error.

org.eclipse.persistence.exceptions.DatabaseException

Internal Exception: java.sql.SQLException: The driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption. Error: “The server selected protocol version TLS10 is not accepted by client preferences [TLS12]”. ClientConnectionId:ab713261-e19e-4c2d-b02b-8e7bce540fae DSRA0010E: SQL State = 08S01, Error Code = 0

Error Code: 0

You can check controller web error in files in folder C:\Program Files\ibm\cognos\ccr_64\fcmweb\wlp\usr\servers\fcm.web\logs

Solution:

The java version that is shipped with Cognos Controller 10.4.2 fp2, is set default to only allow TLS 1.2 communication.

Upgrade SQL server to support TLS 1.2 or as a workaround change the java 8 system to support TLS 1.0 and TLS 1.1

Go into the folder C:\Program Files\ibm\cognos\ccr_64\bin64\jre\8.0\lib\security open the file java.security in notepad++

Change this line:

jdk.tls.disabledAlgorithms=SSLv3, TLSv1, TLSv1.1, RC4, DES, MD5withRSA, DH keySize < 1024, DESede, \
EC keySize < 224, 3DES_EDE_CBC, anon, NULL, DES_CBC

To this line:

jdk.tls.disabledAlgorithms=SSLv3, RC4, DES, MD5withRSA, DH keySize < 1024, DESede, \
EC keySize < 224, 3DES_EDE_CBC, anon, NULL, DES_CBC

by remove the TLSv1, TLSv1.1 value, the system will support TLS 1, TLS 1.1.

Also go into the Controller Web folder, and do the same change.

Open C:\Program Files\ibm\cognos\ccr_64\fcmweb\jre\lib\security\java.security file in notepad++

# In some environments, certain algorithms or key lengths may be undesirable
# when using SSL/TLS. This section describes the mechanism for disabling
# algorithms during SSL/TLS security parameters negotiation, including
# protocol version negotiation, cipher suites selection, signature schemes
# selection, peer authentication and key exchange mechanisms.
#
# Disabled algorithms will not be negotiated for SSL/TLS connections, even
# if they are enabled explicitly in an application.
#
# For PKI-based peer authentication and key exchange mechanisms, this list
# of disabled algorithms will also be checked during certification path
# building and validation, including algorithms used in certificates, as
# well as revocation information such as CRLs and signed OCSP Responses.
# This is in addition to the jdk.certpath.disabledAlgorithms property above.
#
# See the specification of “jdk.certpath.disabledAlgorithms” for the
# syntax of the disabled algorithm string.
#
# Note: The algorithm restrictions do not apply to trust anchors or
# self-signed certificates.
#
# Note: This property is currently used by the JDK Reference implementation.
# It is not guaranteed to be examined and used by other implementations.
#
# Example:
# jdk.tls.disabledAlgorithms=MD5, SSLv3, DSA, RSA keySize < 2048, \
# rsa_pkcs1_sha1
jdk.tls.disabledAlgorithms=SSLv3, TLSv1, TLSv1.1, RC4, DES, MD5withRSA, DH keySize < 1024, DESede, \
EC keySize < 224, 3DES_EDE_CBC, anon, NULL, DES_CBC

# Legacy algorithms for Secure Socket Layer/Transport Layer Security (SSL/TLS)
# processing in JSSE implementation.

Save the file.

Reboot the windows controller server to make the change take effect.

More information:

https://help.mulesoft.com/s/article/Error-The-Server-Selected-Protocol-Version-TLS10-Is-Not-Accepted-by-Client-Preferences-TLS12-After-Upgrading-To-OpenJDK8u292

https://support.microsoft.com/en-us/topic/kb3135244-tls-1-2-support-for-microsoft-sql-server-e4472ef8-90a9-13c1-e4d8-44aad198cdbe

https://www.ibm.com/support/pages/connection-sql-server-fails-when-server-configured-use-tls-12-or-connecting-sql-server-2016

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

by ·Comments Off on could not set system in single user mode

Product:
Cognos Controller 10.4.2
Microsoft Windows 2019 server

Issue:

Scheduled optimization of the cognos controller database does not work from inside the cognos controller program. If you run it manually direct, then it works.

Error message: Session Manager login failed – unable to connect to the remote server

Solution:

When change Cognos Controller to use HTTPS, you must update all files to have it. The Controller Batch Service look into the file CCRBatch.exe.config at D:\Program Files\ibm\cognos\ccr_64\server folder.

Open file in notepad and edit the line WSSurl;

<appSettings>
<add key="numberOfThreads" value="1" />
<add key="dbPollingFrequency" value="20" />
<add key="WSSUrl" value="https://servername.domain.com/ibmcognos/controllerserver" />
</appSettings>

Save the file, reboot the windows server for the change to take affect.

You may need to update this files when you change to HTTPS;

D:\Program Files\ibm\cognos\ccr_64\server\CCRProxy.options

D:\Program Files\ibm\cognos\ccr_64\ControllerProxyServer\web.config

C:\Program Files\ibm\IBM Cognos Controller Local Client\ccr.exe.config

D:\Program Files\ibm\cognos\analytics\templates\ps\portal\variables_CCRWeb.xml

 

 

More Information:

https://www.ibm.com/support/pages/scheduled-database-optimization-fails-message-database-optimization-completed-error-remote-server-url-not-defined

https://www.ibm.com/support/pages/requirement-fill-details-inside-client-distribution-server-and-start-batch-service-controller-configuration-controller-1040-onwards

https://www.ibm.com/support/pages/error-contacting-user-manager-systemexception-security-error-1-when-launching-controller-caused-firewall-between-primarysecondary-servers

https://www.ibm.com/support/pages/how-configure-main-controller-client-ccrexe-use-https-ssl

https://www.ibm.com/support/pages/controller-client-disappears-after-choosing-database-caused-restrict-access-members-built-namespace-set-false-when-security-set-cam-authentication-not-native

by ·Comments Off on controller excel add in is disabled at start of excel

Product:

Cognos Controller 10.4.2 version
Microsoft Windows 2016 server

Issue:

Every time you start data entry, you get a message that excel need to restart or that cognos controller add in has stop working and should be disabled.

Press NO on questions to disable the excel plug in.

Solution:

Upgrade to later version of Cognos Controller or disable the azure information protection plug in.

Open excel.

Go to file – options.

Select Add-ins.

Select com objects and press go.

Uncheck Microsoft Azure Information protection and click OK.

Close Excel.

 

More information:

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

This issue (APAR PH38143) has been addressed in version 10.4.2.1 IF1.
It is recommended to update to that version of IBM Cognos Controller, or higher.

Please contact IBM Technical Support to request access to this specific interim fix (10.4.2.1 IF1) as it is not generally available.

https://www.ibm.com/support/pages/ibm-cognos-controller-builds-ccr-name-and-database-version