Retrieve only the lowest level selected from the tree prompt

[Shailaja0520]

hi,

This is the hierarchy

Company total
-department 1
--subdepartment 1
---team 1
---team 2
--subdepartment 2
--subdepartment 3
-department 2
-department 3

When user does multiselect in the tree prompt

suppose user has selected subdepartment 1  and subdepartment 3

subdepartment 3 to be retrieved.(which is the lowest level)

as subdepartment 1 is not the lowest level,it is in the higher level.

Can anyone tell the function or any way to achieve this.

Any help  will be greatly appretiated

[CognosPaul]

If I understand your hierarchy correctly it goes:
Company (root)
Department (Level 1)
Subdepartment (Level 2)
Team (Level 3)

Wouldn't Team then be considered the lowest level?

It seems like you're asking for the last member in the selected set.

You can do this with:
item(tail(set(#promptmany('Parameter','memberuniquename')#)),0)

If the user selects {[Subdepartment 1],[Subdepartment 2],[Subdepartment 3]} the tail will return the last member as a set, and the item will convert it back to a member.

If you're looking to return all of the Team members belonging to the selected departments, you can do something like descendants(set(#promptmany('Parameter','memberuniquename')#),[Cube].[Dimension].[Hierarchy].[Team])

[Shailaja0520]

hi,
Thanks for the Prompt reply.

But this is not what required.
tail returns the last member in a set..

We want only those level from a selected list {} which have no child.
  In this {[Subdepartment 1],[Subdepartment 2],[Subdepartment 3]}

  [Subdepartment 2],[Subdepartment 3]} should be the result.

[CognosPaul]

 ;D "Prompt reply" ;D


Okay, I think I understand. You need to evaluate each member in the selected set to ensure it's always the bottom most member.

Hint first: You'll need to use the filter, currentMember, count and children in the same expression.

I don't have access to a cube from where I am, so I'm doing this from memory. It may need some tweaking, but the logic is sound.

Code Select Expand

filter
(
   set(#promptmany('Parameter','memberuniquename')#)
   ,count(1 within set children(currentMember([cube].[dimension].[hierarchy])))=0
)


[Shailaja0520]

 Great Its Working.

Thank for the quick and exact reply that met the requirement.

[Ann]

I have a requirement, which could probably be an extension to this requirement.

I have a report which will display the efficiency % of department/Subdepartment/Team ( respective member selected by the user). In the example specified, my report report wil display efficiecy% for [Subdepartment 1],[Subdepartment 3].(assuming that the user have selected [Subdepartment 1] and [Subdepartment 3] from the prompt.

Till here I am good.

But now I have another table which stores the employees working in each department.
The table  contains the bottom most member and respective associates.

Something like this:
Team1:A
Team2:B
subdepartment 2:C
subdepartment 3:D
department 2:E
department 3:F

In my report I need to have a list to display the associates in each of the bottom most member among the selected memebers.


With respect to my current example my oputput should be two list reports:
1.
Subdepartment 1   efficiency%
Subdepartment 3   efficiency%


2. subdepartment 3:D


If I do a dril down from Subdepartment 1  from the first list and reach any of the bottom most member, say suppose team 1, then in the second list we should display the accociates of team1.

Any Idea how to implement this?

[CognosPaul]

In my experience drill downs tend to be very difficult to deal with. You can link separate objects for drill downs, but they must have the same data item visible.

Let me see if I understand:

You have a list that appears as


subDept   | Effic % |
-----------+---------+
subDept 1 |     95% |
subDept 2 |     87% |


You drill down to the bottom most level and it appears as:

Team   | Effic % |
--------+---------+
Team 1 |     95% |
Team 2 |     82% |
Team 3 |     23% |


Once the first list is on the the last level a second list appears which contains the emps associated with that team:

Team    | Emp   |
---------+-------+
Team 1  | Emp 1 |
Team 1  | Emp 2 |
Team 1  | Emp 3 |


Does that describe your requirements?

[Ann]

partially correct.

when you run the report first time, below wil be the two list reports displayed

subDept   | Effic % |
-----------+---------+
subDept 1 |     95% |
subDept 2 |     87% |


Team    | Emp   |
---------+-------+
subDept 2  | Emp D |

And then same as you said, when you drill down from subDept 1 to reach team1, team2 and team3,

again two two list as you depicted.

Hope that made some sense.

[CognosPaul]

I think I get it.

Load the attached XML.

The report has two lists, top one is Sales Territory and Revenue, and the bottom one is Sales Territory and staff. Staff is a calculated data item that is order(filter([great_outdoors_company].[Staff].[Staff].[Staff name],[Revenue]>0),[Revenue],DESC). This works well with drilldowns, however it will only show staff with a revenue of >0. If you also wish to show staff with a revenue of 0 then this will not work.

I've tried is using the Staff level from the Sales Territory hierarchy, unfortunately this disables the drilldown on the Sales Territory level. (This is 8.2, it might work in 8.4. Give it a try)

Code Select Expand

<report xmlns="http://developer.cognos.com/schemas/report/3.0/" expressionLocale="en-nz"><!--RS:8.2-->
    <modelPath>/content/package[@name='Great Outdoors Company']/model[@name='model']</modelPath>
    <queries>
        <query name="Query1">
            <source>
                <model/>
            </source>
            <selection><dataItem name="Sales territory" aggregate="none"><expression>[great_outdoors_company].[Sales Territory].[Sales Territory].[Sales territory]</expression></dataItem><dataItem name="Revenue"><expression>[great_outdoors_company].[Measures].[Revenue]</expression></dataItem><dataItem name="Staff name" aggregate="none"><expression>order(filter([great_outdoors_company].[Staff].[Staff].[Staff name],[Revenue]&gt;0),[Revenue],DESC)</expression></dataItem></selection>
        </query>
    </queries>
    <layouts>
        <layout>
            <reportPages>
                <page class="pg" name="Page1">
                    <pageBody class="pb">
                        <contents>
                           
                        <table class="tb"><tableRows><tableRow><tableCells><tableCell><contents><list class="ls" refQuery="Query1">
                                                       
                                                       
                                                       
                                                        <style>
                                                            <CSS value="border-collapse:collapse"/>
                                                        </style>
                                                        <listColumns><listColumn><listColumnTitle class="lt"><contents><textItem><dataSource><dataItemLabel refDataItem="Sales territory"/></dataSource></textItem></contents></listColumnTitle><listColumnBody class="lc"><contents><textItem><dataSource><dataItemValue refDataItem="Sales territory"/></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle class="lt"><contents><textItem><dataSource><dataItemLabel refDataItem="Revenue"/></dataSource></textItem></contents></listColumnTitle><listColumnBody class="lm"><contents><textItem><dataSource><dataItemValue refDataItem="Revenue"/></dataSource></textItem></contents></listColumnBody></listColumn></listColumns></list>
                                                </contents></tableCell></tableCells></tableRow><tableRow><tableCells><tableCell><contents><list class="ls" refQuery="Query1">
                                                       
                                                       
                                                       
                                                        <style>
                                                            <CSS value="border-collapse:collapse"/>
                                                        </style>
                                                        <listColumns><listColumn><listColumnTitle class="lt"><contents><textItem><dataSource><dataItemLabel refDataItem="Sales territory"/></dataSource></textItem></contents></listColumnTitle><listColumnBody class="lc"><contents><textItem><dataSource><dataItemValue refDataItem="Sales territory"/></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle class="lt"><contents><textItem><dataSource><dataItemLabel refDataItem="Staff name"/></dataSource></textItem></contents></listColumnTitle><listColumnBody class="lc"><contents><textItem><dataSource><dataItemValue refDataItem="Staff name"/></dataSource></textItem></contents></listColumnBody></listColumn></listColumns></list>
                                                </contents></tableCell></tableCells></tableRow></tableRows><style><CSS value="border-collapse:collapse"/></style></table></contents>
                    </pageBody>
                   
                   
                </page>
            </reportPages>
        </layout>
    </layouts>
<drillBehavior drillUpDown="true"><drillQueryBehavior refQuery="Query1"><drillDataItemBehavior refDataItem="Revenue" disableDrillUp="true" disableDrillDown="true"/></drillQueryBehavior></drillBehavior></report>