How to really filter out elements in a hierarchical subset?

[foogy]

Dear forum members,

do you now any approach to fulfill the following requirement? I'm almost convinced that the required functionality isn't even foreseen in TM1, but I'd like to ask you for confirmations or - which I hope for - any suggestion.

Given a dimension with a hierarchy, e.g.

Code: Select all

A
    a1
    a2
    a3
B
    b1
    b2

There is a TI process that creates a reduced subset from that dimension by skipping some elements that shall not be available for the purpose of the subset. The resulting subset may look like this:

Code: Select all

A
    a2
B
    b1
    b2

The subset shows the hierarchy drilled down to level 0. Everything is fine. But if the user now collapses element A and then expands it again, elements a1 and a3 are also shown again, which is not intended.

The behaviour is the same if the reduced subset is a dynamic subset with MDX statement. So "dynamic" only refers to the initial selection and drill down of the subset, right?

Any suggestions that will respect the reduced subset while still allowing for collapsing and expanding by the user?

Thank you very much.

[declanr]

The moment you expand or collapse an element within a subset, you have stopped looking at the subset.

If this is only for specific users have you considered using element security?

[whitej_d]

There's a couple of ways you may be able to get around this depending on why you need to suppress the elements.

If it's user related, you could use security to give 'none' access to the elements you don't want to show. This would allow users to drill up and down and only see the ones they have permission to see.

If you have a situation where the same user sometimes need to see different lists of elements in different reports, you would have to set up a flag element 'zShowline' or something similar. Set it to 1 for lines you want to show and use zero suppression to make sure the unwanted elements are not displayed when drilling up and down. If the hidden lines contain data, and can't be zero suppressed, you'd have to have an extra dimension in the cube to match the reporting context in a way that only the lines you want to see contain data.

[declanr]

It's also worth noting that we have probably skipped the most obvious solution of simply creating extra consolidations. You could do it in the same process you are currently using to create the subsets in question.

[tomok]

The OP called the original request a "subset" but I think they really meant an alternate hierarchy. If the proposed solution is done via a subset then the data shown will not be correct for any of the parent nodes. Just because a child of a parent does not show up in the list does not mean it's values aren't part of the parent's total. This is true whether you do it with a subset or via security. An alternate hierarchy is the only way to do this.

[declanr]

The OP called the original request a "subset" but I think they really meant an alternate hierarchy. If the proposed solution is done via a subset then the data shown will not be correct for any of the parent nodes. Just because a child of a parent does not show up in the list does not mean it's values aren't part of the parent's total. This is true whether you do it with a subset or via security. An alternate hierarchy is the only way to do this.

I would say it depends on the situation, I have previously set up consolidations of "Products" into "Brands" where all products needed to be included; in case actuals were sold against them but for the purpose of forecasting a number of the products were hidden within the active forms in order to stop users forecasting against them. So as long as no values are input against the hidden elements then everything is fine. However, having said that - I do agree that an alternate hierarchy is usually the best way to go.

[tomok]

I would say it depends on the situation, I have previously set up consolidations of "Products" into "Brands" where all products needed to be included; in case actuals were sold against them but for the purpose of forecasting a number of the products were hidden within the active forms in order to stop users forecasting against them. So as long as no values are input against the hidden elements then everything is fine.

Did you have actuals on that same form? I've rarely done a planning input template where the client didn't want a column showing last years numbers for comparison. If you did then the parent nodes for actuals didn't add up. You have to do some jiggery-pokery (in honor of you being Scottish ) to keep the form from being misleading.

[whitej_d]

I've had a situation where I had around 1,000 products in a hierarchy with around 1,000 customers. Customers stock a subset of the products, so need to be able to submit forecasts without having to trawl through the whole product hierarchy to find their products. Maintaining around 1,000 separate hierarchies in the product dimension would have been a pain in this case as the intermediate C level elements would all have to be unique for each hierarchy also. They also have to be able to see all their products whether zero or not so suppression on the forecast values was not an option.

In this case a flag of which products were live for each customer worked well as zero suppression could be applied to a view/active form which includes the live flag. If it's an active form then the flag can be hidden from view. Obviously this approach only works as customers don't forecast for products they don't stock so zero suppression can be applied.

[declanr]

Did you have actuals on that same form? I've rarely done a planning input template where the client didn't want a column showing last years numbers for comparison.

- This particular organisation didn't like the sales guys to be able to see last years numbers as when it was originally set up that way they realised the email to say they had started working on it on average arrived about 10 seconds before the one to say they had completed it....
The finance folk however used a different set of forms where they could see everything and anything they wanted.

jiggery-pokery (in honor of you being Scottish

- I may live in Scotland but I am an out and out Sassenach! http://www.urbandictionary.com/define.p ... =sassenach
We do however also say jiggery-pokery but more commonly in my experience... a number is "fudged".

[foogy]

Thank you for so much feedback and suggestions
Unfortunately, element security is not an option as the same elements are required in another context for the same user.

The moment you expand or collapse an element within a subset, you have stopped looking at the subset

Thanks declanr, this explanation really points out the reason for the behaviour I observed.

Concerning alternative hierarchies: I think I should give it a try in our DEV system as this seems to be the only possile way out. However, the TM1 model is a legacy one (created by some consultants without useful documentation), so I'm not sure whether creation of additional root elements by introducing alternative hierarchies will cause other TI processes to behave in an unexpected manner.

Another question in this context: creating alternative hierarchies would mean that most elements are the same in each hierarchy. So can they even have different children in each hierarchy? Hm, hard to explain what I'm concerned of ...

Regards,
foogy