IBM Cognos TM1

IBM Planning Analytics Tips & Tricks: Excel Keyboard Navigation

January 5, 2021 by Lee Lazarow Leave a Comment

Have you ever wanted to quickly go to the last used cell within a spreadsheet but did not know how? Have you ever wanted to determine where the next available row or column resides? Have you ever wondered why a spreadsheet with only a few cells of data may appear as a large file? All these answers can be provided with a single keystroke combination.

Pressing Ctrl and End at the same time will bring you to the last cell within a spreadsheet that Excel is referencing. The definition of “last” entails the rightmost column and the lowest used row on the worksheet. The last cell may not always contain data, but it will always be registered by Excel as something that is utilizing memory.

Here are some other keystroke combinations that will help you quickly navigate around your spreadsheet:

Ctrl and Home at the same time: this will bring you to the first cell (A1) in the worksheet.
End then an arrow key: this will bring you to the next empty value in any direction. For example, pressing End and then the down arrow will bring you to next empty cell in the column where your cursor currently resides.
Ctrl and PageUp : this will bring you to the previous sheet in the workbook.
Ctrl and PageDn: this will bring you to the next sheet in the workbook.

Keyboard shortcuts can easily help you navigate your Excel files and help you determine the “ending” cell within a spreadsheet. This will be useful in situations such as dynamic reports where rows and columns are automatically generated via automation.

IBM Planning Analytics, which TM1 is the engine for, is full of new features and functionality. Not sure where to start? Our team here at Revelwood can help. Contact us for more information at info@revelwood.com. And stay tuned for more Planning Analytics Tips & Tricks weekly in our Knowledge Center and in upcoming newsletters!

IBM Planning Analytics Tips & Tricks: Change Element Type in PAW

December 22, 2020 by Revelwood Leave a Comment

Most users of IBM Planning Analytics Workspace (PAW) are familiar with the dashboarding and reporting capabilities for front-end users. However, IBM has built-in features that allow administrators to manage their models from PAW as well. Managing the dimension structure is one such feature. Though most of it is intuitive, updating the element type is a bit hidden.

To access the dimension editor, first open a workbook in PAW. Then in the left navigation pane, expand the “Dimensions” node to find the dimension that you would like to edit.

IBM Planning Analytics Tips & Tricks: Change Element in PAW

Drag the dimension to the work area pane on the right. You’ll see a warning stating that any changes will apply instantly unless you have reserved the dimension. Reserving the dimension locks the dimension so that no one else can make changes.

IBM Planning Analytics Tips: Change Element Type in PAW

In the PAW dimension editor, click on the icon to show the properties window. Select a member to show or change its properties. Please note that PAW doesn’t have “Consolidated” as a selectable type in the dropdown. PAW will automatically convert an element to a consolidation when you add children to it. Conversely, it will change a consolidation to a simple element when you remove all children.

IBM Planning Analytics Tricks: Change Element Type in PAW

IBM Planning Analytics Tips & Tricks: Rule Editor Keyboard Shortcuts

December 15, 2020 by Lee Lazarow Leave a Comment

Are you an “old school” keyboard person that still presses Ctrl-S to save your Excel workbooks? Do you flip through windows by pressing Alt-Tab? Do you remember Lotus backslash macros that used commands such as /FS for File, Save? If so, did you know that you can still do things like this within the new IBM Planning Analytics rules editor?

The easiest way to get started with keyboard commands is to press Ctrl-K within a rules window. This will open an area that shows a list of keys that can be used within the rule.

Here are some of the combinations that I use regularly when writing rules:

Ctrl-F then Ctrl-G: Ctrl-F will find a specific word or phrase and Ctrl-G will then allow you to scroll through the rule to find the next instance of that phase. This is helpful when you have a long rule and want to go directly to a section (example: quickly go to the FICA calcs in a comp model).

Ctrl-Shift-R: Ctrl-Shift-F will allow you to find and replace a single instance of text, but Ctrl-Shift-R allows you to do a find and replace within the entire rule (example: I have ‘Plan’ in the left side of many rules, but I want to replace it with ‘Plan2’).

Ctrl-Home and Ctrl-End: These combinations allow you quickly navigate to the start or the end of the rule. It’s an easy way to look for your SKIPCHECK command or go directly to your feeders section.

Ctrl-Z and Ctrl-Y: Ctrl-Z is the equivalent of the undo button and Ctrl-Y is the undo of the undo (or a redo). I sometimes get careless with Ctrl-Z and undo too much. This is where Ctrl-Y comes into play.

Ctrl-A: This will select the entire rule. I used this a lot with Perspectives since I often had to bring my rule into another text editor, but PA’s new formatting and line numbering has minimized the amount of times I use this combination.

If you are a keyboard person then it is good to know that Planning Analytics’ rule editor still allows you to use these combinations. And if you are not a keyboard person then maybe some of these will help improve your efficiency.

IBM Planning Analytics Tips & Tricks: Log Lock Notification

December 8, 2020 by Lee Lazarow Leave a Comment

Have you ever tried to move or download log files and then realize that some of the files cannot be modified since they are in use? IBM Planning Analytics Workspace (PAW) recognizes this and now offers notifications to let you know which files are locked.

You can download log files by first selecting the PA Administration button at the top of your screen.

IBM Planning Analytics Tips & Tricks: Log Lock Notification

One inside the administration area, you can view the logs by clicking on the three dots at the top, right corner of your server and selecting the option called “Download logs.”

From there, you can simply select the logs that you want to download and then select the “download” button at the bottom of the screen. Any locked log files will show the word “locked” on the screen and will also prevent you from selecting that log. The example below shows the tm1server.log file checked and shows the tm1s.log file as locked.

IBM Planning Analytics Tricks: Log Lock Notification

This approach will help you easily remember which files are accessible and which are not.

IBM Planning Analytics Tips & Tricks: Asymmetric Axis in PAW

December 1, 2020 by Revelwood Leave a Comment

One of the helpful features of IBM Planning Analytics is the ability to create explorations using asymmetric axes. This is useful when you have stacked column or row dimensions, and only want to display certain combinations of elements instead of all combinations of the stacked dimensions.

To do this in Planning Analytics Workspace (PAW), open a workbook in PAW and open an exploration. Click on the icon to make the row dimensions asymmetric or the icon for the column dimensions. Next, hold down the CTRL key and select the rows or columns you want to keep or hide.

IBM Planning Analytics Tips & Tricks: Asymmetric axis in PAW

Right-click on one of the selected columns and select “Keep” or “Hide.”

If you need to revert back to the normal view, right-click on any row or column and select “Unhide All.”

IBM Planning Analytics Tips & Tricks: Convert Existing View Directly to Reports

November 24, 2020 by Revelwood Leave a Comment

Did you know that Planning Analytics for Excel (PAx) provides the ability to convert an existing view directly to a report in Excel? Without opening the view as an Exploration, you can start building your report directly from the Task Pane.

Just right-click on the view in the Task Pane to display the available options.

IBM Planning Analytics Tips & Tricks: Convert Existing View to Reports

You will see the following options by hovering over the Quick report, Dynamic report, and Custom report items:

How to convert existing views into reports in Planning Analytics

In this example, each of the items does the following:

On this sheet: This will open the existing Planning Analytics view as a Quick report on the current Excel sheet at cell $A$1.
On new sheet: This will open the view as a Quick report on a new sheet at cell $A$1.
At current location: This will open the view as a Quick report at the cell that is currently active.
At specified location: This will open a pop-up window that allows you to input the cell at which you would like the Quick report to appear. You can then enter the cell address and click OK to generate the Quick report, as shown in the following image.

Learn how to convert existing views to reports in Planning Analytics

IBM Planning Analytics Tips & Tricks: PAx Task Pane Workbook Tab

November 17, 2020 by Revelwood Leave a Comment

Did you know that Planning Analytics for Excel (PAx) automatically keeps track of the Planning Analytics objects you have open in an Excel workbook?

Simply click on the Workbook tab of your Task Pane to view the list of Dynamic reports, Action buttons, Explorations, and Quick reports that are currently in use.

IBM Planning Analytics Tips & Tricks: PAx Task Pane Workbook Tab

These folders will be populated as you work with your Planning Analytics environment. Whether you manually add these items to a worksheet, or they are included in an existing view or report, they will appear here.

For example, clicking the Exploration button in the PAx ribbon will create an Exploration on a new worksheet.

The PAx task pane workbook tab in IBM Planning Analytics

This will also add an item to the Explorations folder on the Workbook tab of the Task Pane. When you right-click on the name of the new Exploration, you will see the following options:

Learn about the PAx task pane workbook tab in IBM Planning Analytics

Similar options appear for Dynamic reports, Action buttons, and Quick reports.

IBM Planning Analytics Tips & Tricks: Alternative Row Formats for Excel Reports

November 10, 2020 by Lee Lazarow Leave a Comment

In the early days of computing, printers often used paper that had alternating white and green areas which allowed the reader to easily follow a line of text across the page. This was known as “green bar” paper. While we no longer use this type of paper, the concept is still applicable for situations where your output contains many columns that are hard to follow.

You can create this approach by using alternate row formats within your Excel reports. This is done by combining two Excel formulas:

The ROW formula returns the row number for a cell reference. For example, ROW(K5) returns the number 5 since the cell is in the 5^th row of the spreadsheet.
The MOD formula returns the remainder of two numbers after division. For example, MOD(9,2) returns the number 1.

When combined, the following formula will result in either a 1 or a 0:

=MOD(ROW(cell),2)

From there, you can use conditional formatting to define the fill colors. If you are doing this in standard Excel, then you can copy the formula throughout the applicable area. If you are doing this within a dynamic report, then you can use it within the formatting area.

This approach allows you to make your wide reports easier to read. This also means that you can recreate the green bar format while you pull out your dot matrix printer and watch classic movies such as Wargames!

IBM Planning Analytics Tips & Tricks: Minimizing the Subset Area

November 3, 2020 by Lee Lazarow Leave a Comment

Are your member names long and hard to see in the subset editor in IBM Planning Analytics? Does it get confusing to see both the available members and the current set at the same time? Both situations can be solved by hiding one side of the subset editor.

Here is an example of the subset editor which shows both the available members and the current set.

IBM Planning Analytics Tips & Tricks: Minimizing the Subset Area

Each of the two sections has an option to minimize the area. This is done by clicking on the minus symbol at the top of the applicable section.

IBM Planning Analytics: Minimizing the Subset Area

Once clicked, the applicable area will compress and the other area will expand. Note that you can only minimize one side at a time. Below are examples of what each screen will look like when compressed.

Minimized Available Members:

IBM Planning Analytics Tips: Minimizing the Subsea Area

Minimized Current Set:

Minimizing the subset area in IBM Planning Analytics

This approach will allow you to further customize your subset editor screen to allow you to focus only on the area you want to see.

Read more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips & Tricks: Maintaining Subset-Drive Consolidations

IBM Planning Analytics Tips & Tricks: Sort Elements within a Subset

IBM Planning Analytics Tips & Tricks: Subset Control Dimension

IBM Planning Analytics Tips & Tricks: Excel Keyboard Navigation

Read more Excel Tips & Tricks:

IBM Planning Analytics Tips & Tricks: Change Element Type in PAW

Read more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips & Tricks: Rule Editor Keyboard Shortcuts

Read more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips & Tricks: Log Lock Notification

Read more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips & Tricks: Asymmetric Axis in PAW

Read more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips & Tricks: Convert Existing View Directly to Reports

Read more IBM Planning Analytics Tips & Tricks posts:

IBM Planning Analytics Tips & Tricks: PAx Task Pane Workbook Tab

Read more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips & Tricks: Alternative Row Formats for Excel Reports

Read more IBM Planning Analytics Tips & Tricks posts:

IBM Planning Analytics Tips & Tricks: Minimizing the Subset Area

Revelwood Overview

EXPERTISE

ABOUT

CONNECT

IBM Cognos TM1

Read more Excel Tips & Tricks:

Read more IBM Planning Analytics Tips & Tricks:

Read more IBM Planning Analytics Tips & Tricks:

Read more IBM Planning Analytics Tips & Tricks:

Read more IBM Planning Analytics Tips & Tricks:

Read more IBM Planning Analytics Tips & Tricks posts:

Read more IBM Planning Analytics Tips & Tricks:

Read more IBM Planning Analytics Tips & Tricks posts:

Footer

Revelwood Overview

EXPERTISE

ABOUT

CONNECT