This article is focused on one of ReportWORQ’s supported data sources, IBM Planning Analytics (PA, for short). Specifically, I’m covering things we’ve done in ReportWORQ to deal with the fact that PA users have two different Microsoft Excel add-ins available to them to author their Excel reports:
- IBM Cognos TM1 Perspectives: This is the older, legacy add-in for Planning Analytics which supported both Excel reporting and Planning Analytics application development & administration.
- IBM Planning Analytics for Excel (PAfE): PAfE is built on newer PA API’s and is designed to provide a more modern, consistent user experience regardless of PA hosting options. It includes new reporting modes, like Quick Reports, and has a very active roadmap with monthly releases.
Users who authored Excel reports using TM1 Perspectives often need to update their Excel reports when moving to PAfE because of slight differences in how some TM1 Excel formulas, as well as Active Forms (now called Dynamic Reports), are calculated. PAfE, for instance, doesn’t support directly referencing a subset name in a DBRW formula in order to compute a consolidated result. But there are a number of other subtle differences that users frequently need to contend with.
We’ve tried to help ReportWORQ customers cope with these differences in a couple ways that I wanted to highlight here:
-
A “PAfE Calculation Mode” setting that is configurable per-connection:
-
Log messages to highlight potential issues, as well as Excel report auditing capabilities in our Excel add-in to help find potential issues when migrating from Perspectives to PAfE:
PAfE Calculation Mode is turned ON 
for all new PA connections, by default. You can change this option to better-match where you are in your adoption of PAfE vs. Perspectives. Here is a list of some reporting scenarios where there are calculation behavior differences:
| Reporting Behavior | PAfE Mode OFF  |
PAfE Mode ON |
Notes |
|---|---|---|---|
| Invalid Element in DBR & DBRW | Blank result returned | #N/A error returned | |
| SUBNM/DIMNM errors | Blank result returned | #N/A error returned | This can also apply to other simple element reference functions like ELLEV, DTYPE, and more. |
| SUBNM reference to Element by Index | Private Subsets Prioritized | Public Subset Prioritized | If you have a Private and Public subset with the same name, Perspectives and PAfE make different choices when finding elements by index. |
| Repeating element labels in nested Active Forms/Dynamic Reports | Repeating element names are hidden | Repeating element names are displayed | For example, if you nest products within product group, do you want the product group repeated on each row. |
Some additional background on differences between PA’s Excel add-ins can be seen in this blog post. IBM has also documented some of the fundamental differences between the two add-ins here.
Please feel free to comment here or reach out to ReportWORQ Support (support@reportworq.com) if you see ways in which we can further improve our consistency in support of both of PA’s Excel add-ins!