One of the bigger challenges that customers encounter when taking steps to make the switch to PAfE comes while testing existing Perspectives reports to ensure that they work properly in PAfE. The challenge is this: there are differences between Perspectives and PAfE when it comes to Planning Analytics/TM1 Excel formula evaluation. Your beautiful Income Statement Excel report from Perspectives may suddenly show a sea of #VALUE! errors the first time you open it in PAfE. Identifying the root cause of and resolving those differences can be frustrating and time-consuming!
This blog post provides a detailed summary of the formula differences that you may encounter while making the switch from Perspectives to PAfE and how to overcome them.
There are a few major areas in which PAfE and Perspectives differ across the supported IBM TM1 Worksheet Functions. The first question you might ask is: Why did anything have to change? Only IBM can say for sure, but I will say that, in general, I believe PAfE:
The 3 major problems and solutions due to differences in Perspectives vs. PAfE that we’ll cover here are the following:
The worst-kept secret of TM1 Perspectives is the ability to reference a named subset in a DBR formula to retrieve a value from a cube which is based on the consolidation of all elements in that subset, on-the-fly. From a reporting perspective this gives all users, so long as they can read data from that cube and subset’s elements, extra analytical power in Excel (perhaps at the expense of some reporting controls, one could argue).
Maybe, like me, you found this by accident and only use it on an ad-hoc basis to avoid having to add a one-off rollup to your dimension. But many customers rely on this “feature” in their production reports. In fact, this behavior for subset references in Perspectives extends to many more functions than you probably expected:
But this Perspectives “feature”, although it’s been around for nearly a couple decades, was never officially documented in TM1 Perspectives.
Probably not coincidentally, PAfE still does not (as of release 53) have any support for named subset references in place of elements, despite many requests to bring it back (including this one). In PAfE you will see this result when referencing a subset name instead of an element name: a #VALUE! Excel error.
The only solution available to PAfE, currently, for your Custom or Dynamic Reports is to change any named subset references to, instead, reference a rollup defined in your dimension’s default hierarchy.
Perspectives returns a special “*KEY_ERR” result for DBR/DBRW formulas when you reference practically anything that prevents it from finding a value in a cube, including Excel error values like #REF! and #VALUE!. *KEY_ERR is not an Excel-defined error value, but rather a text result that we’ve come to understand in Perspectives.
PAfE, by contrast, always uses the Excel #VALUE! error in situations where Perspectives would have returned *KEY_ERR. If your report is designed to look for and hide “*KEY_ERR” values through the use of IF or FIND Excel functions, it is highly likely that your report will not calculate as expected in PAfE.
Since #VALUE! is an Excel error function, you can make use of Excel functions like IFERROR or ISERROR functions to get equivalent behavior in PAfE.
TM1 Perspectives is incredibly forgiving if you pass an invalid object name or blank value to a TM1 function: you will typically get a blank or 0, depending on the return type of the TM1 function being used. It’s actually much easier to talk about the scenarios where you don’t get one of these blank/0 results in Perspectives:
PAfE will also return an #N/A for a lack of READ access and will also return #VALUE when an Excel error value is referenced, but without the VIEW exception in Perspectives. Any other invalid input to a TM1 function in PAfE, however, will return #VALUE! instead of a blank/0 result.
This is one of the most challenging differences to overcome in that it can require a lot of manual effort to trace through formulas and find the cause of #VALUE! errors in PAfE. Fortunately, QueBIT provides a solution as part of its FREE Print Reports for PAfE tool which can help audit your Excel reports for these and other problems, generating a detailed set of warnings and errors to help you quickly spot and resolve TM1 formula errors like these and others noted in this article.
Lastly, there are a few other differences that didn’t quite fit into the categories above and which are less common that we’ll note here:
Missed the webinar? Watch the recording to learn more about the new free Print Reports options, as well as a quick overview of ReportWORQ’s capabilities.