how to make a drillthough on a virtual hierarchy element?

[mrdauduong]

Hi,

Iam trying to use new function Virtual Hierarchy on PAW

I have some virtual hierarchy with the element consolided diffirent.

I need to make a drillthrough but i realize that i can not drillthough on an element consolided of virtual hierarchy because this element consolided is not exist in architect ==> it return the default element "ND"

How i can make a drillthrough for 1 element consolided of virtual hierarchy?

Thanks a lot for help !

drill through.PNG (143.41 KiB) Viewed 1398 times

[Wim Gielis]

Are you sure that drillthroughs are supported in alternate hierarchies ?

[ascheevel]

It's been a while since I did this with alternate hierarchies, but you can parse the hierarchy selection, build the view manually in the prolog, and return that instead of the wizard-defined view. To be clear, the view you're returning is one without alternate hierarchies present. You'll need to parse the string parameter for whichever dim has the multi-hierarchy intersection in the drill-source. With two hierarchies from the same dim in the source view, the string parameter will look like this "^[base dim element selection]^[hierarchy name]:[hierarchy element selection]", three hierarchies would look like "^[base dim element selection]^[hierarchy1 name]:[hierarchy1 element selection]^[hierarchy2 name]:[hierarchy2 element selection]". If you only have 1 hierarchy for the dim in the view and it's an alternate hierarchy, the element name will be passed to the parameter like any normal drill view. You then would simply need to add all leaf children of that rollup in the alternate hierarchy to a subset or static list and attach that to the dynamic view you're creating in the prolog to be returned with ReturnViewHandle in the epilog.

[mrdauduong]

Are you sure that drillthroughs are supported in alternate hierarchies ?

this is the first time i use a alternative hierarchies, so, i dont know

It's been a while since I did this with alternate hierarchies, but you can parse the hierarchy selection, build the view manually in the prolog, and return that instead of the wizard-defined view. To be clear, the view you're returning is one without alternate hierarchies present. You'll need to parse the string parameter for whichever dim has the multi-hierarchy intersection in the drill-source. With two hierarchies from the same dim in the source view, the string parameter will look like this "^[base dim element selection]^[hierarchy name]:[hierarchy element selection]", three hierarchies would look like "^[base dim element selection]^[hierarchy1 name]:[hierarchy1 element selection]^[hierarchy2 name]:[hierarchy2 element selection]". If you only have 1 hierarchy for the dim in the view and it's an alternate hierarchy, the element name will be passed to the parameter like any normal drill view. You then would simply need to add all leaf children of that rollup in the alternate hierarchy to a subset or static list and attach that to the dynamic view you're creating in the prolog to be returned with ReturnViewHandle in the epilog.

Thanks for your reponse very clear Ascheevel, i will try it the next week and give you a feedback later !!!

[lotsaram]

You can only access alternate hierarchies through MDX views not traditional views. At this time drill-through only supports traditional views as the target and not MDX views.
You can vote for the RFE to support MDX views in drill-through here https://ibm-data-and-ai.ideas.ibm.com/ideas/PAOC-I-393

Note: this refers to having alternate hierarchies as the TARGET of a drill-through. Having alöternate hierarchy elements as part of the slice from which a drill through is defined (i.e. the SOURCE of a drill-through) shouldn't be a problem. This is just a matter of defining a rule in the drill cube.

[mrdauduong]

hi Ascheevel,

i dont really understand the idea. can you give a an exemple for this?

Thanks !

It's been a while since I did this with alternate hierarchies, but you can parse the hierarchy selection, build the view manually in the prolog, and return that instead of the wizard-defined view. To be clear, the view you're returning is one without alternate hierarchies present. You'll need to parse the string parameter for whichever dim has the multi-hierarchy intersection in the drill-source. With two hierarchies from the same dim in the source view, the string parameter will look like this "^[base dim element selection]^[hierarchy name]:[hierarchy element selection]", three hierarchies would look like "^[base dim element selection]^[hierarchy1 name]:[hierarchy1 element selection]^[hierarchy2 name]:[hierarchy2 element selection]". If you only have 1 hierarchy for the dim in the view and it's an alternate hierarchy, the element name will be passed to the parameter like any normal drill view. You then would simply need to add all leaf children of that rollup in the alternate hierarchy to a subset or static list and attach that to the dynamic view you're creating in the prolog to be returned with ReturnViewHandle in the epilog.

[ascheevel]

What element in the base hierarchy do you want the consolidation from the alternate hierarchy to map to? If the alternate hierarchy consolidation doesn't exist in the base dim, you'll need to specify something, otherwise it will use a default which you're seeing with the "ND" selection.

[mrdauduong]

What element in the base hierarchy do you want the consolidation from the alternate hierarchy to map to? If the alternate hierarchy consolidation doesn't exist in the base dim, you'll need to specify something, otherwise it will use a default which you're seeing with the "ND" selection.

hi,
i can not map with an element in the base hierarchy because the alternate hierarchy consolidation doesn't exist in the base dim, what i need to do to "specify something"?
thanks your reponse!

[ascheevel]

Exactly, it's well understood already that the consolidation in the alternate hierarchy doesn't exist in the base hierarchy, so the question is, "what do you want to do for the base hierarchy/dim?". Your drill-through view is going to need some element/subset selection for the base hierarchy, are you going to map to different selections based on what's selected in the alternate hierarchy or will you simply want to map to a standard default regardless of the drill source selection?

[mrdauduong]

Exactly, it's well understood already that the consolidation in the alternate hierarchy doesn't exist in the base hierarchy, so the question is, "what do you want to do for the base hierarchy/dim?". Your drill-through view is going to need some element/subset selection for the base hierarchy, are you going to map to different selections based on what's selected in the alternate hierarchy or will you simply want to map to a standard default regardless of the drill source selection?

I can not map to different selectios based on what's selected in the alternate hierarchy because all the alternate hierarchy is very diffrent than the base hierarchy.
I will wait for the solution in the next update. maybe TM1 will support drill through for alternaty hierarchy in the future.
For now, i will try to do another way.
Thanks for your reponse!

[ascheevel]

I will wait for the solution in the next update

I think this is the best course of action for you at this time, hopefully the wait isn't too long. Cheers.