Product:
Planning Analytics 2.0.9
Planning Analytics Workspace 54
Microsoft Windows 2016 server

Problem:
Sum up on a hierarchy?

Suggested solution:
Turn on hierarchy (virtual dimensions) by adding EnableNewHierarchyCreation=T to the TM1 applications tm1s.cfg file.
Ensure that you have free memory on the server before you turn this one.

Login to paw and open a new book.
Right click on the dimension, and select edit dimension. Click on attributes icon.
Right click on the attribute you want to create a hierarchy for.

The new hierarchy is shown as a dimension.

There are 3 first hierarchy created at first time, the one with same name is for backward compatibility, your new one, and levels. If you delete a member from a hierarchy – it is only removed from that virtual dimension. To delete a leaf member, you should delete it from the leaves hierarchy. Then data and all existing of that member is removed from all hierarchy’s.

Start PAX and connect to your TM1 application.
Right click on the cube that contain the data you want to see, and select Exploration.

Expand the dimension to see the new hierarchy. Drag the hierarchy to the rows area. Now you got the report summarized on the Plants.

Convert the report to a quick report.

Create a excel report the way you want and reference the result on the quick report sheet. Publish the report to TM1WEB.

Hierarchy can only be used in Explorations and Quick Reports. If you add members to the leaves, you have to recreate the dimension with a TI script to get the new members added.

More information:
https://code.cubewise.com/blog/mastering-hierarchies-in-ibm-tm1-and-planning-analytics
https://exploringtm1.com/creating-virtual-dimensions-in-tm1-planning-analytics/

Product:
Planning Analytics 2.0.9
Planning Analytics Workspace 54
Microsoft Windows 2016 server

Problem:
How sum up values from a group of accounts, in an Excel report?

Suggested solution:
Create a hidden dynamic report with the given subset in the TM1RPTROW and a sum underneath, then refer the sum.

Mark out the accounts that you want to sum with some text in the attribute, like here in planning samples, we set AccountCatagory to text “coffee”.

Go to Planning Analytics Workspace (PAW) and open a new book.
Expand the Dimensions, and for your “plan_chart_of_accounts” right click and select Edit dimension.
Click on icon for Member attribute mode in top right corner of the view.
Scroll to the column you want to edit, in out example AccountCatagory.
Enter the word “coffee” on the rows you want selected.
Updates are save direct to the dimension (tm1 database).

Go to Planning Analytics for Excel (PAX) and connect to your planning sample.
Expand the views on the right side and import a view that contain the columns you want.

On the view, right click and select dynamic report on new sheet.
Click on “Show Format Areas”.
In cell B11 insert the MDX formula:

‘{FILTER(TM1SUBSETALL([plan_chart_of_accounts].[plan_chart_of_accounts]) , (INSTR(1 , [plan_chart_of_accounts].[plan_chart_of_accounts].CURRENTMEMBER.PROPERTIES(“AccountCategory”) , “coffee” , 1) >= 1))}

In cell B21 update the TM1RPTROW to have a reference to the MDX formula last:

=@TM1RPTROW($B$12,”Planning Sample CAM:plan_chart_of_accounts”,,,”AccountName”,FALSE,B$11)

Above B$11 will give that the TM1RPTROW use the MDX formula to select accounts.
Then you need to sum up this values to a single cell.

Open the drop down of areas and mark the tm1rptdatarng line and press ctrl+c to copy the value.
Go to cell C19 and enter a SUM formula with INDEX:

=SUM(INDEX(TM1RPTDATARNG48625521,0,3))

Replace the TM1RPTDATARNG48625521 with your unique area name.
This should give that this always sum up the values in column 3 (ly actual) to this cell.

Now create a new sheet in the excel workbook.
Insert the dynamic report you want, and on the last line enter a reference to above cell.

=Sheet2!C19

This will update this cell with the value from the other sheet.

You need to refresh the book for the numbers to be updated.

You can hide the second sheet in the excel workbook, then the report is only one page.

There is better ways to solve this – check out the links below.

More information:
https://cubewise.com/blog/tm1-attributes-things-to-be-aware/
https://docs.microsoft.com/en-us/analysis-services/multidimensional-models/mdx/mdx-member-properties-intrinsic-member-properties?view=asallproducts-allversions&viewFallbackFrom=sql-server-ver15
https://www.tm1forum.com/viewtopic.php?t=13759
https://www.bihints.com/creating_dynamic_subsets_in_applix_tm1_with_mdx_a_primer
https://docs.microsoft.com/en-us/sql/mdx/iif-mdx?view=sql-server-ver15
https://docs.microsoft.com/en-us/sql/mdx/instr-mdx?view=sql-server-ver15
https://www.tm1forum.com/viewtopic.php?t=14731
https://www.contextures.com/xlFunctions01.html
https://exceljet.net/formula/sum-range-with-index

Product:
Planning Analytics 2.0.9
Planning Analytics Workspace 54
Microsoft Windows 2016 server

Problem:
How add a nice clock to the dashboard?

Suggested Solution:
Create a separate html page on your IIS server (or external) that all users can reach.

In that HTML page have this code only:
<!DOCTYPE html>
<html>
<body>

<iframe src=”https://free.timeanddate.com/clock/i7gqbnya/n239/fn6/fs16/fc9ff/tc000/ftb/bas2/bat1/bacfff/pa8/tt0/tw1/th1/ta1/tb4″ frameborder=”0″ width=”217″ height=”60″></iframe>

</body>
</html>

( you can create your own clock at site https://www.timeanddate.com/ )
Then copy the HTML code to a webpage that you save as default.html on your site. Any links need to be renamed to use https instead of http in the code, otherwise you get a blank page in paw.
(the default.html page will be the default page shown when a user browse to that url).

Inside PAW open the sheet you want to add a clock to.

Click on URL icon, and paste in the URL to your page.

https://time.tm1.dk/default.htm

Important that you enter HTTPS, as you can only use HTTPS pages inside PAW.
You can rotate the URL block and cut the empty space, then it looks nice when you leave edit mode in PAW.

More information:
https://blog.octanesolutions.com.au/adding-images-in-paw

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/

http://public.dhe.ibm.com/software/data/cognos/documentation/docs/en/tm1/9.0sp3/is_document_1425_tm1websupportedexcelfeaturelist.pdf

Some excel function does not work in TM1WEB, you must test them first.

Recreating TM1 Alternate Hierarchies

https://code.cubewise.com/blog/mastering-hierarchies-in-ibm-tm1-and-planning-analytics