I recently had a “Back to the Future” experience when I re-entered the consulting pool as a hands-on Planning Analytics (PA) developer after spending several years focusing on training and mentoring a new generation of TM1 experts. While my core skills in architectural design, rules and TurboIntegrator (TI) development remained fresh, I had limited in-the-field experience with newer features, and it has been fun getting back in the trenches and figuring things out.
In this blog post I will share some of my experiences getting more closely acquainted with developing Dynamic Reports – formerly known as TM1 Active Forms – in Planning Analytics for Excel (PAfE) for use as websheet widgets in Planning Analytics Workspace (PAW) Books.
This is written for:
- Someone whose PA reporting skills are moving beyond the “beginner” stage
- Myself 😊(the blog I wish had already been written six months ago)
Let’s Start with the Conclusion
Two important lessons were learned:
- Dynamic Reports can be powerful – and fragile. Make backup copies of your Excel book any time you have successfully completed a piece of development.
- Always test incrementally in both Excel and in PAW.
In the remainder of this blog post we will talk about two examples where Excel behavior is different from PAW behavior.
Please refer to the IBM PA Dynamic Reports documentation for a refresher on the basics.
The Base Example
For our example we will use a dynamic report on a Greenhouse Gas (GHG) Emissions cube showing miles flown and carbon emissions (in CO2 metric ton equivalents) by departure city and employee. This report was generated automatically from a Cube View in PAfE except for the column headings “Origin” and “Employee” which were typed in.
Notice that this report has two nested TM1RPTROW functions. Here they are together with the auto-generated MDX that selects all leaf elements in the respective dimensions:
|
Cell
|
Function
|
|
$B$23
|
=@TM1RPTROW($B$12,"Emissions:BT Originating Locations",,,,FALSE,B$11)
|
|
B$11
|
{TM1FILTERBYLEVEL(TM1SUBSETALL([BT Originating Locations].[BT Originating Locations]) , 0)}
|
|
$C$23
|
=@TM1RPTROW($B$12,"Emissions:Employees",,,"Name",FALSE,C$11)
|
|
C$11
|
{TM1FILTERBYLEVEL(TM1SUBSETALL([Employees].[Employees]) , 0)}
|
|
$B$12
|
=@TM1RPTVIEW( … )
|
Challenge 1: Nested DBRW formulas
For our first challenge we modify the Flight Miles and Carbon Emissions DBRW formulas to make them nested formulas. We want:
- Flight miles in 1000s (DBRW / 1000) and
- Carbon emissions in kg CO2 equivalents (DBRW * 1000).
With this change, every DBRW in the report is a sub-expression within another formula.
In Excel it works as expected:
But when published to the server and embedded in a PAW Book, no rows are generated:
The problem is solved by adding a column with a “clean” DBRW formula.
This now works in PAW:
The final step is to hide that extra column F in Excel and republish it to get the desired result.
This specific problem could have been solved in other ways, for example by setting the Excel format to display in thousands, or by creating a calculation in the PA (TM1) database. But if you have a situation that cannot be solved using a formatting string, or where you have a good reason not to write a rule or create a C: element, this is a useful general-purpose approach.
Challenge 2: Sorting on Values with Two TM1RPTROW formulas
For our next challenge we would like to sort all the records in descending order of Flight Miles. PAfE offers the handy TM1RPTFILTER function which we can point at the [Flight Miles] measure.
In Excel it works perfectly:
Unfortunately, PAW seems to ignore the TM1RPTFILTER:
Curiously, when there is only a single TM1RPTROW function in the report, the TM1RPTFILTER works as expected:
An alternative way to achieve a values-based sort on two row dimensions in PAW is to use a Cube View:
This is possible because PAW cube views are based on MDX, and MDX allows for “cross-join” type nesting (check out this excellent blog post on MDX views by George Tonkin) but Dynamic Reports were designed before MDX views were the standard in PA and they behave more like a two-column Excel sort where the second column is a secondary sort. Of course there is the ability to specify custom MDX for each of the TM1RPTROW functions, but they operate independently and there is no way (that I know of) to get the desired result without introducing significant complexity and effort.
I look forward to exploring whether the new Universal Reports are able to address this problem within published Excel websheets!
PAW Version: 2.0.85 (Local)
#IBMChampion #PlanningAnalytics #Excel #DynamicReports #ActiveForms
#IBMChampion