TM1

Did you know IBM Planning Analytics for Microsoft Excel (PAx) provides several tools to view, maintain, and report on your Planning Analytics data? Dynamic Reports, one of the PAx reporting tools, allows you to create flexible reports with personalized formatting. An example of this is a ranked report, which will display and sort the top set of rows. To do this, simply insert a TM1RPTFILTER formula into a cell on the Excel worksheet containing the Dynamic Report.

The TM1RPTFILTER formula has five parameters that can be used to customize the filter:

1. ReportView – A reference to the cell containing the TM1RPTVIEW formula that applies to the Dynamic Report
2. Tuple – A string specifying the member of the column dimension to which the filter applies
3. FilterFunction – A string from the following list that describes the set of data to be displayed:

• TOPCOUNT
• BOTTOMCOUNT
• TOPPERCENT
• BOTTOMPERCENT
• TOPSUM
• BOTTOMSUM

4. FilterValue – The number of rows to be displayed
5. SortOrder – A string from the following list that describes the way in which the data should be sorted:
   • asc – Ascending
   • desc – Descending
   • nbasc – Ascending without breaking the hierarchy
   • nbdesc – Descending without breaking the hierarchy

The following is an example of a ranking function using TM1RPTFILTER. This report would display the top 20 rows, ranked by December’s data, in descending order.

=TM1RPTFILTER(RptView, “[Month].[Dec]”, “TOPCOUNT”, 20, “desc”)

This concept can easily be applied it to a variety of models to quickly and effectively report on the rankings within a data set.

For more information, refer to the IBM Planning Analytics 2.0.0 documentation or the TM1RPTFILTER documentation.

IBM Planning Analytics 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!

Read more IBM Planning Analytics Tips & Tricks blog posts:

IBM Planning Analytics Tips & Tricks: Converting Reports to PAx

IBM Planning Analytics Tips & Tricks: Creating Dynamic, Attractive Views and Dashboards

IBM Planning Analytics: Using the Full Power of MDX

This is a guest post by Revelwood’s Shane Bethea. 

Did you know you can use MDX to compare dimension hierarchies? IBM Cognos TM1 dimensions often have multiple hierarchies that should all roll up to the same total.  Said another way, these separate hierarchies should all contain the same set of n-level elements below them.  For example, a customer dimension may have a hierarchy for rolling up the ‘All Customers – by Type’ and another for ‘All Customers – by Region.’  A TI process can be used to maintain the elements in the dimension and these two hierarchies, but sometimes the two hierarchies may become out of sync.  MDX can be used to see if both hierarchies contain the exact same number of n-level customers and, if the two hierarchies are out of sync, identify the elements causing the problem.

In our example above, let’s say that a customer was added to the dimension within the ‘by Type’ hierarchy but not within the ‘by Region’ hierarchy.  If two separate reports were written to pull the total revenue for all customers then the reports would show inconsistent values.  This is because the ‘by Type’ hierarchy would have one more customer than the ‘by Region’ hierarchy.

To quickly figure out where the difference lies between the two hierarchies, an MDX statement can be used to compare the n-level elements within the two hierarchies.

The MDX statement above uses the ‘EXCEPT’ function and will yield the difference between the ‘All Customer – by Type’ hierarchy and the ‘All Customer – by Region’ hierarchy.  The result of the MDX statement is the one n-level element that is missing from the ‘by Region’ hierarchy.  From here, you can investigate why the element was added to one hierarchy and not the other.

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!

Did you know you can easily visualize data with IBM Planning Analytics Workspace?  IBM Planning Analytics Workspace (PAW) allows you to visualize your data using a variety of charts and graphs, including the ability to display your results via a map.  The only requirement for mapping is to ensure that your geography dimension includes recognizable place names.  IBM defines “recognizable” to include items such as countries, states and provinces and offers a list of acceptable items on their website.

You can create a map in PAW with three easy steps:

1. Ensure that your dimension type is defined as geography within the }DimensionAttributes cube.

2. Create a view in PAW with either your rows or columns displaying the geography dimension.

3. Set your visualization type to be a map.

The resulting display will appear as a map:

Planning Analytics will re-analyze the data set each time you refresh the values.  If at least 80% of the row or column members are recognized as geographic map values then PAW will re-create the map.  This will allow you to quickly shift between worldwide, nationwide and regional maps by simply changing and suppressing the values you want to display.

IBM Planning Analytics  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!

Read more IBM Planning Analytics Tips & Tricks:

Tips & Tricks for using TM1: Variance Analysis in IBM Cognos TM1

IBM Planning Analytics Tips & Tricks: Creating Dynamic, Attractive Views and Dashboards

Tips & Tricks for Using IBM Planning Analytics – Creating TM1/Planning Analytics Picklist Cubes

This is a guest blog post by Revelwood’s Nicole Frigo.

Did you know you can easily convert existing Perspective reports to IBM Planning Analytics for Excel (PAx)? Converting reports and upgrading action buttons require just a few simple steps when transitioning to PAx. Here’s what you need to do:

1. Open PAx and connect into your Planning Analytics environment

2. Once connected, select Options on the Planning Analytics ribbon and select Upgrade Action Buttons

3. You will have the option to create a backup and select a location for the backup. You will also be prompted to select a location for conversion logs.  It is important to note the once converted, action buttons will no longer work in Perspectives so it is highly recommended to both create a backup and to define a log directory.

Once the directories are selected, click OK. You will then be prompted to navigate to the file location. Select the file(s) to be upgraded and click Upgrade.

4. After converting, you will need to publish the new workbook so others have access. To do this, the workbook must be saved first as a Macro Enabled Workbook or .xlsm

5. Once saved, you can publish workbooks back up to the server by selecting Publish on the Planning Analytics ribbon

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!

Read more IBM Planning Analytics Tips & Tricks blog posts:

Tips & Tricks for using IBM Planning Analytics: Creating URLs for TM1 Websheets

Tips & Tricks for using TM1: Variance Analysis in IBM Cognos TM1

IBM Planning Analytics: Using the Full Power of MDX

This is a guest blog post by Revelwood’s Susan Musselman.

Did you know you can not only set up a landing page for IBM Cognos TM1 Web, but you can also set up custom landing pages for different groups of users? Here’s a quick step-by-step guide to do this.

The landing page for users is set up in the }ClientSettings cube:

In the above example, George Washington will see the Main Portal when he logs in and Al Capone will see Portal-2.  There are 3 easy steps to set this up:

Create elements in the Control Cube and populate control cube to define the path of the portal pages: the }ClientSettings cube:

Create a WebPortal attribute in the }Client dimension and populate for your users:

Create a rule in the }ClientSettings cube to determine which websheet to use for each }Client based on the attribute in the }ElementAttributes_}Clients cube:

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!

Read more blog posts with Tips & Tricks for IBM Planning Analytics:

Tips & Tricks for using IBM Planning Analytics: Creating URLs for TM1 Websheets

IBM Planning Analytics Tips & Tricks: Creating Dynamic, Attractive Views and Dashboards

Tips & Tricks for using IBM Planning Analytics: Creating TM1/Planning Analytics Picklist Cubes

Did you know IBM Planning Analytics Workspace (PAW) allows you to visualize your data using a variety of charts and graphs, including the ability to display your results via a pie chart?  In order to create a pie chart, you must follow one simple rule: ensure that you have only one “measure” element on either the row or the column.

You can create a pie chart in PAW with three easy steps:

1. Ensure that a dimension is defined as the measure by right clicking on the cube name within Server Explorer and then selecting the option for properties.

2. Create a view in PAW with either the rows or the columns displaying a single element from the measure dimension.

3. Set your visualization type to be a pie.

The resulting display will appear as a pie chart:

Once the pie chart is created, you can use PAW to change the appearance of the colors, the legend and other details.

IBM Planning Analytics 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!

This is a guest blog post by Revelwood’s Susan Musselman.

Did you know many companies find it beneficial to do a variance analysis to identify differences between the planned financial outcomes and the actual financial outcomes? Many clients prefer to store account balances in IBM Cognos TM1 using positive values unless the value is contra to the account. For example: even though revenue is a credit value in the General Ledger, it is shown as a positive number on a P&L report. Costs are also shown as positive and then subtracted from Revenue to get Gross Profit.

To get a variance amount, Budget is subtracted from Actual. This results in a correct variance for Revenue. For example: if budgeted revenue is $15,000 and actual Revenue is $20,000 then the variance would be a positive $5,000, which is correct because higher revenue is a good thing. However, if we use the same calculation for cost then the variance is backwards. For example:  if budgeted cost is $5,000 and actual cost is $8,000 then the difference calculates to positive $3,000. However, a higher cost is not good so the number should be displayed as a negative.

One solution would be to use a different formula for every row on every variance report that is created. However, this solution would require a lot of manually created rules that will slow down your overall TM1 application. A better solution would be to create a generic rule that can calculate the correct variance based on an attribute. To do this, first create an attribute in the Account dimension and populate the value with a 1 or -1 for every account. You can populate the attribute via rule to avoid future maintenance to the model.

Here is an example of some attributes values (please note that the attribute is populated for all levels of elements, including consolidations):

Next, create an element in your version dimension called “variance”. You can then create a rule for the new version to calculate the variance.

This gives the correct results:

Now the variance element can be used in any report!

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!

Read more blog posts on Tips & Tricks for IBM Planning Analytics and IBM Cognos TM1:

Tips & Tricks for IBM Planning Analytics: Creating URLs for TM1 Websheets

Tips & Tricks for IBM Planning Analytics: Creating TM1/Planning Analytics Picklist Cubes

IBM Planning Analytics: Using the Full Power of MDX