In a previous blog post, we strongly encouraged customers who currently use Perspectives for their Planning Analytics & TM1 Excel reporting to consider making plans to switch to the newer Planning Analytics for Excel add-in (“PAfE” for short, previously called “PAx”). PAfE is designed to support the future of Planning Analytics Excel reporting, and offers new reporting options, like Quick Reports, which are not available in Perspectives.
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.
What Changed?
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:
- Is more aligned with the behavior of built-in Excel functions
- Is less ambiguous than Perspectives
- Honors TM1 security more consistently
The 3 major problems and solutions due to differences in Perspectives vs. PAfE that we’ll cover here are the following:
- Support for the undocumented Subset reference “feature”
- Which error values are returned when a TM1 function can’t be evaluated
- Handling of invalid/missing TM1 function arguments
1. The Magical Subset Reference Undocumented “Feature”
The Problem
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:
- Returns a result: DBR, DBRW, DIMIX, DTYPE, ELCOMP, ELCOMPN, ELISCOMP, ELLEV, ELSLEN, ELWEIGHT, TM1RPTELISCONSOLIDATED, TM1RPTELISEXPANDED, TM1RPTELLEV
- Will return a blank value: DBRA (Text attribute), DNEXT, ELPAR
- Will return 0: DBRA (Numeric attribute), ELPARN
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 Solution
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.
2. What’s a *KEY_ERR?
The Problem
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.
The Solution
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.
3. Perspectives TM1 Formulas: Anything Goes!
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:
- *KEY_ERR: Returned by DBR/DBRW formulas when TM1 can’t find your cube value for any reason other than security or a TM1 Rules error
- #N/A: Returned by DBR/DBRW formulas whenever:
- You don’t have at least read access to the cube value
- The cube value currently has an error in TM1 Rules evaluation
- #VALUE!: Returned by all TM1 functions when a reference to an object name (cube, dimension, element, etc.) points to an Excel error value. (Exception: VIEW functions in Perspectives return the cube parameter even when Excel errors are referenced in one or more element parameters)
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.
The Solution
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.
Other Perspectives vs. PAfE Differences of Note
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:
- DTYPE function: Perspectives returns more specific attribute type information (e.g., AA, AS, AN) when referencing an }ElementAttributes_<DimensionName> element. PAfE will return only “S” for Alias/Text and “N” for Numberic attribute elements.
- DBR vs. DBRW (Perspectives only): If you pass a blank element parameter to a DBR formula, you will receive a *KEY_ERR result. An identical DBRW formula, however, returns a blank result.
- Functions other than DBRA/DBR/DBRW (Perspectives only): DNEXT, DTYPE, ELCOMP, etc. don’t fully take TM1 security into account
Summary
Longtime users of TM1 Perspectives may run into problems migrating existing Excel reports to PAfE because of differences in the way the same TM1 formulas are calculated in both Excel add-ins. Perspectives is generally much looser in its interpretation of invalid TM1 function parameters than PAfE. Tools like QueBIT’s free “Print Reports” tool and resources like the Ask QueBIT knowledge base can help ease and plan for the transition while also helping to audit your Excel reports for errors and data usage.
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.
