The problem with this approach is that if you start to develop a number of reports, each requiring the same list of accounts, then your maintenance exercise grows proportionally to the number of reports. It is probably inevitable that you will need to change the list of accounts and every time you will need to alter X number of reports rather than one subset inside the TM1 instance. Using active forms and dynamic subsets looks like your preferred approach given what you've said so far.rmexcel wrote:I want to work in excel, could be formulas or VBA.
Further to my comment above, if you can see that subsets are useful and not junk, then you can direct your maintenance effort into managing the proliferation of subsets in your dimensions. Lots of people use a prefixing strategy, e.g. putting 'Sys' or 'z' or using the '}' character to hide the subset allow people to group up system or report subsets separately from those that are user-defined.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.
If you go down the active form/ dynamic subset route then the TM1FilterByLevel function may help: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?
Code: Select all
{TM1FILTERBYLEVEL( {TM1SUBSETALL( [YOUR_ACCOUNT_DIMENSION_NAME] )}, 0,2,3,4,5,6,7,8,9)} 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.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.
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.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?
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?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.
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.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)
I would love to understand how to use the TM1RptRow function; how much of a pain will it be to maintain?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.
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?rmackenzie wrote:If you go down the active form/ dynamic subset route then the TM1FilterByLevel function may help: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.Code: Select all
{TM1FILTERBYLEVEL( {TM1SUBSETALL( [YOUR_ACCOUNT_DIMENSION_NAME] )}, 0,2,3,4,5,6,7,8,9)}
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.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?
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.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.
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: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.
I will look into the recording feature, and make good use of it.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.
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!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 am getting help from him, but not as much as I would like to. He has his own duties too :)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.
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 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!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.
But I will keep this in mind for other reports...
I do agree that I don't "get it" yet. I will look into it again.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.
I'm kind of getting the impression that you are looking at TM1 as a data repository tool where you can easily dump out a portion of GL data into a worksheet and use that as the raw data from building reports? I've met a few people who have excitedly shown me their monster pivot tables they built off of a 30,000 row cube slice (e.g. your trial balance by cost centre for a month...) and then watched their confused expression when I told them that TM1 is the monster pivot table and maybe they've wasted their time somewhat.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...
Perhaps you are from a relational background? Oftentimes, people who have had a lot of reporting/ analytics experience in e.g. SQL server initially struggle with OLAP concepts... Perhaps you could try and see the TM1 cube as a big star schema with a few bells and whistles and see where it leads you?rmexcel wrote:I do agree that I don't "get it" yet. I will look into it again.