We are facing with an problem. We need to use a subset in another subset. So far so good, no Problem using e.g. [Periods].[All_Base].
But this give us alle the elements of the given subset (Jan-Dec), but we want just one element (All Months), which contains the elements of the subset. We think it has to be such like an virtual consolidation, but we can't realize what we need.
Is there a solution for such a problem? (Periods is only used as example dimension, of course we have a aggregation there )
Well, you can use a subset reference within a DBRW formula and it will implicitly aggregate all the elements of the subset, so it is achievable in at least one front end. What interests me however is why you think you have to use subsets within subsets. Why not just have appropriate consolidations created in the dimension and use them? Can you describe your situation in a bit more detail please?
We have multiple front ends, therefore we want to solve it in database, not in front end.
I try to describe bit better.
We have a dimension of car models. It looks like
-Total
-- Model A
--- Model A Year 2017
--- Model A Year 2014
-- Model B
--- Model B Year 2017
--- Model B Year 2012
The Base Element have attributs if the Model is actual or historic (no longer produced). In on subset, we already need in front end, we filterd all current Models which are actual.
In second subset we use All Base Elements of dimension + additional we want to show a summary of all actual Models. So we hope there is a possibility to sum up all Actual Models from first subset to only one summary element.
Ganos Lal wrote:In second subset we use All Base Elements of dimension + additional we want to show a summary of all actual Models. So we hope there is a possibility to sum up all Actual Models from first subset to only one summary element.
Just create a consolidation of all the current models and have a TI update its definition whenever the relevant attribute values change. I see no good reason to want to do it the way you are describing.
To make it a fix structure we also thought about. But keeping the dimenson clean we would like to do it virtually in subset. If there's no other way than building a real new consolidation we will do it, but first we hoped we find another solution here.
Ganos Lal wrote:To make it a fix structure we also thought about. But keeping the dimenson clean we would like to do it virtually in subset. If there's no other way than building a real new consolidation we will do it, but first we hoped we find another solution here.
That's a lot of effort in the name of avoiding adding one (!) element into a dimension that already likely has tens if not hundreds of them. I can relate to the idea of 'keeping a dimension clean', but to me this is not a case where your Return On Investment would even be measurable.
Consolidating numbers in TM1 is achieved via hierarchies, full stop. There are other ways too, but all of them can be considered 'hacks', have their caveats and should only be used in very specific circumstances.
There are other ways too, but all of them can be considered 'hacks'
One way, if you are feeling adventurous, is to create a subset using MDX to filter the required attributes based on attribute or cube reference etc. Once you have a subset, you can reference the subset name and get the total based on the aggregation of the elements.
There are other ways too, but all of them can be considered 'hacks'
One way, if you are feeling adventurous, is to create a subset using MDX to filter the required attributes based on attribute or cube reference etc. Once you have a subset, you can reference the subset name and get the total based on the aggregation of the elements.
I don't think you read the OP's original question as this is their attempted approach. But this works only when referencing a subset as a pseudo-element in a DBR or manually copy/paste a subset name into the sunset editor. It DOESN'T work in an MDX subset as the set is returned not the pseudo-element.
I can only agree with Kamil that a rollup IS SIMPLY HOW SUMMATION IS DONE IN TM1. An elaborate workaround to avoid creating a rollup makes no sense. This is what rollups are for, use them!
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
gtonkin wrote:...
One way, if you are feeling adventurous, is to create a subset using MDX to filter the required attributes based on attribute or cube reference etc. Once you have a subset, you can reference the subset name and get the total based on the aggregation of the elements.
Hi, yes we like adventures
We already have the MDX base subset, that we want to aggregate into a new subset, but we don't know how. If we insert the MDX based subset we get all elements of it. Now's the question, how to aggregate them to just one element?
Regards jani
You will need to go with what Kamil and Lotsaram have been saying-I generally add an element called _System Rollups and then add in consolidations underneath this to keep them separate from other structures.
Ganos Lal wrote:We already have the MDX base subset, that we want to aggregate into a new subset, but we don't know how. If we insert the MDX based subset we get all elements of it. Now's the question, how to aggregate them to just one element?
Regards jani
Sali Jani, dini "Abkurzig" ist ein Umweg, dass nicht zum Ziel führt. In TM1 dialect of MDX to refer to a subset is to refer to a set of members. This makes sense. To sum members use a consolidation, it is what they are for.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
)
