TM1 Forum

Hi

I have a challenge where users wish to get a standard view of data from TM1 in a Excel spreadsheet. (With zero suppression and defined structure etc). However the view results needs to change based on a particular dimension subset which they will define in the excel spreadsheet (ie view results relate to cost centre xyz only). My original approach described below was to create a public view linking to a private subset (which would change based on the data they enter in the spreadsheet).

My approach to solving this was to use standard TM1 macro functions described below (requirement is to keep it simple and easy to maintain)

1: use the SUBDEFINE macro so the user can change a cell in excel and the Subdefine macro would simply define/redefine the dimension subset based this cell. Then refresh the view described below.
2: VUSLICE - create the initial view and base it on the required dimension subset above. This was the perfect format for the users requirements. In this view I thought to use the above subset that changes based on the users input.
The problem is I cannot create a public view (public view preferred as one view for admins to maintain) linking to a private subset.

My alternate options appear to be
1: Create and maintain multiple views based on the different users requirments (not preferred option due to maintenance requirements of multiple views)
2: Do the VUSLICE for all data then create an excel macro to delete the rows that are not releveant. (excel based solution may be hard to maintain)
3: Re-create a private view based on the private subset everytime the view is requested. (complex and not standard TM1 macro functionality)

Can anyone please help with ideas to solve this issue. The ideal solution would be the ability maintain View linked to private subsets that are defined as the user requests within excel (functionality and maintainability).

Regards

Jeremy

Jeremy

If you need the zero suppression then one option is to use an Active Form.

I am not clear as to whether the user specific subset is in the title area or on rows.

Either way, so long as it is not on columns then the Active Form can help you.

Personally if you don't have to work on TM1 Web then I would stick with Subdefine and Vuslice as they will probably give better performance. However Active Forms will work on both TM1 Web and normal Excel. They also don't need any macros.

Active Form perfomance is OK if you only have one dimension on rows, but can slow down considerably when you have nested dimensions on rows.

Another option to do the zero suppression if you don't need TM1 Web and can therefore use macros, is to use the Excel Auto-Filter function.

Regards

Paul Simon

Thanks Paul

Unfortunately we are still on TM1 9.1 so Active forms are not an option for us.

The requirement for the user defined subset is in the header section of the report (so this makes it a bit easier) At this stage it looks like I need to run for all data in a VUSLICE (probably retrieving 60,000 rows of info) then overwrite the header section in excel based on the user defined subset and manually filter / delete in excel the zero values to reduce it to a couple of hundred rows. I expect this solution will be slow but it will do the job.

Another option for me may be to allow the user to kick off a TI process that builds a private view based on the excel parameters. Then retrive this view via a VUSLICE into excel, only problem with this is it two distinct steps for them to remember to run.

Regards

Jeremy,

How often would you be reproducing this document? if you have the ablility, you could create a private subset and put it into each user's profile (within the Either way, you could create a private dynamic subset for yourself that would encompass the elements that you required. Here are the steps I would use for this (if you were limited by no public subsets).


1) You could use the SUBSIZ function within excel to get the number of elements (when you're logged in as yourself).

** use macro loop until number of elements populated is less than or equal to the SUBSIZ (or equivalent)

2) Within the loop, you could get each individual element using the ELCOMP function
2b) You could even nest the Elcomp within a DBRA to bring in the Alias of the element in question
2c) I usually turn this to static values if dealing with many elements (better performance)

go to next cell


here is some macro code to assist you:

Where:
Subsetsize = Value result of SUBSIZ function
Rowdelta = Difference of current row against the row prior to starting your table (to give the first row of table a value of 1)
(if my table started on row 13, then I would have =ROW()-12 )

Do While x - rowdelta <= subsetsize



ActiveCell.FormulaR1C1 = "=ELCOMP(<Dim Name>,<Sub Name>,Row delta)"
ActiveCell.Calculate
ActiveCell.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False



ActiveCell.Offset(1, 0).Select
x = x + 1
Loop









SUBSIZ
This is a TM1 worksheet function, valid only in worksheets.

SUBSIZ returns the number of elements in a dimension subset.

Syntax
SUBSIZ(dimension, subset)



ELCOMP

This is a TM1 rules function, valid in both TM1 rules and TurboIntegrator processes.
ELCOMP returns the name of a child of a consolidated element in a specified dimension. If the element argument is not a consolidated element, the function returns 0.

Syntax
ELCOMP(dimension, element, position)