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