August 20th, 2020
Many of you use Excel when working with Planning Analytics. You may be creating budgets, exporting files, creating reports, or uploading info via Excel. It can be a little overwhelming figuring out all of the different Planning Analytics Excel options. Below I am going to outline all the different options to help you in your Planning Analytics journey.
We recently had a client looking to create offline reports for people to enter their budgets and then reconnect to the TM1 server and update the info. We worked with them to create quick reports to send to people and once they send back, we will reconnect to TM1 and update the info.
Planning Analytics Excel – PAX
Planning Analytics for Excel, known as PAX, is the new Excel add in for Planning Analytics. Unlike Perspectives, the old and slower prior version of Excel for TM1, PAX offers a seamless Excel add in experience that works great over both local and wide area networks. PAX also offers several different report types and options. Below you will find details of each report option.
- Exploration Report - quickly change how you view performance measures and to show detailed information from your database in rows and columns. Explorations offer slice and dice capabilities to easily change the look and feel of your reports and create quick analysis. You can learn more here.
- Quick Report - combine data from multiple data sources in a Quick Report and then enhance the data by using Microsoft Excel formulas, formats, and cell references. When you create or open a Quick Report, the names of the dimensions used in the Quick Report are automatically displayed at the top of the report. Click here for more information.
- Dynamic Report – create complex reports that combine the functionality of IBM® TM1® with Microsoft Excel features. Dynamic Reports are implemented through a series of worksheet functions that define the components of a form such as context members, row members and display properties. When you convert an Exploration View to a Dynamic Report, the functions are created for you. Dynamic Reports support features available in Explorations and Quick Reports, such as selectable context members, stacked row and column dimensions, expandable/collapsible consolidations (rows only), zero suppression (rows only) and inserting rows and columns (with some restrictions).
- Custom Report - create reports by using TM1® worksheet functions to populate the cells of the report. You can create a Custom Report by using the following methods - converting an Exploration View to a Custom Report, converting a view in the source tree to a Custom Report or manually entering formulas to build a report. Learn more here.
Exporting to Excel
There are a couple of ways to export to Excel. You can use the PAX option as detailed above. You can also use TM1 Web and Planning Analytics Workspace (PAW) to export. Both options allow an export on to multiple tabs based on a dimension you choose. There are some limits and I will detail all of this below.
- PAW – The procedure to export from PAW is detailed here.
Couple of items to note. There are size limitations to the export.
- Max rows – 1,048,576
- Max columns – 16,384
- Max # of cells – 8 million
- Max # of views (tabs) – 100
The screen shot below would result in 15 tabs in 1 excel workbook. You do have the option to separate these into multiple workbooks.
2. TM1 Web – The option to export to Excel in TM1 is similar to PAW except you are only allowed 50 views in total.
Import to Create Dimensions
Did you know you can create dimensions from csv or text files? Simply click, create new dimension, in PAW and click, browse for file and upload directly in to Planning Analytics.
Summary
Excel is not going away and if we can learn to use it in conjunction with Planning Analytics, the power of both can take your planning and analysis to the next level. If you need to discuss your particular case please reach out to us at services@lodestarsolutions.com For more information on TM1, Excel and Planning Analytics check out our long library of blogs.