TM1 Forum

rmexcel wrote:I want to work in excel, could be formulas or VBA.

rmexcel wrote:The reason why I do not want a dynamic subset, is because I don't want to keep creating subsets for each report I create. This will just mess up the TM1 database with extra junk.

rmexcel wrote:I want all accounts, including the lowest level, but not the second to lowest level. Is there a way to make the level 0 accounts roll into the level 2 accounts?

{TM1FILTERBYLEVEL( {TM1SUBSETALL( [YOUR_ACCOUNT_DIMENSION_NAME] )}, 0,2,3,4,5,6,7,8,9)} 

rmexcel wrote:One major problem here, is that I don't have access to creating public subsets, I just do the reporting, and our "TM1 guy" does the subsets. This might change but we want as little as possible tampering, thus handing it over to one person.

rmexcel wrote:Which one should I use to get my subset into excel? Active form, or SUBNM formula?
From what I read, if new items are added to the rows or column dimensions (such as new products etc.), slices and active forms will not automatically get those items. You will have to either manually add them to your slice, or create a new slice.
If that is true, then am I not safer using the SUBNM function, getting all the elements in the subset?

rmexcel wrote:I think I got my answer. It doesn't look like I can create an active form from a dimension, only from a cube. If that is correct, I will have to go by SUBNM.

Thanks everyone for your help! This has taught me a lot!

asutcliffe wrote:I'm not entirely sure what you're proposing with SUBNM but guess you're talking about using the element indexes to try to get a full list of elements? I've not really tried this.

Personally, I'd still suggest you try with an active form. With a bit of practise you can build some pretty flexible reports.

asutcliffe wrote:You can create a simple active form from a cube that contains the dimension though. Aren't you ultimately going to want to pull some data from a cube into your report anyway?

(Sorry if I'm coming across as an active form zealot)

asutcliffe wrote:The TM1RptRow function (that drives the list of elements in an active form) accepts an MDX string which could, in theory, allow you to work around the inability to create public subsets. This would be a paint to maintain though.

As rmackenzie says though, well thought out subsets (be they dynamic or static) are really useful (I would say indispensable). I would think that your TM1 guy should know this and be happy to help.

rmackenzie wrote:If you go down the active form/ dynamic subset route then the TM1FilterByLevel function may help:

Code: Select all

{TM1FILTERBYLEVEL( {TM1SUBSETALL( [YOUR_ACCOUNT_DIMENSION_NAME] )}, 0,2,3,4,5,6,7,8,9)} 

This won't 'make the level 0 accounts roll into the level 2 accounts' but will have the effect of the level 1 accounts being excluded from the subset that is being displayed on the rows of the active form report. You'll have to play with the subset expression a bit to get what you need exactly.

rmexcel wrote:Would it be possible to show me how/what I need to change here to make it happen? Or guide me to a site that would explain it well enough so I should understand it without knowing too much about the MDX language?

rmexcel wrote:I think I got my answer. It doesn't look like I can create an active form from a dimension, only from a cube. If that is correct, I will have to go by SUBNM.

rmexcel wrote:I am designing my reports in a way that I only have to bring in my COA once per report. And the numbers are being pulled into the report based on index numbers. I'm not sure how do explain this without showing exactly what I do.
But I do like to hear other ways of doing things, and I do NOT have a lot of TM1 experience.

asutcliffe wrote:I would suggest experimenting with the record feature in the subset editor with the expression window visible. Displaying all elements and then filtering to include everything except level 1 should give you an MDX statement along the lines of what rmackenzie has suggested. Tweak this if necessary and keep an eye on the expression being recorded.

If you really want to get into the detail, the TM1 MDX Primer (Google it) is a great resource.

lotsaram wrote:I think this might have been mentioned already but if your aim is to build a report then you will be reporting on cube data therefore the requirement to have cube data to build an active form doesn't seem to be a big deal at all, in fact it seems reasonable and sensible.

lotsaram wrote:You may not have a lot of TM1 experience but I hope that your "TM1 guy" actually does. I'm a bit surprised that the "TM1 guy" isn't able to offer you some direct help with building this report or reports as it is a lot easier and more practical to get 1:1 assistance and training. If the people who look after TM1 think of the "TM1 system" encompassing only the server and not extending to reports and Excel which are "end user business" then this is a very narrow and short sighted view which places a lot of constraints on what can be achieved.

rmexcel wrote:

lotsaram wrote:I think this might have been mentioned already but if your aim is to build a report then you will be reporting on cube data therefore the requirement to have cube data to build an active form doesn't seem to be a big deal at all, in fact it seems reasonable and sensible.

I saw this mentioned, but it won't work for me as I am building many big reports on top of this. So here I would need an active form sheet, and then run VLOOKUP or SUMIF on top of it, which would make workbook calculation time WAY too long!
But I will keep this in mind for other reports...

lotsaram wrote:If this is how you are thinking about building your reports then it is clear that as of now you and your company definitely don't "get it" in terms of how to utilize TM1 to automate GL reporting and remove maintenance overhead. You may get to the end of the process in a month or 2 and then realize "if only I had taken that advice about subsets from rmackenzie", but by then it might be too late.

rmexcel wrote:I am building many big reports on top of this. So here I would need an active form sheet, and then run VLOOKUP or SUMIF on top of it, which would make workbook calculation time WAY too long!
But I will keep this in mind for other reports...

rmexcel wrote:I do agree that I don't "get it" yet. I will look into it again.