Solved : Capturing Parent Member

[LFC]

Hello All,
I have been trying to solve an MDX issue for capturing Parent member from the first level from any level below in the report with drillup/down enabled.   :'(

Issue:
I have attached the hierarcy picture with this post...my issue is that i am trying to capture the Parent from the 1st level irrespective of whichever level i am in and if my report is at the First level then Firstmember of the Level1 should be displayed

The Datasource is powerplay cube and the report is a List( drillup/down enabled)

So the report will look this at the top level

******************************************************
ColumnName       Location           Measure 1         Captured_Parent
                          United States     $100                UnitedStates
                          Canada     $100                         UnitedStates

*******************************************************
Drilling down on Canada should give me this resultset

******************************************************
ColumnName       Location           Measure 1         Captured_Parent
                          Canada-State1     $50                Canada
                          Canada-State2     $50                Canada

*******************************************************                 

and if Drill down on "Canada-State1" should give me this resultset
******************************************************
ColumnName       Location           Measure 1         Captured_Parent
                          Canada-State1-City1     $50                Canada
                          Canada-State1-City2     $50                Canada

*******************************************************             


My problem is that i need to capture the level dynamically ( which i did with "ordinal(level(currentMember(hierarcy(ColumnName)))) " and feed it to the Ancestor MDX but that fails as well. I have been banging my head for couple of days now...If anybody is able to throw some light on this request i would greatly appreciate it.   

[Francis aka khayman]

how about:

ancestor(currentMember(hierarcy(ColumnName));[Level1])

[CognosPaul]

Using a list in an OLAP source isn't the way to go. The best way to handle this would be to use a crosstab.

Build the crosstab with Location 1 and Location 2 in nested in the rows, and measure in the columns. With a drilldown enabled, clicking on Location 2 will automatically change Location 1 to the correct parent.

Next, drag a crosstab node to the right of the measure. Set the Define Contents in the intersection to Yes, unlock the report and drag Location 1 from your query into the intersection.

Now you can hide the Location1  from the rows (drag a crosstab node to the left of the measure, and hide it's intersections), and everything should look fine.

There are a few reasons why a list won't work well with a cube. The biggest one is that Cognos will need to do some (minor) local processing. The same way a relational needs to be pivoted for a crosstab, olap needs to be pivoted for a list.

[LFC]

Thank you khayman and CognosPaul for you replies.

@CognosPaul, i was not totally clear. My main intention to capture the Parent member was to pass it to a drill-thru target report which is looking for only "level 1" information and to add little complexity the Drill link is placed near the Header section of the Master report apart from the Crosstab or List object.

I tried dropping a singleton( dropping the location 1 QS) and setting the properties option to keep the drill in context to no avail. Also i have 5 levels of detail and i need the Level1 parent member info to Pass to the drill thru.

[CognosPaul]

Try dragging in the year level, and disable drill down and up for it.

[LFC]

Yup, Thank you so much CognosPaul.

after reading your first reply i started venturing down that path...works great now.
basically here's what i did,
- added the Level1 as a duplicate Query item into the query and disabled drill up/down for that particular Query item.
- dropped a singleton into the header of the report( where i wanted it).
- dropped the Duplicate Level1 into the singleton.
- went into the properties for that object and checked the check box for original Level 1 QS to be in Scope.
So every time i drill up/down on the cross-tab the singleton displays the parent which i am using to pass the drillthru values. ;D