IBM Planning Analytics

Overview

ReportWORQ supports the refresh of Excel-based reports created using the IBM Planning Analytics Perspectives and Planning Analytics for Excel (PAfE) Excel add-ins. These reports use proprietary custom Excel formulas and other Excel settings to help define how to extract data, attributes, and metadata from IBM Planning Analytics (TM1) Server instances. ReportWORQ reads and writes data to Planning Analytics via the Planning Analytics REST API. The list of IBM Cognos Planning Analytics versions that are supported by ReportWORQ matches the versions actively supported by IBM (refer to the support lifecycle information provided by IBM).

ReportWORQ Parameters can be used to change cell values in Excel reports which can, in turn, change the references to any Planning Analytics formulas and other reporting options to dynamically update Excel reports from Planning Analytics. ReportWORQ additionally supports a couple of Planning Analytics-specific Parameter types: IBM Planning Analytics subsets and MDX Queries.

This article describes the configuration of Planning Analytics connections, as well as the various IBM Planning Analytics features and integration options supported by ReportWORQ:

• Configuring Connections:

  • Planning Analytics - Self Hosted

  • Planning Analytics - IBM Cloud

  • Planning Analytics - SaaS on AWS with MCSP

  • Troubleshooting Connections

• Required Security Permissions

• Supported Reporting Features

• Planning Analytics-Specific ReportWORQ Parameters

• ReportWORQ Automation Options with Planning Analytics

  • Calling the ReportWORQ REST API from Turbo Integrator Processes

  • Cube Monitoring

To use IBM Planning Analytics as a data source, at least one IBM Planning Analytics connection (Self Hosted, IBM Cloud, AWS Cloud, or AWS Cloud with MCSP) must be configured in ReportWORQ, and you must also have an existing Perspectives or PAfE report which uses that connection.

Configuring Connections

To add an IBM Planning Analytics connection to the list of available Datasources, ReportWORQ administrators open the Administration screen then click the '+' button beside Datasources, and select the IBM Planning Analytics option:

Four options for connecting to IBM Planning Analytics are supported by ReportWORQ. Which one to use is determined by how your IBM Planning Analytics environment is hosted and managed:

• Self Hosted Connections: These are connections to any Planning Analytics instance which you manage, either by installing Planning Analytics on servers in your corporate data center or a cloud environment, such as Amazon Web Services (AWS) or Microsoft Azure.

• IBM Cloud Connections: These are connections to Planning Analytics instances that are hosted and managed by IBM.

• IBM Planning Analytics as a Service on AWS Cloud (IAM Authentication): These are connections to Planning Analytics SaaS on AWS instances that use IBM Cloud Identity and Management (IAM) authentication.

• IBM Planning Analytics as a Service on AWS Cloud (with MCSP): These are connections to Planning Analytics SaaS on AWS instances with Managed Cloud Service Provider (MCSP) authentication.

Self Hosted Configuration

Planning Analytics Self Hosted connections are defined based on the authentication options supported by your Planning Analytics server instances (Databases), specifically the IntegratedSecurityMode setting defined in your database configuration. All security modes are supported in ReportWORQ:

• Native (IntegratedSecurityMode 1 + 2): User Logins are fully managed within Planning Analytics.

• Windows Integrated (IntegratedSecurity Mode 2 + 3): User logins to Planning Analytics are mapped to Active Directory (Windows Domain) logins.

• Cognos Access Manager / CAM (Integrated Security Mode 4 + 5): User logins to Planning Analytics are managed as part of CAM Security.

Prerequisites

The following information is required as a prerequisite when configuring connections to local IBM Planning Analytics databases:

1. REST API Access Must be Enabled: ReportWORQ communicates with Planning Analytics through the REST API. To enable the Planning Analytics REST API, please configure a valid HTTPPortNumber in the tm1s.cfg file:

2. Basic Database Connection Information:

   1. The address of the Planning Analytics Admin Host service where your Planning Analytics Server database is registered, or

   2. The following Planning Analytics Server database configured settings:

      1. Name (e.g., Planning Sample)

      2. Address

      3. REST API Port Number

      4. SSL Enabled / Disabled

3. Firewall Access Rules: REST API ports (HttpPortNumber setting) must be opened in any firewall between ReportWORQ and your Planning Analytics server. Port 5895 and 5898 must additionally be opened in any firewalls between ReportWORQ and the machine running the Planning Analytics Admin Host service if that service will be used to automatically retrieve settings for a Planning Analytics database connection.

4. Login Credentials & Mode: Verify the IntegratedSecurityMode setting for the database that you wish to connect to, and make sure that you have valid login credentials for that database.

Connection Configuration

The following configuration information will appear after selecting Self Hosted as the Authentication Type or when clicking the link for an existing connection in ReportWORQ:

• Datasource Name: This is a friendly name that describes this connection, and will be visible to all users of the ReportWORQ application when accessing information related to Planning Analytics (such as parameters). The name must be unique across all defined datasources.

• Database: This is the Database name as defined in your Planning Analytics configuration. You can manually enter the name or select from the list if populated from a Planning Analytics Admin Host service.
  IMPORTANT: If you plan to use the Planning Analytics distributor and there are multiple datasources that use the same database, all such datasources except the one required for the current job must be disabled while the job is running.

Distribution Credentials:

• Username & Password: Distribution credentials are required, and are the same credentials used to authenticate IBM Planning Analytics Microsoft for Excel. If Data Collection credentials are not enabled, they are also used for Data Collection.

Data Collection Credentials:

• Username & Password: Data Collection credentials are optional. When enabled, they limit contributor access to specified areas of the system.

The icon to the right of the Database option can be used to automatically populate the Database drop-down list based on a lookup to a Planning Analytics Admin Host Service:

When you click it, you will be prompted for the Admin Host Service address, for example:

Clicking OK will attempt to load a list of servers, each of which will automatically be populated with the Address, Use SSL, and Port settings that are required below. This helps avoid errors compared encountered manually entering the same information.

• Address: This is the address of the machine where your Planning Analytics Server database(s) are running. This address must be accessible by the ReportWORQ Server and can be:

  • An IP address (e.g., 10.0.0.123)

  • A machine name (e.g., prod-paserver-01)

  • A fully qualified domain name (e.g., prod-paserver-01.company.com)

• Port: This is the REST API port number for the Planning Analytics database.

• Use SSL: This defines whether or not ReportWORQ will communicate with your Planning Analytics database using SSL, and is determined by your Planning Analytics Database configuration.

• Username: The username used to log in (leave blank for Integrated Login).

• Password: The password used to log in (leave blank for Integrated Login).

• Namespace: This is the Cognos namespace, which is required when your Planning Analytics Database is configured to use CAM Security.

• Test: Clicking the '✓' button will either display a success message or show error messages with further information on missing or misconfigured settings.

• Save: The 'Save' button saves changes made to the connection.

• The IBM Planning Analytics Datasource can be enabled or disabled by clicking the checkbox.
  
  Advanced Options can be configured by selecting the following toolbar button:
  

  
  
  
  

• PAfE Calculation Mode: This setting tells ReportWORQ how to calculate and resolve Planning Analytics Excel formula results:

  • Enabled: ReportWORQ will calculate formulas like the Planning Analytics for Excel (PAfE) add-in, for instance, by returning #VALUE! errors when invalid or missing element names are referenced.

  • Disabled: ReportWORQ will calculate formulas like the TM1 Perspectives add-in, for instance, by returning blank values when invalid or missing element names are referenced.

• Enable Proportional Data Collection: This enables ReportWORQ to send data to consolidated cells using the Proportional Spread option, and is only available for customers who have licensed the ReportWORQ Data Collection feature.

• Enable Cube Monitor: This is a method for automating ReportWORQ Job Execution from Planning Analytics. Enabling this option will create additional objects on this Planning Analytics Database Instance.

Integrated Login Support

Additional configuration related to Active Directory and Kerberos is required to ensure that ReportWORQ can successfully connect to Planning Analytics using Integrated Login. Before performing the configuration steps described in this section, please confirm that the following are true:

1. Planning Analytics Integrated Login is already working properly when using Architect or Perspectives.

2. The Planning Analytics Server service is running under an Active Directory domain/service account.

3. You know the URLs and IP address(es) of the Planning Analytics Server.

4. You have verified that you have access to someone (typically an Active Directory administrator) who can:

   • Create or modify Service Principal Names (SPNs) in Active Directory.

   • Update/push policy changes to client desktops, if needed.

Once you've confirmed the above, the primary steps required to configure ReportWORQ to use integrated login are to add one or more Service Principal Names (SPNs) using the following structure:

setspn -s HTTP/<Address> <Planning Analytics Service Account Name>

Key:

• <Address>: The address of the server machine running the Planning Analytics Server, which may be:

  • Fully-qualified name, for example, 'app01.company.com'

  • Machine name, for example, 'app01'

  • IP address, for example, '10.4.121.15'

• <Planning Analytics Service Account Name>: The domain account that the Planning Analytics Server application is running under (the "Log on as" account)

Examples:

If your Active Directory domain is called "Corporate. Internal" and your Planning Analytics Server runs on a machine called "PAAppPrd01" under a service account called "TM1Services" running at IP Address "10.10.12.34" you might add the following SPNs:

setspn -s HTTP/PAAppPrd01.Corporate.Internal TM1Services
setspn -s HTTP/PAAppPrd01 TM1Services
setspn -s HTTP/10.10.12.34 TM1Services

Note that SPN updates may not be visible to all client desktops immediately; your Active Directory administrator can help determine whether or not those changes have propagated across all client machines.

IBM Cloud Configuration

IBM Planning Analytics Cloud connections are managed separately in ReportWORQ because of differences in the way they are configured and managed by IBM.

Prerequisites

The following is needed to connect to IBM Planning Analytics Cloud:

1. The Welcome Kit provided by IBM.

2. A non-interactive user account, which is part of the Welcome Kit

   • NOTE: If the Non-Interactive Account section of the welcome kit is empty then you will need to request a non-interactive account by opening a support case with IBM.

3. Enable REST API nocanon Configuration Setting

   • This is only required if your reports reference element names containing special characters such as a backslash ("\"), and must be enabled by opening an IBM support case. You will need to include the following information in your support case:

IBM,

We need "nocanon" added to the ProxyPass in our IBM Cloud environments:

[Customer cloud environment 1]
[Customer cloud environment 2]
...
[Customer cloud environment n]

This is needed to address an issue with IBM PA Cloud’s handling of REST API requests, which is documented in case number TS002469510.

Connection Configuration

The following configuration information will appear after selecting "IBM Cloud authentication":

These are the connection configuration options:

• Datasource Name: This is a friendly name that describes this connection, and will be visible to all users of the ReportWORQ application when accessing information related to Planning Analytics (such as parameters). The name must be unique across all defined datasources.

• Database: This is the Database name as defined in Planning Analytics Cloud.
  IMPORTANT: If you plan to use the Planning Analytics distributor and there are multiple datasources that use the same database, all such datasources except the one required for the current job must be disabled while the job is running.

Distribution Credentials:

• IBM Cloud Url: This is an IBM-provided URL for your IBM Planning Analytics Cloud environment.

• Namespace: This is the namespace provided in your IBM Cloud Welcome Kit, which is typically 'LDAP'

• Username: This is the username for IBM-provided non-interactive login credentials.

• Password: This is the password for IBM-provided non-interactive login credentials.

• Test: Clicking the '✓' button will either display a success message or show error messages with further information on missing or misconfigured settings.

• Save: The 'Save' button saves changes made to the connection.

• The IBM Planning Analytics Datasource can be enabled or disabled by clicking the checkbox.
  
  Data Collection Credentials:
  

  
  
  

• Username & Password: Data Collection credentials are optional. When enabled, they limit contributor access to specified areas of the system.
  
  Advanced Options can be configured by selecting the following toolbar button:
  

  

• PAfE Calculation Mode: This is a setting that tells ReportWORQ how to calculate and resolve Planning Analytics Excel formula results:

  • Enabled: ReportWORQ will calculate formulas like the Planning Analytics for Excel (PAfE) add-in, for instance, by returning #VALUE! errors when invalid or missing element names are referenced.

  • Disabled: ReportWORQ will calculate formulas like the TM1 Perspectives add-in, for instance, by returning blank values when invalid or missing element names are referenced.
    

• Enable Proportional Data Collection: This enables ReportWORQ to send data to consolidated cells using the Proportional Spread option, and is only available for customers who have licensed the ReportWORQ Data Collection feature.

• Enable Cube Monitor: This enables Cube Monitoring, which is a method for automating ReportWORQ Job Execution from Planning Analytics. Enabling this option will create additional objects on this Planning Analytics Database Instance.

Connecting to IBM Planning Analytics as a Service on AWS Cloud (with MCSP)

This section describes how to connect to IBM Planning Analytics as a Service on AWS Cloud with Managed Cloud Service Provider (MCSP) authentication.

Before you begin:

1. Create an API Key for Planning Analytics as a Service with MCSP:

   1. Select an existing user account in your Planning Analytics as a Service environment that you would like ReportWORQ to use when refreshing reports.

   2. Follow IBM's instructions for creating an API Key for that user account.

2. Gather connection information for Planning Analytics as a Service with MCSP:

   • The name of the Planning Analytics Server database instance(s) that you plan to connect to from ReportWORQ (e.g., Planning Sample).

   • The AWS Tenant ID where your IBM Planning Analytics as a Service environment is hosted (e.g., A1BCD2EFGHIJK).

   • The AWS Data Center URL (example) where your IBM Planning Analytics as a Service environment is hosted.
     

To configure and test the datasource connection:

1. In the ReportWORQ Administration interface, select the existing IBM Planning Analytics datasource you want to configure, or add a new one.
   The following configuration settings appear when you select AWS Cloud (MCSP) as the datasource’s Authentication Type.
   
   

   
   

2. Configure the Datasource Settings as follows:

   • Datasource Name: This is a friendly name that describes this connection, and will be visible to all users of the ReportWORQ application when accessing information related to Planning Analytics (such as parameters). The name must be unique across all defined datasources.

   • Authentication Type: Select AWS Cloud (MCSP).

   • Database: This is the Database name as defined in Planning Analytics Cloud.
     IMPORTANT: If you plan to use the Planning Analytics distributor and there are multiple datasources that use the same database, all such datasources except the one required for the current job must be disabled while the job is running.

   • Data Center Url: This is an AWS/IBM-provided URL for your environment.

   • Tenant ID: This is an AWS/IBM-provided URI for your environment.

3. In the Distribution Credentials area, enter the API Key you retrieved earlier. The API Key is used to connect to your IBM Planning Analytics database.

4. Select Test Connection .

5. If the test is successful, select the Enable datasource connection check box and then select the Save Changes icon.

6. Review and configure the Advanced Settings  as required:
   
   
   

   • PAfE Calculation Mode: This is a setting that tells ReportWORQ how to calculate and resolve Planning Analytics Excel formula results:

     • Enabled: ReportWORQ calculates formulas like the Planning Analytics for Excel (PAfE) add-in, for instance, by returning #VALUE! errors when invalid or missing element names are referenced.

     • Disabled: ReportWORQ calculates formulas like the TM1 Perspectives add-in, for instance, by returning blank values when invalid or missing element names are referenced.

   • Enable Proportional Data Collection: This enables ReportWORQ to send data to consolidated cells using the Proportional Spread option, and is only available for customers who have licensed the ReportWORQ Data Collection feature.

   • Enable Cube Monitor: This enables Cube Monitoring, which is a method for automating ReportWORQ Job Execution from Planning Analytics. Enabling this option will create additional objects on this Planning Analytics Database Instance.

Troubleshooting Connections

The following are suggested troubleshooting steps for IBM Planning Analytics connections for all supported connection types.

Troubleshooting for Self Hosted Connections

The suggested troubleshooting steps for Self-Hosted connections are as follows:

1. Attempt to access the "REST API Metadata" and "Planning Analytics Database Information" URLs defined below from a web browser located on the ReportWORQ Server.

2. If either of those tests fails, try the same test from a user desktop that you know can connect to Planning Analytics from Architect, Perspectives, or PAfE.

3. If #1 and #2 fail, please verify:

   1. Connection settings (address, port, and use SSL).

   2. Firewall settings on the Planning Analytics Server.

4. If only #1 fails, please verify:

   1. Firewall settings on the Planning Analytics Server.

   2. Firewall settings on the ReportWORQ Server.

5. If all of the above tests pass, but you are still unable to connect from ReportWORQ, please verify your login credentials from Architect, Perspectives, or PAfE.

The sample URLs for Self Hosted environments are defined below:

REST API Metadata URL:

{HttpOrHttps}://{Address}:{Port}/api/v1/$metadata

Planning Analytics Database Information URL:

{HttpOrHttps}://{Address}:{Port}/api/v1/Server

Where:

• {HttpOrHttps}: "Https" when "Use SSL" is enabled in connection settings, otherwise "Http"

• {Address}: the address specified in connection settings

• {Port}: the REST API port number specified in connection settings

For example, if these are your configured settings for a Planning Analytics Database:

• Address: prod-pa-server-01.company.com

• Use SSL: checked

• Port: 12887

Your test Urls & responses will be:

REST API Metadata Sample

URL: https://prod-pa-server-01.company.com:12887/api/v1/$metadata

Response (only an excerpt of the full response is below):

<?xml version="1.0" encoding="UTF-8"?>
<edmx:Edmx Version="4.0"
	xmlns:edmx="http://docs.oasis-open.org/odata/ns/edmx">
	<edmx:Reference Uri="https://oasis-tcs.github.io/odata-vocabularies/vocabularies/Org.OData.Capabilities.V1.xml">
		<edmx:Include Namespace="Org.OData.Capabilities.V1" Alias="Capabilities"/>
	</edmx:Reference>
	<edmx:Reference Uri="https://oasis-tcs.github.io/odata-vocabularies/vocabularies/Org.OData.Core.V1.xml">
		<edmx:Include Namespace="Org.OData.Core.V1" Alias="Core"/>
	</edmx:Reference>
	<edmx:DataServices>
		<Schema Namespace="ibm.tm1.api.v1" Alias="tm1"
			xmlns="http://docs.oasis-open.org/odata/ns/edm">
			<Annotation Term="Core.Revisions">
				<Collection>
					<Record>
						<PropertyValue Property="Version">
							<String>10.2.2.0</String>
						</PropertyValue>
						<PropertyValue Property="Kind">
							<EnumMember>Core.RevisionKind/Added</EnumMember>
						</PropertyValue>
					</Record>
				</Collection>
			</Annotation>
			<Annotation Term="Core.SchemaVersion">
				<String>11.6.0</String>
			</Annotation>
		</Schema>
	</edmx:DataServices>
</edmx:Edmx>

Planning Analytics Database Information Sample
URL: https://prod-pa-server-01.company.com:12887/api/v1/Server

Response:

{
	"@odata.context": "$metadata#Server",
	"Name": "Prod",
	"ProductVersion": "11.7.00002.1",
	"PortNumber": 12345,
	"ClientMessagePortNumber": 12346,
	"HTTPPortNumber": 12887,
	"UsingSSL": true,
	"SecurityPackageName": "",
	"ServicePrincipalName": "",
	"IntegratedSecurityMode": "Mixed",
	"ClientCAMURI": "",
	"ClientPingCAMPassport": 900
}

Troubleshooting for IBM Cloud

The suggested troubleshooting steps for IBM Cloud (IBM fully managed and hosted) connections are as follows:

1. Attempt to access the IBM Cloud-specific "REST API Metadata" and "Planning Analytics Database Information" Urls defined below from a web browser located on the ReportWORQ Server.

2. If either of those tests fails, you should next try the same test from a user desktop that you know can connect to IBM Planning Analytics Cloud from PAfE.

3. If #1 and #2 fail, please verify your connection settings (IBM Cloud Url and Database Name).

4. If all of the above tests pass, but you are still unable to connect from ReportWORQ, you should verify with IBM Support that the IBM PA Cloud Interactive User login credentials and related REST API configuration settings are valid for your IBM Cloud-hosted database.

The cloud-specific sample Urls are defined below:

REST API Metadata:

https://{IBM Cloud Url}/tm1/api/{Database}/api/v1/$metadata

Planning Analytics Database Information:

https://{IBM Cloud Url}/tm1/api/{Database}/api/v1/Server

Where:

• {IBM Cloud Url}: the address of your IBM Planning Analytics Cloud environment

• {Database}: the name of the database instance, as defined in IBM Cloud

For example, if these are your configured settings for a Planning Analytics Database:

• IBM Cloud Url: company-prod.planning-analytics.ibmcloud.com

• Database: Prod

Your test Urls & responses will be:

REST API Metadata Sample

Url: https://company-prod.planning-analytics.ibmcloud.com/tm1/api/Prod/api/v1/$metadata

Response (only an excerpt of the full response is below):

<?xml version="1.0" encoding="UTF-8"?>
<edmx:Edmx Version="4.0"
	xmlns:edmx="http://docs.oasis-open.org/odata/ns/edmx">
	<edmx:Reference Uri="https://oasis-tcs.github.io/odata-vocabularies/vocabularies/Org.OData.Capabilities.V1.xml">
		<edmx:Include Namespace="Org.OData.Capabilities.V1" Alias="Capabilities"/>
	</edmx:Reference>
	<edmx:Reference Uri="https://oasis-tcs.github.io/odata-vocabularies/vocabularies/Org.OData.Core.V1.xml">
		<edmx:Include Namespace="Org.OData.Core.V1" Alias="Core"/>
	</edmx:Reference>
	<edmx:DataServices>
		<Schema Namespace="ibm.tm1.api.v1" Alias="tm1"
			xmlns="http://docs.oasis-open.org/odata/ns/edm">
			<Annotation Term="Core.Revisions">
				<Collection>
					<Record>
						<PropertyValue Property="Version">
							<String>10.2.2.0</String>
						</PropertyValue>
						<PropertyValue Property="Kind">
							<EnumMember>Core.RevisionKind/Added</EnumMember>
						</PropertyValue>
					</Record>
				</Collection>
			</Annotation>
			<Annotation Term="Core.SchemaVersion">
				<String>11.6.0</String>
			</Annotation>
		</Schema>
	</edmx:DataServices>
</edmx:Edmx>

Planning Analytics Database Information Sample

Url: https://company-prod.planning-analytics.ibmcloud.com/tm1/api/Prod/api/v1/Server

Response:

{
	"@odata.context": "$metadata#Server",
	"Name": "Prod",
	"ProductVersion": "11.7.00002.1",
	"PortNumber": 12345,
	"ClientMessagePortNumber": 12346,
	"HTTPPortNumber": 12887,
	"UsingSSL": true,
	"SecurityPackageName": "",
	"ServicePrincipalName": "",
	"IntegratedSecurityMode": "CAM",
	"ClientCAMURI": "https://company-prod.planning-analytics.ibmcloud.com/ibmcognos/cgi-bin/cognosisapi.dll",
	"ClientPingCAMPassport": 900
}

IBM PA SaaS on AWS Troubleshooting

The suggested troubleshooting steps for IBM Planning Analytics SaaS on AWS connections are as follows:

1. Verify your Data Center Url and Tenant Id by logging into your IBM PA SaaS on AWS environment from a web browser. Once logged in you'll see a URL like the following: https://<Data_Center_URL>/?accountId=ABC123DEF456&tenantId=<Tenant_Id>&perspective=pa-home

2. Login to IBM Cloud and verify that your API Key has been created and is still valid.

3. If all of the above tests pass, but you are still unable to connect from ReportWORQ, you should contact IBM Support to troubleshoot API access to your IBM PA SaaS on AWS hosted database(s).

Required Security Permissions

Planning Analytics login credentials in ReportWORQ require only the minimum READ access credentials needed to refresh data in any Planning-Analytics-enabled reports. When that feature is enabled, read access may also be required for any Cube Monitoring objects.

When ReportWORQ Data Collection support is required with Planning Analytics databases, ReportWORQ will additionally need WRITE access to any database cubes and dimensions that need to be populated as part of Data Collection processing.

Administrative permissions are not required for ReportWORQ, with two one-time exceptions:

• Enabling Cube Monitoring requires one-time Administrative permissions to create cube, dimension, and Turbo Integrator processes needed to support this ReportWORQ feature.

• Enable ReportWORQ REST API Integration requires one-time Administrative permissions to create Turbo Integrator processes needed to support this ReportWORQ feature.

Once any administrative operations have been completed, those permissions may be removed from the stored login credentials used by ReportWORQ.

Supported Reporting Features

ReportWORQ Supports the following Planning Analytics reporting modes and Excel formulas:

Reporting Modes

• Slices (Perspectives) / Custom Reports (PAfE)

• Active Forms (Perspectives) / Dynamic Reports (PAfE)

• Quick Reports (PAfE)

Explorations (PAfE) and In-Spreadsheet Browser (Perspectives) reports are not supported, currently.

Planning Analytics Excel Formulas

The following Planning Analytics Excel formulas are supported in ReportWORQ:

These Planning Analytics Excel formulas are not supported, currently:

• DBSA

• DBSS

• ELEMENTFIRST

• TM1GLOBALSANDBOX

• TM1INFO

• TM1RPTISEXPANDED

Planning Analytics-Specific ReportWORQ Parameters

ReportWORQ has several Parameter types that can be defined based on information in Planning Analytics:

• Planning Analytics Dimension: Used to return all elements from a Planning dimension hierarchy.

• Planning Analytics Subset: Used to return all elements from a named Private or Public Planning Analytics hierarchy Subset.

• Planning Analytics Subset MDX: Used to return elements from a dimension based on an MDX expression, including a special "Repeater" Parameter type that is designed to be used dynamically with other parameters.

More information about these parameter types and other parameter options that can be used in ReportWORQ with Planning Analytics and other datasources are described in more detail in the Creating Jobs topic.

Turbo Integrator Automation Options

Planning Analytics has built-in capabilities, such as its Turbo Integrator ETL tool, that are well-suited to automating the execution of ReportWORQ Jobs. There are two Turbo Integrator-related automations supported by ReportWORQ:

• REST API Integration: This is only supported for Self Hosted connections, and allows Planning Analytics developers to run ReportWORQ tasks, such as running Jobs, by calling the ReportWORQ REST API from a Turbo Integrator process.

• Cube Monitoring: This is designed for IBM PA Cloud (primarily) or Self Hosted connections, and allows Planning Analytics developers to insert ReportWORQ Job execution requests into a cube that ReportWORQ monitors and processes at regular intervals.

Both of these options are documented in more detail below, as are the Turbo Integrator processes and other objects that are created and used by ReportWORQ.

REST API Integration

This topic explains how to set up two Turbo Integrator Processes that integrate with the ReportWORQ REST API. This integration adds the following TI Processes to your Planning Analytics datasource:

• ReportWORQ - REST API Run Job Sample 2.1: Used to execute a Job in ReportWORQ, with several Job-specific overrides and settings available as parameter settings.

These TI Processes rely on accompanying scripts:

• For Windows, each of these TI Processes references a provided PowerShell script.

• For LINUX, you can edit the ReportWORQ - REST API Run Job Sample 2.1 Process to reference a provided .bash script.

To obtain the Turbo Integrator Processes for ReportWORQ:

1. In the Self Hosted Configuration Settings, click the link to Install REST API TI Process. This action adds these two Turbo Integrator Processes to your Planning Analytics environment.

2. Optionally, update the default parameter values in these Turbo Integrator processes:

   • pAccessToken: This can be set to the value of the ReportWORQ API Token created previously.

   • pApiUrl: This can be set to the configured ReportWORQ Server Url.

Windows only - to set up the PowerShell scripts:

1. These processes rely on two accompanying PowerShell scripts that are installed in a Scripts subfolder where ReportWORQ is installed (e.g., C:\Program Files\ReportWORQ\Scripts\). If Planning Analytics is running on a different machine than ReportWORQ then copy these files to the Planning Analytics server in the same folder path. If you do not use this default path then modify the Prolog of the Turbo Integrator process and adjust to the path used.

   • ProcessInput.ps1: Needed by "ReportWORQ - REST API Process Input".

   • RunApiJob.ps1: Needed by "ReportWORQ - REST API Run Job Sample 2.1".

2. Test the RunApiJob.ps1 PowerShell script for security policy changes. Right-click on the RunApiJob.ps1 file and choose Run with Powershell. If the Powershell window opens and closes then the script is ready to run. If the Execution Policy Change message in the screenshot below appears then select Y to enable this script.
   
   

   
   
   

3. For the ReportWORQ - REST API Run Job Sample 2.1 Process only: In the Prolog are two directory variable names that need to be updated to existing folders in your environment:

   • PowerShellScriptFolder: This is the folder that contains the RunApiJob.ps PowerShell script file. (e.g., C:\ReportWORQ Scripts\)

   • JobRequestFolder: This is a folder where job requests are written when this process runs. We recommend creating a subfolder within the PowerShellScript folder location (e.g., C:\ReportWORQ Scripts\RunApiJobRequests\).

LINUX only - to set up the RunApiJob.bash script:

1. Download the RunApiJob.bash script and then move it to a folder that can be accessed by Turbo Integrator.

2. In Turbo Integrator, edit the Prolog of the ReportWORQ - REST API Run Job Sample 2.1 Process to reference the RunApiJob.bash script instead of the RunApiJob.ps1 script, and to point to the folder that contains the script.
   For example:
   BashScriptFolder = '/reportworq/scripts/';

   BashScript = 'RunApiJob.bash' ;

   BashScriptFileLocation = BashScriptFolder | BashScript ;

3. Edit the Epilog of the ReportWORQ - REST API Run Job Sample 2.1 Process to reference the RunApiJob.bash script directly in the EXECUTECOMMAND function, and to point to the folder that contains the script.
   For example:
   CommandToRun = ' cd '| BashScriptFolder |' ; sh ' | BashScript | ' -token ' | pAccessToken | ' -url ' | pApiUrl | ' -jobPath ' | JobRequestFolder | LOWER(JobFileName) | ' -tlsSecurity ' | pTlsSecurity | ' > ' | BashScriptFolder | 'RunApiJob.log';

Additional Parameters for the ReportWORQ - REST API Run Job Sample 2.1 Process

Additional parameters are as follows:

• pJobName: The ReportWORQ job name to run.

• pNotificationEmail: Email address used by ReportWORQ to optionally send notifications when the job is complete.

• pHoldForDelivery: Specify "true" to run the Job with the Hold for Delivery option enabled.

• pTlsSecurity: This tells the PowerShell script what TLS protocol versions should be used when connecting to ReportWORQ. The default values are normally sufficient on most environments: Tls11, Tls12.

For more information about overriding ReportWORQ Job parameters, please view instructions and samples on the Epilog tab of the ReportWORQ - REST API Run Job Sample 2.1 TI Process. The Parameter Override logic uses the ReportWORQ API Run Command with the appropriate JSON being constructed through Turbo Integrator.

TI Script Troubleshooting

If a user is having difficulty with their TI script, it is recommended that they check this short list to determine the error before contacting support.

• Is the ReportWORQ API enabled?

• Are you able to access ReportWORQ in a browser? (Is it running?)

• Does the pApirUrl value match the ReportWORQ address and is it accessible from the TM1 server where you are attempting to run the script?

• Does the active accessToken in the TI parameters match the API token configured in the ReportWORQ API integration?

• Has the process run successfully since the RW5 script upgrade?

• Does the job you are trying to run exist?

• If you follow the Excel reference in the job definition, does it actually evaluate to a valid job name that exists in ReportWORQ?

• Does the name of the TM1 server you are attempting to use match the value in the pServer parameter?

• If the above is true, are there multiple jobs with the same name or a job and folder that share the same name? If so, a full path to the job will need to be used instead of just the job name.

• Is the path to the PowerShell file that is referenced in the TI script valid?

Cube Monitoring

To enable Cube Monitoring for Planning Analytics connections (Self Hosted, IBM Cloud), perform the following steps:

1. In the Self Hosted or IBM Cloud connection settings, select the check box to Enable Cube Monitor. This action adds the following objects to your Planning Analytics datasource:

   1. "}ReportWORQ__Queued Jobs" Cube: This cube, along with its two dimensions, is used by ReportWORQ to track job requests. ReportWORQ polls that cube every few seconds to see if a Job has been queued to run. Jobs are queued to run by adding an element to the "}ReportWORQ__Queued Job Id" dimension and populating the details of the job to be executed. ReportWORQ automatically removes processed job requests from the "Job Id" list. The "ReportWORQ - Add Queued Job" Turbo Integrator process can be used to add requests to this queue cube.

   2. "ReportWORQ - Add Queued Job" and "ReportWORQ - Add Queued Job for Workspace" Processes: These Turbo Integrator process provides a working example of how to create the Job Request entry and can be integrated into other Processes and Chores in Planning Analytics, for instance by requesting that a ReportWORQ Job be run after a data load is complete, or to provide some self-service reporting bursting options for users. The Add Queued Jobs for Workspace process should be used when using the Workspaces feature in ReportWORQ so that the job being executed can be located.

2. Information about ReportWORQ - Add Queued Job's additional parameters:

   • pJobDefinition: The job definition parameter tells ReportWORQ what to run, and uses the following specification to support optional parameter overrides:

     • Job Name;Job Name!ParamName|ParamOverrideValue,ParamName!ParamOverrideValue

     • Sample pJobDefinition parameter values:

       • Specify a Job without overrides, by name: Monthly Sales Job

       • Specify the path of a Job without overrides: Monthly Reporting/Sales/Monthly Sales Job

         • Tip: the full path of the job name can be obtained by selecting Copy Job Path from the context menu of the job in the Job Editor tree.

       • Override a SalesRep parameter value: Monthly Sales Job;Monthly Sales Job!SalesRep|1001

       • Override the SalesRep and Period parameter values: Monthly Sales Job;Monthly Sales Job!SalesRep|1001,Period|Jan-2023

   • pNotificationEmail: Email address used by ReportWORQ to optionally send notifications when the job is complete.

   • pWorkspaceName: The name of the workspace where the job is located. The Add Queued Job for Workspace process should be used when more than one workspace has been configured.

   • Please note that the delimiter for text lists of parameters is “**”. When adding multiple parameters ensure that they are separated by **.

This mechanism is specifically designed to support IBM Cloud customers where the Planning Analytics Cloud-hosted TI process would not otherwise have the ability to create a file or call a REST endpoint on the locally-hosted ReportWORQ Server.

No additional configuration in ReportWORQ is needed, once this feature is enabled for a Planning Analytics connection.