TM1 Tools add in releases

Ideas and tips for enhancing your TM1 application
Locked
User avatar
Alan Kirk
Site Admin
Posts: 5801
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: 9.5.2 64 bit moving to 10.2.2
Excel Version: 2010
Location: Sydney, Australia
Contact:

TM1 Tools add in releases

Post by Alan Kirk » Sun Mar 14, 2010 9:56 pm

Below is the latest version of Martin, Steve and my add-in. This release includes the bulk paste tool, which will paste data in the clipboard into a range of DBR, DBRW and DBRA cells. It also includes the Hierarchy Documenter, and the various processes for converting formulas to values as well as the traditional KEY_ERR tracer.

Edit: Known Issues
- The group and outline feature of the Hierarchy Documenter can sometimes miss the last group. This isn't critical as the actual hierarchy data is correct, but will be fixed in a later release.
- The PDF document lacks some of the links found in the .chm help file. It will sometimes refer to things being found "here" where, in the .chm file, "here" would be a hyperlink. The references will be reworded in a later release.
- The Bulk Paste feature Does Not Like It if you've copied a range of blank cells and are trying to paste them into String cells to clear them. This is a fairly easy fix and will be taken care of in the next release. Actually I've identified a couple of issues when there are gaps in the source data, but they should all be fixed next time. Most of you probably won't come across them.
- The Bulk Paste Strings button has been incorrectly assigned the standard Bulk Paste function. That's already fixed in development.
- Ensure that your calculation mode is MANUAL when you use any of the Clear Formulas methods. The next version of the tool will set this temporarily to manual before it clears the formulas, then set it back when it's done. However you should avoid using Automatic calculation mode with TM1 anyway.
- Not so much an issue but a design modification; on some large sheets where you need to do multiple pastes, I've found the sheet recalc function which is done after each paste can be a pain. I'm therefore going to include an option to turn that off, allowing you to manually recalc the sheet / workbook when you're done.
- 14-Apr: The Bulk Copier does not seem to care for formatted numbers. This will be fixed in the next release. In the meantime, ensure that you strip the formatting off before you copy the data.

Bear in mind that the bulk of the Bulk Paste tool was put together in a few hours so yes, it may still have some issues. Feel free to post a modification request if you come across any.

Admin Edit:

This topic is locked for replying and will only be used for new releases. Please discuss problems and general use of the tool here.
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.

User avatar
Alan Kirk
Site Admin
Posts: 5801
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: 9.5.2 64 bit moving to 10.2.2
Excel Version: 2010
Location: Sydney, Australia
Contact:

Re: TM1 Tools add in releases

Post by Alan Kirk » Mon Aug 16, 2010 4:14 am

Version 0.9 is attached. {Removed with the release of 1.0; final download count was 166} It gets rid of most of the annoyances of V0.8 and introduces a couple of new features. The V0.8 download has been removed from the thread.

Known Issues
- The group and outline feature of the Hierarchy Documenter can sometimes miss the last group. This isn't critical as the actual hierarchy data is correct, but will be fixed in a later release. Still outstanding.
- The PDF document lacks some of the links found in the .chm help file. It will sometimes refer to things being found "here" where, in the .chm file, "here" would be a hyperlink. The references will be reworded in a later release. Believe that I got all of these in version 0.9.
- The Bulk Paste feature Does Not Like It if you've copied a range of blank cells and are trying to paste them into String cells to clear them. This is a fairly easy fix and will be taken care of in the next release. Actually I've identified a couple of issues when there are gaps in the source data, but they should all be fixed next time. Most of you probably won't come across them. Fixed in version 0.9.
- The Bulk Paste Strings button has been incorrectly assigned the standard Bulk Paste function. That's already fixed in development. Fixed in version 0.9.
- Ensure that your calculation mode is MANUAL when you use any of the Clear Formulas methods. The next version of the tool will set this temporarily to manual before it clears the formulas, then set it back when it's done. However you should avoid using Automatic calculation mode with TM1 anyway. Fixed in version 0.9.
- Not so much an issue but a design modification; on some large sheets where you need to do multiple pastes, I've found the sheet recalc function which is done after each paste can be a pain. I'm therefore going to include an option to turn that off, allowing you to manually recalc the sheet / workbook when you're done. Feature added in version 0.9.
- 14-Apr-10: The Bulk Copier does not seem to care for formatted numbers. This will be fixed in the next release. In the meantime, ensure that you strip the formatting off before you copy the data. Fixed in version 0.9.

New Features
Version 0.9, August 2010

Calculation Timers Two timer icons which will calculate the time taken to recalculate a single sheet, or do a full calculation. These can help you optimise the format of TM1 reports. The time taken will be displayed in a dialog box and printed to the Immediate window in the Visual Basic Editor.

File Lister Tool This will list the files (or a subset of files matching one or more search patterns) in a specified folder into an Excel workbook. The tool can also optionally search sub-folders.
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.

User avatar
Alan Kirk
Site Admin
Posts: 5801
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: 9.5.2 64 bit moving to 10.2.2
Excel Version: 2010
Location: Sydney, Australia
Contact:

Re: TM1 Tools add in releases

Post by Alan Kirk » Thu Mar 10, 2011 7:54 pm

Version 1.0 / build 158 has been released.

Known Issues
- From version 0.9: The group and outline feature of the Hierarchy Documenter can sometimes miss the last group. This isn't critical as the actual hierarchy data is correct, but will be fixed in a later release. Fixed in version 1.0.

Changed Features
Version 1.0, February 2011
- The Options form now allows you to select which toolbar buttons you want to display. It also allows you to have a menu created along with the toolbar. A couple of additional options have been added.
- Toolbar icons should be consistent across all platforms. (Previously they changed depending on which version of Windows / Excel you were using.)
- The TraceFormulas tool has been rewritten to cover most of the TM1 functions, excluding those for dynamic slices / active forms.
- It will also show consolidations on a blue/grey background to highlight elements that may be causing writing issues.
- Zap Formulas now allows you a third option; to zap formulas on all but the current sheet. It also makes the use of the current settings optional the next time you run it so it's possible to save your usual settings (eg zapping a single worksheet), and have those come up as the default each time even if you (say) last zapped an entire workbook.
- In addition to the bug fix, some new features have been added to the Hierarchy Documenter.
-- The buttons that require your attention are highlighted in yellow;
-- You can now have the element names listed in column A and one of their attributes listed in column B to help with filtering the chart;
-- If the current workbook was created by the Hierarchy Docimenter, you can have new output sent to the same workbook;
-- Applying Excel group and outlining to the hierarchy is now optional.

New Features
Version 1.0, February 2011
- The ability to load text files (including .pro and .rux files) into an excel workbook and then search them for a specified text expression. This can help determine the TI processes or rules which affect a specified cube.
- Wrapper functions which call some of the TM1 API functionality, and which can be called from your own code if you set a reference to the TM1 Tools project (the process for which is explained in the help file). At present these are:
-- A function which you can call to execute a TurboIntegrator process.
-- A function which will return a list of the names of the available servers as a string array.
-- A function which will return a collection of custom TM1Client objects which have information about clients and the security groups that they are in. This function will only work for users with Admin access to the server.
- Another non-API wrapper function called GetOpenFolder which you can also call from your own VBA code to get the name of a folder. (Equivalent to using Excel's own GetOpenFileName.) You need to have a reference to the TM1 Tools project to do that. (This is explained in the help file.)
- A new worksheet function called IsFolderValid which can be used to check whether a directory path is valid.
Attachments
TM1Tools.zip
(3.72 MiB) Downloaded 3263 times
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.

Locked