Product:
Planning Analytics 2.0.9
Planning Analytics for Excel
Microsoft Windows 2016 Server
Problem:
How sum a selection of accounts on a PAX custom report?
Suggested Solution:
Start Excel and login as developer in your TM1 instance (we use 24Retail in our example)
Find the cube that contain the dimensions you want to work with.
Right-click and select Open in viewer.
Move the dimensions to where you want them.
Right click on row and select Show attributes, to preview them in the view.
Mark the attribute you want to see and click on arrow and OK.
Click on the reports icon to insert it to your excel sheet.
Select custom reports, to have it in that format.
Select the column B and right click to Insert a new column, to show the attribute.
Enter the cube and dimension in cell A7
Enter the Attribute name in cell B8
Enter the DBRA function in cell B9, like this: =@DBRA($A$7,$A9,B$8)
Drag the formula, by grab in the corner of the cell, to the whole column to get values.
Now you have the attribute listed.
Enter the attribute value you want to sum in cell A27 and below.
Enter the SUMIF function in cell A27, like this: =SUMIF($B9:$B24,A27,C$9:C$24)
The SUMIF function have this parameters =SUMIF(area to search after value in, value to look for, area to sum rows where value is).
Copy the formula down to the other rows.
Now you can hide column B if you want it to look more automatic.
In TM1WEB you click on “recalculate” icon, to get new values.
There exist other ways to solve this, use of hierarchies can be one.
More information:
https://www.ibm.com/support/knowledgecenter/SSD29G_2.0.0/com.ibm.swg.ba.cognos.tm1_dg_dvlpr.2.0.0.doc/tm1_dg_dvlpr.pdf
https://exploringtm1.com/display-tm1-attributes-in-excel/
https://cubewise.com/blog/attributes-in-pa/
Some excel function does not work in TM1WEB, you must test them first.
https://code.cubewise.com/blog/mastering-hierarchies-in-ibm-tm1-and-planning-analytics