Revelwood

Your SUPER-powered WP Engine Site

IBM Cognos TM1

IBM Planning Analytics Tips & Tricks: Excel TEXTBEFORE & TEXTAFTER

by

A while back, I wrote a blog about using Excel to manipulate text. The functions in that blog allow you to rip text apart and put the pieces together so you can parse out pieces of your text strings. Since that writing, Microsoft has introduced two additional functions that can help you search for specific text values. These functions are TEXTBEFORE and TEXTAFTER.

  • TEXTBEFORE will return text before the characters you are searching for
  • TEXTAFTER will return text after the characters you are searching for

Both functions have two required parameters:

=TEXTBEFORE(text,delimiter)

=TEXTAFTER(text,delimiter)

  • The text parameter defines the string value you are searching for
  • The delimiter parameter defines the point after you want to extract

In addition, there are optional parameters that can be used to determine case sensitivity and what to do if no match if found.

The following example shows how to use these functions:

Graphical user interface, text, application, chat or text message Description automatically generated

This approach will simplify your searches by eliminating the need to merge functions such as FIND, and LEN and LEFT/RIGHT together.

Revelwood has worked with IBM Planning Analytics / TM1 for more than 27 years. We’ve partnered with hundreds of companies on the design, development, maintenance and updates of IBM Planning Analytics applications, across every industry. Have a challenge with Planning Analytics / TM1? We can help you!

Read more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips & Tricks: How to Set Up Action Buttons in Planning Analytics for Excel

IBM Planning Analytics Tips & Tricks: Excel SEQUENCE

IBM Planning Analytics Tips & Tricks: PAW Go To Line in Process

Home » IBM Cognos TM1 » Page 9

IBM Planning Analytics Tips & Tricks: How to Set Up Action Buttons in Planning Analytics for Excel

by

Revelwood’s latest IBM Planning Analytics Tips & Tricks video features Dillon Rossman, a consultant with our IBM Planning Analytics practice, demonstrating how to set up action buttons in IBM Planning Analytics for Excel (Pax).

In this video, Dillon shows your three approaches: 

  • Default approach
  • Creating the action button in a different workbook or sheet
  • Run a process and then go to another worksheet

Revelwood has worked with IBM Planning Analytics / TM1 for more than 27 years. We’ve partnered with hundreds of companies on the design, development, maintenance and updates of IBM Planning Analytics applications, across every industry. Have a challenge with Planning Analytics / TM1? We can help you!

Watch more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips & Tricks: How to Set Up Synchronizations in IBM Planning Analytics Workspace

IBM Planning Analytics Tips & Tricks: Filtering in IBM Planning Analytics Editor

IBM Planning Analytics Tips & Tricks: Global Settings in Overview Area of Exploration

Home » IBM Cognos TM1 » Page 9

IBM Planning Analytics Tips & Tricks: How to Set Up Synchronizations in IBM Planning Analytics Workspace

by

Revelwood’s latest IBM Planning Analytics Tips & Tricks video features Dillon Rossman, a consultant with our IBM Planning Analytics practice, demonstrating how to set up synchronizations in Planning Analytics Workspace (PAW).

In this video, Dillon walks you through how to:

  • Change an exploration to a pie chart
  • Edit the synchronization properties
  • Select the dimensions you want to be synchronized
  • Perform synchronizations on both a sheet and a book

Revelwood has worked with IBM Planning Analytics / TM1 for more than 27 years. We’ve partnered with hundreds of companies on the design, development, maintenance and updates of IBM Planning Analytics applications, across every industry. Have a challenge with Planning Analytics / TM1? We can help you!

Watch more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips & Tricks: Filtering in Planning Analytics

IBM Planning Analytics Tips & Tricks: Global Settings in Overview

IBM Planning Analytics Tips & Tricks: Color in Planning Analytics

Home » IBM Cognos TM1 » Page 9

IBM Planning Analytics Tips & Tricks: Excel SEQUENCE

by

Have you ever needed to quickly generate a list of numbers? Maybe you need to create a list from 1-100. Maybe you want to create a list of the first 100 even numbers. Maybe you want to create those same lists in descending order. Did you know that Excel has a function called SEQUENCE which allows you to quickly create these lists?

The SEQUENCE function is used to generate an array of sequential numbers.  The syntax of the function is:

=SEQUENCE (rows, [columns], [start], [step])

  • Rows (required)
    • This is the number of rows to be filled
  • Columns (optional)
    • This is the number of columns to be filled
    • If skipped, this defaults to 1
  • Start (optional)
    • This is the starting number in the sequence
    • If skipped, this defaults to 1
  • Step (optional)
    • This is the increment for each subsequent value
    • If skipped, this defaults to 1
    • This can be negative to provide a descending list

The following example shows a single column list from 1-10

Chart Description automatically generated with medium confidence

The same formula doubles the result when the column parameter is defined. Note that the results read from right to left (e.g., the entire row) and then goes down.

Table Description automatically generated with medium confidence

Here is the same formula when adding both a starting value and an increment.

Table Description automatically generated

Not only can this formula be used to quickly generate lists, but it can also be used as part of a combination:

  • With the DATE formula to define a calendar
  • With the TIME formula to create a daily schedule
  • With data from another column to create unique IDs for items like products or employees

Read more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips & Tricks: Creating Control Objects from the Modeling Workbench

IBM Planning Analytics Tips & Tricks: Shortcuts in PAW Models

IBM Planning Analytics Tips & Tricks: Customizing Background Colors for Data and Header Cells

Home » IBM Cognos TM1 » Page 9

IBM Planning Analytics Tips & Tricks: PAW Go To Line in Process

by

Sometimes your TurboIntegrator scripts can become very long. While it’s nice to be able to scroll up and down throughout your code, there are times that you may want to go directly to a specific line. IBM Planning Analytics Workspace (PAW) version 79 allows this via the “Go to Line” button.

Once clicked, you are prompted to select a section and a line within the section.  

When defining a line, note that the line numbers restart within each section. In the example below, each set of generated statement lines begin at line 2 (since I like to skip a line for easy reading) and the first line of code in the Data section begins at line 5.

This approach will help you speed up your navigation as you write code in TurboIntegrator.

Revelwood has worked with IBM Planning Analytics / TM1 for more than 27 years. We’ve partnered with hundreds of companies on the design, development, maintenance and updates of IBM Planning Analytics applications, across every industry. Have a challenge with Planning Analytics / TM1? We can help you!

Read more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips & Tricks: Planning Analytics Workspace Borders in Preview

IBM Planning Analytics Tips &Tricks: Rounded Buttons in Planning Analytics Workspace

IBM Planning Analytics Tips & Tricks: MDX Syntax Explained

Home » IBM Cognos TM1 » Page 9

IBM Planning Analytics Tips & Tricks: Planning Analytics Workspace Borders in Preview

by

IBM Planning Analytics Workspace (PAW) allows you to easily create selector widgets that can be used for updating the data in your PAW book. This is done by adding a dimension as a selector widget.

You can edit the list of elements (e.g., the set) and the way the list appears. The selector widget allows you to easily select an element that is synchronized to your charts and explorations. When selecting the element, a border appears around the widget.

Diagram Description automatically generated with low confidence

While this border offers features such as the ability to expand the screen, it clearly makes the list into a separate section of the page. You can remove the border by using the property called “Show selected border in preview” (within the area called General, Appearance). Here is the same list without the border enabled:

Text Description automatically generated

This property will allow you to create the perception of a single page and not a set of independent objects.

Revelwood has worked with IBM Planning Analytics / TM1 for more than 27 years. We’ve partnered with hundreds of companies on the design, development, maintenance and updates of IBM Planning Analytics applications, across every industry. Have a challenge with Planning Analytics / TM1? We can help you!

Read more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips &Tricks: Rounded Buttons in Planning Analytics Workspace

IBM Planning Analytics Tips & Tricks: MDX Syntax Explained

IBM Planning Analytics Tips & Tricks: Global Settings in Overview Area of Exploration

Home » IBM Cognos TM1 » Page 9

IBM Planning Analytics Tips &Tricks: Rounded Buttons in Planning Analytics Workspace

by

IBM Planning Analytics Workspace (PAW) allows you to easily create buttons that can be used for navigation and to run TurboIntegrator (TI) scripts. This is done by dragging the Action Button widget onto the sheet.

By default, the button appears as a rectangle.  

Shape Description automatically generated

You can make the button more rounded by using the property called “Corner Radius” (within the area called General, Appearance).  Here is the same button with a corner radius setting of 90:

Shape, rectangle Description automatically generated

You can also update the height of the button to make it appear even more rounded:

Shape, circle Description automatically generated

From there, you can further customize the formatting associated with colors, fonts, and text size.

This property will allow you to define various ways to incorporate simple “press this button to…” approaches within your PAW books.

Revelwood has worked with IBM Planning Analytics / TM1 for more than 27 years. We’ve partnered with hundreds of companies on the design, development, maintenance and updates of IBM Planning Analytics applications, across every industry. Have a challenge with Planning Analytics / TM1? We can help you!

Read more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips & Tricks: Working with Two Time Zones in Google Calendar

IBM Planning Analytics Tips & Tricks: New Filter Option in Set Editor

IBM Planning Analytics Tips & Tricks: 21/21/21

Home » IBM Cognos TM1 » Page 9

IBM Planning Analytics Tips & Tricks: MDX Syntax Explained

by

MDX enables you to query dimensions and cubes, lists of elements, data points, calculations, and more. 

The first step in learning MDX is to understand the syntax. Below is an example of an MDX expression that returns all elements of the product dimension sorted in ascending order. To understand the expression, we’ve color-coded the syntax below. 

Part1, the part that is associated with the TM1 structure and model, is in green. This defines cubes, dimensions, hierarchies, elements and more. 

Part 2, in blue, is the MDX Function.

Part 3 is in red. These are known as curly brackets or braces. These convert the results of the MDX Expression into a set. 

The Expression below lets you list all elements of the product dimension and sort the list in ascending order:

Graphical user interface, text, application, email Description automatically generated

Revelwood recently hosted a webinar on How to Use MDX in your IBM Planning Analytics Environment. Watch the recording to learn more about using MDX.

Interested in learning more about MDX? We have another MDX-specific webinar on Wednesday, November 9, 2022 at 1pm ET. Attendees will learn advanced MDX functions and see real-life examples of how Revelwood used MDX to address complex reporting requirements and to solve client challenges. 

Have a question about MDX? Register for our upcoming webinar or reach out to us at info@revelwood.com

Revelwood has worked with IBM Planning Analytics / TM1 for more than 27 years. We’ve partnered with hundreds of companies on the design, development, maintenance and updates of IBM Planning Analytics applications, across every industry. Have a challenge with Planning Analytics / TM1? We can help you!

Read more IBM Planning Analytics Tips & Tricks on MDX:

IBM Planning Analytics Tips & Tricks: An Approach to Strengthening your MDX Skills

IBM Planning Analytics Tips & Tricks: Making Default Cube Views Dynamic with MDX

IBM Planning Analytics Tips & Tricks: Dynamic Subsets Based on a Cube

Home » IBM Cognos TM1 » Page 9

IBM Planning Analytics Tips & Tricks: Global Settings in Overview Area of Exploration

by

Do you know how to define your global settings in the overview area of an IBM Planning Analytics Exploration? Revelwood’s FP&A Technology Director, Lee Lazarow, demonstrates how to manage and define these settings. 

Watch our latest IBM Planning Analytics Tips & Tricks video to learn:

  • Locate the gear icon in the overview area of your Exploration
  • Identify the four different global settings you can define
    • Hierarchy name – shows details about each dimension
    • Iconography – gives details about each one of the dimensions, similar to the legacy version of Subset Editor
    • Context information – provides details about the database, which database it is references, which cube it is referencing
    • Section headers – shows within the area what is the context, or what was previously called titles or picks.

When you define your global settings in IBM Planning Analytics, you create a better experience for your end users. It enables end users to determine what they are looking at and what they are navigating through.

Revelwood has worked with IBM Planning Analytics / TM1 for more than 27 years. We’ve partnered with hundreds of companies on the design, development, maintenance and updates of IBM Planning Analytics applications, across every industry. Have a challenge with Planning Analytics / TM1? We can help you!

Watch more IBM Planning Analytics Tips & Tricks Videos:

IBM Planning Analytics Tips & Tricks: Add Color to Multidimensional PAW Charts

IBM Planning Analytics Tips & Tricks: Shortcuts in PAW Models

Home » IBM Cognos TM1 » Page 9