Page 1 of 1

TM1 Utilities

Posted: Thu Jun 25, 2015 11:45 pm
by paulsimon
This Excel Add-In includes a range of over 40 utilities to help you maximise your investment in IBM Cognos TM1 or IBM Cognos Express. Highlights include:

• Reporting
o Update DBRW formula when a new dimension is added to a cube
o Hide Workings, Freeze Panes and Protect the sheet which one click, before publishing your spreadsheet report
o Find the source of errors in DBRW formula
o Generate TM1 Spreadsheets for each Dept, etc, and Email them out
• Loading
o Generate a Template Loader
o Copy spreadsheet Email attachments to a folder
o Load data from all spreadsheets in a folder into TM1
• Impact Analysis
o Find out which Text and Excel files reference a given cube, etc.
• Replacer for Text and Excel files eg replace Dev Server with Prod Server
• Regression Testing eg Development vs Production
o Generate a sheet for the Default View on each Cube
o For example, run on both Dev and Prod for comparison
o Report differences in these or any other spreadsheet reports even BI reports
• Document
o List Cubes, their Dimensions, and Measures
o Generate an indented dimension hierarchy
• System Utilities
o Clear TM1 Cache to get Excel back in step with TM1
o Clear Clipboard to resolve annoying cannot empty clipboard errors
o Automatically set Excel Options such as no recalculation before save.
• Develop
o Can’t delete a Subset because it is used by a View? Find out which View.
o Generate TI Create Script for all Cubes and Views
o Generate TI statements to Add Cube Dependencies from TM1 Server log
o Scan TM1 Server Log for TI Calls
o List Chores, Run Times and Frequencies
• Additional Excel-TM1 Functions, such as ELISANC_zTD
• Includes TM1EasyAPI
o Provides an Object Model over the raw TM1 API

• Easy deployment - all pure VBA so no installation process is required

Download your trial version from now!

Contact us at

Re: TM1 Utilities

Posted: Wed Aug 05, 2015 9:18 am
by Paul Segal
Am I missing something Paul - can't see a trial version on your site?


Re: TM1 Utilities

Posted: Sat Oct 10, 2015 10:36 pm
by paulsimon
Hi Paul

Sorry we were having some problems with the web hosting service, but the product page is there now. You can download an evaluation version.

Recent enhancements include :
  • Generate Cube and View Create TI statements for a selected list of Cubes instead of all Cubes.
  • Remove Conditional Formats from all Active Form Data Ranges on a sheet, to get over the bug where the Conditional Formats from the Format Range can get duplicated in the Data Range every time you Rebuild the Active Form.
  • Find Cubes using a given Dimension.
  • Generate Application Shortcuts for TM1 Objects which can then be dragged into File System Application Folders - somewhat faster than dragging in Perspectives.
  • Deactivate Chores before starting the server, eg when you don't want any chance of Chores running on a Dev Server

Paul Simon

Re: TM1 Utilities

Posted: Sun Jan 27, 2019 8:11 pm
by paulsimon
Dear all

Version 128.1 has been released. Recent enhancements include :

Check Security - We frequently get questions from users asking why they cannot write to a certain cell. This provides an easy way to find out:

Open the sheet that they are working on (or if it is a view, slice it to Excel). Select the cell that has a DB function, eg DBRW, then select Chk Security on the zTD_TM1_Utils Menu. You will be prompted to select the user. It will then generate a report which will check the following, that the cell is not locked in Excel, that the user has at least Write access to the cube, across all the Groups of which they are a member, that the cube is not locked or reserved, and for each Element that identifies the cell, it will check that the user has at least Write access, and that the element is not locked or reserved. It will check that the user is not being blocked by Cell level Security. A process is provided to check for Cell Data Reservations.

Show/Hide Workings

Typically in a TM1 linked spreadsheet there are areas where you put cube references etc and other calculations that you do not want to be visible when the finished sheet is published. The Hide utility provides an easy way to hide those areas, it also applies FreezePanes and protects the sheet. The Show utility makes the workings visible again for future amendment.

To use the utility you put the word 'HIDE' at the top of any column and in the left most cell of any row you want to hide. You then specify two ranges, one to identify the cell that should be the top left visible cell - typically the one with the title of the report, and the other to identify the FreezePanes point, typically the one that contains the first DBRW.

While working with Planning Analytics TM1 Web we realised that it was not correctly recalculating cells that were hidden. We therefore introduced an alternative markup of 'THIN', which means that the column or row is made so thin that for all practical purposes it is invisible but it is not actually hidden and therefore it is still recalculated in Planning Analytics TM1 Web.

Paste Special Values

The utility includes the option to Paste Special Values either an individual sheet or all sheets in a workbook.

This now copes with cases where a sheet has a Data Filter applied. It saves the filter details, removes the filter, paste special values the cells, and then re-instates the filters.


Paul Simon
Success Cubed Ltd