Product:

Planning Analytics 2.0.9.19
Microsoft Windows 2019 Server

Issue:
How start or stop a TM1 instance from command line?

Solution:

There is a bat file in the TM1 installation you can take help from – check the folder : C:\Program Files\ibm\cognos\tm1_64\bin64

startup_tm1s.bat <path to tm1s.cfg file> { <TM1 Server Name> }

Above will start the TM1 if it is installed, otherwise it will install the TM1 instance and start it. Service_tm1s.bat file will both start and stop a TM1 instance.

Write a CMD file that have command of this character to use it;

Here we start TM1 instance FAP, in folder c:\tm1data\FAP\config.

>service_tm1s.bat stop TM1ServerConfigFolder TM1Servername

>service_tm1s.bat start TM1ServerConfigFolder TM1Servername

The downsize is that you need to inform of the path to the TM1 config file.

A bat file with the NET START command is better.

REM Stop the services
NET STOP "FAP" /Y

REM Start the services again
NET START "FAP"

More Information:

https://www.wimgielis.com/tm1_backup_EN.htm 

https://exploringtm1.com/how-to-do-a-daily-or-weekly-restart-a-tm1-model/

https://ecapitaladvisors.com/blog/how-to-set-up-turbo-integrator-chores-to-run-advanced-scheduling/ 

https://www.coretechnologies.com/products/ServiceScheduler/ 

https://support.pelco.com/s/article/Configure-a-Windows-service-to-restart-on-a-schedule-using-Task-Scheduler-1538586706677?language=en_US 

Product:

Planning Analytics 2.0.9.19

Issue:

Want the user name, running the TM1 TI process to be in the filename – how?

Solution:

There are many ways to do this, check internet and tm1py.

But this is a suggestion, when you use CAMID with Cognos Analytics:

# -- get the user
sUserRAW = TM1User() ;

IF ( SCAN ('R*S' ,sUserRAW ) <> 0 ) ;
sUser = 'Scheduled' ;
ELSE;
# -- get the alias 
sUserALIAS = ATTRS( '}Clients' , sUserRaw , '}TM1_DefaultDisplayValue') ;
# -- find the name part of the domain/name 
sUserBREAK = SCAN ( '/' , sUserALIAS ) ;
# -- SUBST(string, beginning, length) 
sUser = SUBST( sUserALIAS, sUserBREAK +1, LONG (sUserALIAS) - ( sUserBREAK ) ) ;
ENDIF;

sFileName = 'Data.' | sUser | '.' | TIMST( NOW(), '\Y\m\d.\h\i', 1) | '.zip';

More Information:

https://everanalytics.wordpress.com/2017/08/10/tm1_alias_attributes/ 

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

Product:

Planning Analytics 2.0.9.19

Issue:

When we copy the production TM1 instance to the development server, we have to turn off all the chores – is there a simpler way?

Solution:

Create a TM1 TI process that check a environment cube value, that is stored in a separate data folder, that tell if it is TEST or PROD server you run in.

Then add this TI process first in all the chores, so when a chore is run, it first checks if it is in TEST, and then quits the chore.

In the log file you will have a text like this: TM1.Process Process “SYS. Turn off this chore if in TEST”: : Execution was aborted by ChoreQuit() Function. : ChoreQuit() function called

To be able to temporary run the chore, we have a prompt in the TI process, that you can set to YES in the schedule, to make it run in your development environment.

Prompt can look like this:

Code should be like this in PROLOG:

sEnvironment = CellGetS( 'systemparameters', 'Environment' , 'Text');
IF ( sEnvironment @= 'Test') ;
IF ( pRun @<> 'YES' );
ChoreQuit ;
ENDIF; 
ENDIF;

More Information:

How to set up Turbo Integrator Chores to run Advanced Scheduling

https://bihints.com/closer-look-chores

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

Product:

Planning Analytics 2.0.9.19

Planning Analytics Workspace version 96

Microsoft Windows 2019

Issue:

The csv files amount have changed from period to comma for decimals when we run the TM1 TI process?

Possible Solution:

Depending from where you start your TM1 TI process, the local region settings will affect the decimal separator in the file.

If you run a TM1 TI process from PAW in EDGE set to Swedish for language and spelling, then the value will have a comma (,) as a decimal separator.

If you run a TM1 TI process from PAW in EDGE or CHROME set to English for language and spelling, then the value will have a period (.) as a decimal separator.

Check the web browser options – language and ensure that you have English as first choice to get a period as decimal separator like 100.99

TM1 Architect will use the region settings of the users value in Windows control panel.

You can set values in the TM1 TI process, to make conversion of value work, but the ASCIIOUTPUT may not be affected.

DatasourceASCIIDecimalSeparator TurboIntegrator local variable sets the decimal separator to be used in any conversion of a string to a number or a number to a string. If you set this variable you must also set the DatasourceASCIIThousandSeparator variable. This will affect NumberToString (value) but not ASCIIOUTPUT (value).

The character specified must be a standard ASCII printable character, with a decimal value between 33 and 127 inclusive.

Either of the following examples sets the comma character (,) as the separator.

DatasourceASCIIDecimalSeparator=',';

DatasourceASCIIDecimalSeparator=Char(44);

More Information:

https://cubewise.com/functions-library/tm1-function-for-ti-numbertostringex/ 

https://cubewise.com/functions-library/tm1-function-for-ti-datasourceasciidecimalseparator/

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

What is the syntax for NumberToStringEx?

NumberToStringEx(Value, NumericFormat, DecimalSep, ThousandsSep)

• ​Value = any real number​
• NumericFormat = TM1 numeric format string e.g. #,0.## ​
• DecimalSep = Decimal separator to be used in the output​
• ThousandsSep = Thousands separator to be used in the output

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

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

Product:

Planning Analytics 2.0.9.19
Microsoft Windows 2019 server

Issue:

How can i find data path to TM1 session?

Solution:

Search the internet and use the code to build something like this, it will check if the TM1S.CFG file is in the data or a \config\ folder, and then load the data from TM1S.CFG file to a dimensions, that you later can ask in your code for information from.

#Section Prolog

#****Begin: Generated Statements***
#****End: Generated Statements****

# -- get the data folder if the logs folder is logs or logfiles ---
sLogDirName1 = LOWER ('Logfiles\' ) ;
sLogDirName2 = LOWER ('Logs\' ) ;
sConfigDirName = LOWER ('Config\') ;
sDataDirName = LOWER ( 'Data\' ) ;
sBackupDirName = LOWER ( 'Backup\' );
sLogDirPath = LOWER( GetProcessErrorFileDirectory );

nLScan1 = SCAN (sLogDirName1, sLogDirPath) ;
nLScan2 = SCAN (sLogDirName2, sLogDirPath) ;
IF ( nLyckadScan1 <> 0 );
# sDataDirPath = DELET (sLogDirPath, nLScan1, LONG (sLogDirName1)) | sDataDirName;
# sBackupDirPath = DELET (sLogDirPath, nLScan1, LONG (sLogDirName1)) | sBackupDirName;
sConfigDirPath = DELET (sLogDirPath, nLScan1, LONG (sLogDirName1)) ;
ELSEIF ( nLyckadScan2 <> 0 );
# sDataDirPath = DELET (sLogDirPath, nLScan2, LONG (sLogDirName2)) | sDataDirName;
# sBackupDirPath = DELET (sLogDirPath, nLScan2, LONG (sLogDirName2)) | sBackupDirName;
sConfigDirPath = DELET (sLogDirPath, nLScan2, LONG (sLogDirName2)) ;
ELSE;
# the log folder can be the same as the datafolder 
sConfigDirPath = sLogDirPath ;
ENDIF;

CFGpath = sConfigDirPath ;

pParamsList=UPPER(':adminhost:ServerName:DataBaseDirectory:PortNumber:ClientMessagePortNumber:ServerCAMURI:LoggingDirectory:MTQ:HTTPPortNumber:IntegratedSecurityMode');
Dlmtr=';';
LenDtr=LONG(Dlmtr);
DataSourceType='CHARACTERDELIMITED';
# -- change the folder options to match your setup ---
sAfolder = 'config\' ;
ConfigFile = 'Tm1s.cfg';

IF (FileExists(CFGpath | sAfolder | ConfigFile ) = 1 ) ;
DatasourceNameForClient= CFGpath | sAfolder | ConfigFile;
DatasourceNameForServer= CFGpath | sAfolder | ConfigFile;

ELSEIF (FileExists(CFGpath | ConfigFile ) = 1 ) ;
DatasourceNameForClient= CFGpath | ConfigFile;
DatasourceNameForServer= CFGpath | ConfigFile;
ELSE;
# -- will use tm1s.cfg in the data folder --
DatasourceNameForClient= 'Tm1s.cfg';
DatasourceNameForServer= 'Tm1s.cfg';
ENDIF;


DatasourceASCIIDelimiter='=';
DatasourceASCIIHeaderRecords=1;

DimName='SYS_ServerParameters';
IF( DimensionExists(DimName) = 0 );
DimensionCreate( DimName );
ENDIF;

IF( CubeExists( '}ElementAttributes_'|DimName )=0 );
AttrInsert( DimName , '' , 'Value' , 'S' );
ENDIF;

IF( CubeExists( '}ElementAttributes_'|DimName )=1 );
IF(DIMIX( '}ElementAttributes_'|DimName , 'Value' )=0 );
AttrInsert( DimName , '' , 'Value' , 'S' );
ENDIF;
ENDIF;

#Section Metadata

#****Begin: Generated Statements***
#****End: Generated Statements****
#Section Data

#****Begin: Generated Statements***
#****End: Generated Statements****


IF( SUBST( Param , 1 , 1)@<>'#' & Param @<>'' & SCAN(UPPER(Param) , pParamsList)>0 );

IF( SCAN( ';' , Value )>0 );
CurPos=1;
ValueList=Value|Dlmtr;
ParamNum='';
i=0;
WHILE(CurPos>0&LONG( ValueList )>0);
CurPos=SCAN( Dlmtr , ValueList );
Value=SUBST( ValueList , 1, CurPos-1);
ValueList=DELET( ValueList, 1, CurPos+LenDtr-1);
#LOGOUTPUT( 'INFO' , Param | ParamNum |' = '| Value );
DimensionElementInsertDirect(DimName, '', Param | ParamNum ,'N');
AttrPutS(Value, DimName, Param | ParamNum , 'Value' );
i=i+1;
ParamNum=NUMBERTOSTRING(i);
END;
ELSE;

#LOGOUTPUT( 'INFO' , Param |'='| Value ); 
DimensionElementInsertDirect(DimName, '', Param ,'N');
AttrPutS(Value, DimName, Param , 'Value' );
ENDIF;

ENDIF;
#Section Epilog

#****Begin: Generated Statements***
#****End: Generated Statements****

More Information:
https://www.ibm.com/docs/en/planning-analytics/2.0.0?topic=trf-delet-1

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

https://www.ibm.com/docs/en/cognos-tm1/10.2.2?topic=chores-running-chore-server-startup 

Product:
Planning Analytics 2.0.19
Microsoft Windows 2019 server

Issue:
How get the parent and child of a dimension to a text file?

Solution:

Create a new TM1 TI process with the data source to the dimension.

And a parameter where you enter the dimension:

And the TM1 code as below:

#Section Prolog


# -- check the enter value to not be blank --
IF (LONG(pDimension) = 0 ) ;
sDim = 'account' ;
ELSE;
sDim = pDimension ;
ENDIF;
sOutputFile = 'D:\temp\' | sDim | '.csv';

# -- set the dimension as source --
DatasourceNameForServer = sDim ;
DatasourceNameForClient = sDim ;
DATASOURCEDIMENSIONSUBSET = 'all';

#Section Metadata

#Section Data

# ----- export the dimension ---

sElementType = DTYPE ( sDim, v1 );
# always output the element and its type
AsciiOutput ( sOutputFile, sElementType , v1 );
# cycle through children if it is a consolidated element
IF ( sElementType @= 'C' );
iCount = 1;
iMax = ElCompN ( sDim, v1 );
While ( iCount <= iMax );
sChild = ElComp ( sDim, v1, iCount );
sWeight = NumberToString ( ElWeight ( sDim, v1, sChild ) );
AsciiOutput ( sOutputFile, '', sChild , sWeight );
iCount = iCount + 1;
End;
EndIf;

#Section Epilog

This need to be adjusted to meet your needs.

More Information:

https://exploringtm1.com/how-to-use-a-dynamic-path-in-a-ti-for-a-data-source/ 

https://www.ibm.com/docs/en/cognos-tm1/10.2.2?topic=variables-datasourcedimensionsubset

https://lodestarsolutions.com/tag/tm1-dimension-subsets/

https://www.wimgielis.com/tm1_mdxstatements_EN.htm

Product:
Planning Analytics 2.0.9.19

Microsoft Windows 2019 server

Issue:
How send mail with powershell from Tm1?

Solution:

Create a folder on your tm1 server called d:\script\ , and in there create a file SENDMAIL.PS1 in Notepad++ with this content:

# Define the paths to your text files
$messageFilePath = "d:\script\message.txt"
$recipientFilePath = "d:\script\recipient.txt"

# Read the content of the text files
$messageBody = Get-Content -Path $messageFilePath -Raw
$recipient = Get-Content -Path $recipientFilePath -Raw

# Define the email parameters
$smtpServer = "smtp.yourserver.com"
$from = "your-email@domain.com"
$subject = "Your Subject Here"

# Send the email
Send-MailMessage -From $from -To $recipient -Subject $subject -Body $messageBody -SmtpServer $smtpServer

Update the $messageFilePath and $recipientFilePath variables with the paths to your text files.
Make sure to replace “smtp.yourserver.com” and “your-email@domain.com” with your actual SMTP server and email address.

Create a file “message.txt” with the text you want to send as body.
Create a file “recipient.txt” with the email person with the receiver of the mail.

Then create a TM1 TI process with above prompt, and below code:

#Section Prolog

#--- set variables ----
vFileMessage = 'd:\script\message.txt' ;
vFileRecipient = 'd:\script\recipient.txt' ;
toPerson = 'tm1@company.com' ;
toMessage = 'This is a test - something went wrong' ;

#--- check the prompts -----------
isMail = scan ( '@' , pReciver) ;
IF ( isMail <> 0 ) ;
toPerson = pReciver ;
ENDIF ;

IF ( LONG (pMessage) <> 0 ) ;
toMessage = pMessage ;
ENDIF ;

# --- Change to get clean text in the text file
DatasourceASCIIQuoteCharacter='';
DatasourceASCIIDelimiter = ',';
#--- try change this value to support other characters than english ------
setOutputCharacterSet (vFileMessage, 'TM1CS_ISO_8859_1');

#-------write the files-------------
TextOutput( vFileRecipient, toPerson ) ;
TextOutput( vFileMessage, toMessage ) ;

#Section Metadata

#Section Data

#Section Epilog

# call the powershell file to send the mail
ExecuteCommand( 'Powershell -ExecutionPolicy ByPass -file "D:\script\sendmail.ps1" ', 1 );

Run the process, to test it.

More Information:

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

https://edu.cubewise.com/functions-library/tm1-function-for-ti-setoutputcharacterset

https://www.ibm.com/docs/it/cognos-tm1/10.2.2?topic=SS9RXT_10.2.2/com.ibm.swg.ba.cognos.tm1_ref.10.2.2.doc/r_tm1_ref_tifun_asciioutput.htm

https://www.ibm.com/docs/en/planning-analytics/2.0.0?topic=tf-ascii-text-turbointegrator-functions 

https://lazyadmin.nl/powershell/send-email-powershell/ 

https://mailtrap.io/blog/powershell-send-email/

Product:

Planning Analytics 2.0.9.19
Microsoft Windows 2019 server

Issue:

How to add extra withlist on prompts, to ensure the value is somewhat correct, before processing?

Suggested solution:

If you have a prompt pYear and pMonth, you can have below code in prolog:

# if year is blank enter current year
IF (pYear  @= '') ;
pYear = NumberToString( Year ( Today (1) ) ) ; 
ENDIF;

# if year is not 4 characters - stop the process
IF (LONG (pYear ) <> 4);
sErrorMessage = 'Year is not correct value';
ItemReject ( sErrorMessage ); 
ProcessQuit;
ENDIF;

# add zero if only one digit
IF (LONG (pMonth) = 1) ;
pMonth = '0' | pMonth  ;
ENDIF;

# check that month is 2 digits

IF (LONG (pMonth) <> 2) ; 
ProcessQuit ;
ENDIF;

More Information:

https://www.ibm.com/docs/ru/SSD29G_2.0.0/com.ibm.swg.ba.cognos.tm1_ref.2.0.0.doc/tm1_ref.pdf 

https://nvlpubs.nist.gov/nistpubs/specialpublications/nist.sp.800-167.pdf

https://exploringtm1.com/year-function-tm1-syntax-use/

Product:
Planning Analytics 2.0.9.19
TM1 perspective
Microsoft® Excel® for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20550) 32-bit

Issue:

Then create a websheet with formulas, when it refresh the formulas change to =@TM1RPTROW.

Excel will treat any value containing an @ as an email address.

Solution:

For a single cell right click and go down the menu which appears to link -> “remove hyperlink.”
Release mouse and the hyperlink is disabled in that cell.

To prevent it to happen in future:

Go to menu File > Options > Proofing > AutoCorrect Options > Click Tab AutoFormat As You Type > Uncheck Internet and network paths with hyperlinks > OK

More Information:

https://support.microsoft.com/en-us/office/remove-or-turn-off-hyperlinks-027b4e8c-38f8-432c-b57f-6c8b67ebe3b0 

Product:
Planning Analytics 2.0.9.19 TM1 Architect

Microsoft Windows 2019 server

Issue:

When open a cube view in TM1 Explorer from TM1 Perspective on my laptop, and click on the dimension to get the subset editor, A beep hears but no dialog is shown. The cube view is frozzen, and only way out is to exit TM1 architect.

Solution:

When moveing your laptop between desks at the office, and connect it to diffrent displays with diffrent resultions, this error will be more common.

Turn of Tm1 Architect.

Start Windows file explorer.

Go to folder C:\Users\%username%\AppData\Roaming\Applix\TM1

Open file tm1p.ini in notepad

Remove the line with the text:   SubsetWindowLayoutInfo

Save the file.

Start Tm1 Architect/Tm1Perspective and test again to open the subset editor.

More information:

TM1 Perspectives subset editor missing when opened on remote desktop system with multiple displays
https://www.ibm.com/support/pages/apar/PM95711

https://www.ibm.com/support/pages/unable-view-all-open-tm1-objects-after-disconnecting-secondary-monitor