If you are unable to create a new account, please email support@bspsoftware.com

 

Need to disable the drilldown after reaching the leaf node in DMR model in cogno

Started by babjiichowdary, 16 Dec 2010 11:59:06 AM

Previous topic - Next topic

babjiichowdary


Level 1   Level 2    Level 3   Level 4   Level 5
PYK           SCA           MSR       
PYK           SCA           MSS           VSN   
PYK           SCB           MST           PAR           LVS
PYK           SCC           MSY       
PYK           SCC           MSZ          TSK   

Hierarchy is defined for 5 levels (DMR model) in FM in cognos 8.4.1

I am creating a list report using report studio 8.4.1

In this scenario, I need to disable the drilldown functionality after reaching PYK > SCA > MSR (as MSR is the leaf node) but my report is showing drilldown for MSR and the next level (shows empty cell). On the empty cell, if I right-click its showing drill-down.

Is there any way which I can stop the drilldown functionally at the leaf node.

MFGF

Hi,

You have a more fundamental problem than this.  The hierarchy you describe is unbalanced (ie different branches have leaf members at different logical levels).  While this may not seem to be a problem when viewing the dimension in isolation, it definitely becomes a huge issue when considering the facts (measures) it links to.  If you have measures, they link to a specific logical level of the hierarchy (often the lowest level, but not always) and are then aggregated upwards, but here you do not have a fixed, fully-populated lowest level, so I can't see you getting any kind of sensible reporting or analysis of measures while your hierarchy is unbalanced in this way.

Normally, an appropriate solution is to balance the hierarchy - usually by copying the leaf members at higher levels to the unpopulated level(s) below. Tools such as Data Manager have inbuilt functionality to achieve this (in the data), but if you cannot change underlying database you will probably need to code calculations to achieve a similar result in FM.

Regards,

MF.
Meep!

babjiichowdary

Quote from: babjiichowdary on 16 Dec 2010 11:59:06 AM
Level 1   Level 2    Level 3   Level 4   Level 5
PYK           SCA           MSR       
PYK           SCA           MSS           VSN   
PYK           SCB           MST           PAR           LVS
PYK           SCC           MSY       
PYK           SCC           MSZ          TSK   

Hierarchy is defined for 5 levels (DMR model) in FM in cognos 8.4.1

I am creating a list report using report studio 8.4.1

In this scenario, I need to disable the drilldown functionality after reaching PYK > SCA > MSR (as MSR is the leaf node) but my report is showing drilldown for MSR and the next level (shows empty cell). On the empty cell, if I right-click its showing drill-down.

Is there any way which I can stop the drilldown functionally at the leaf node.


jive

Hi ,
As MF said, are when we face that can of problem and the information for a level are not available we always have a member telling 'N/A', that each level form top to bottom are populated It will give a schema like this

Level 1     Level 2      Level 3        Level 4      Level 5
PYK           SCA           MSR           'N/A'         'N/A'
PYK           SCA           MSS           VSN          'N/A'
PYK           SCB           MST           PAR           LVS
PYK           SCC           MSY          'N/A'           'N/A'
PYK           SCC           MSZ          TSK           'N/A'

In your request, you have to stop at a certain level for what  reason
1-the data empty
2-the lower level are not important.
If it's the first reason by having a balance dimension there will not be empty data.
If it's for the second , may be your dimension have to be remodel to stop at the level you need. In framework you can build a hierarchy and add some other based on the same information.
Build a complete one and just have to 'add hirerachy' in FM, kept the same information just lower the number of level. If you have a lot of report to do it will be easy to add multiple hierarchy instead of customizing each report manually.

thanks




babjiichowdary

As MF said, to balance the hierarchy - I have copied the leaf members at higher levels(unpopulated levels) as  below.

Level 1     Level 2      Level 3        Level 4      Level 5
PYK           SCA           MSR           MSR         MSR
PYK           SCA           MSS           VSN         VSN
PYK           SCB           MST           PAR          LVS
PYK           SCC           MSY           MSY         MSY
PYK           SCC           MSZ           TSK         TSK

At the report level, how can we put a condition like if both the levels are having same data, I need to disable the drilldown.

After copying the leaf Members, I tried a different approach like
defined the member set:
taken 3 data items - Reference Member, Reference Member for Children, Ancestors for Reference Member and in the drill behaviour, I have defined Reference Member -> Replace Item, Reference Member for Children -> Replace Expression, Ancestors for Reference Member -> Preserve

Ofcourse, this is also not helping me in achieving the solution.

Please suggest me in this regard.

Thanks in Advance,
Babji Chowdary