SOLVED - Return lowest level in treeprompt

[Ziid]

Hi

I have a multi-select tree prompt with 3 levels as:

1. Area
2. Country
3. City

The user could for example select:

Europe, New York and Japan

The ONLY thing I would like to show in my cross tab is all the cities that correspond to the selected values. In the above case for example:

Stockholm, Oslo, Berlin, Franfurt, London, Paris, Aberdeen etc (for selected value Europe) + New York (for selected value New York) + Tokyo, Osaka, Hiroshima etc (for selected value Japan)

How can I accomplish to only show the lowest level from the hierarchy in my cross tab? (a accomplished the "drill-through-example" posted here but I am not that happy with the solution. Basically - the only thing that is interesting for my customer is the city, thats it.)

Very happy for help!

[Greg]

You could try something like this:

generate( ?location?, descendants( currentMember( [Location Hierarchy] ), [NS].[Location].[Location Hierarchy].[City] ) )

I haven't used the generate function much but from my understanding this should return the descendants at the city level for each member selected in the location prompt.

[Ziid]

Thank you for responding...

For unknown reason that makes the reportserver to "not respond"... I am quite new to Cognos which makes me a bit unsure wether I understood your suggestion correctly. Anyhow, this how a put it:

generate(?from?; descendants(currentMember([Dimension View].[Från Ort].[Från Ort]); [Dimension View].[Från Ort].[Från Ort].[Från Ort]))

The name of my hierarchy is "Från Ort" which could be a bit confusing because the last level in the hierarchy is also named "Från Ort". "Från Ort" is Destination in swedish.... ?from? is my parameter which is sent from the tree-prompt.

Well, thanks anyway...

[pricter]

Create one data item to bring the chosen and a second one to find the descendants

For example (based on Great Outdoors Sales cube)

Selected:

Code Select Expand

set([great_outdoors_sales_en].[Products].[Products]->?Prod?)

Lowest childrent

Code Select Expand

descendants ([Selected],[great_outdoors_sales_en].[Products].[Products].[Product])

Place the second one on the rows of the crosstab

[Ziid]

Thanks again...

The report now executes but generates no values (eventhough I know there is values). But can I do as you suggested eventhough I have a multiselect on my tree-prompt? I dont need something like:

set([Dimension View].[From Destination].[From Destination] in(?from?))

...for the first value that you named "Selected"?

Sorry, but this is kind of tricky the first times you do it... :)

[pricter]

In order to have multiselect parameter the following expression should be used (in OLAP data sources)

Code Select Expand

set([great_outdoors_sales_en].[Products].[Products]->?Prod?)

and not

Code Select Expand

set([great_outdoors_sales_en].[Products].[Products]in?Prod?)

Take a look in the following report specification

Code Select Expand

<report xmlns="http://developer.cognos.com/schemas/report/9.0/" useStyleVersion="10" expressionLocale="en">
<modelPath>/content/folder[@name='Samples']/folder[@name='Cubes']/package[@name='Great Outdoor Sales (cube)']/model[@name='2010-04-26T14:54:46.968Z']</modelPath>
<drillBehavior modelBasedDrillThru="true"/>
<queries>
<query name="Query1">
<source>
<model/>
</source>
<selection><dataItem name="Selected"><expression>set([great_outdoors_sales_en].[Products].[Products]-&gt;?Prod?)</expression></dataItem><dataItem name="LowestLevel"><expression>descendants ([Selected],[great_outdoors_sales_en].[Products].[Products].[Product])</expression></dataItem><dataItemMeasure name="Revenue"><dmMember><MUN>[great_outdoors_sales_en].[Measures].[Revenue]</MUN><itemCaption>Revenue</itemCaption></dmMember><dmDimension><DUN>[great_outdoors_sales_en].[Measures]</DUN><itemCaption>Measures</itemCaption></dmDimension><XMLAttributes><XMLAttribute name="RS_dataType" value="9" output="no"/></XMLAttributes></dataItemMeasure></selection>
</query>
</queries>
<layouts>
<layout>
<reportPages>
<page name="Page1"><style><defaultStyles><defaultStyle refStyle="pg"/></defaultStyles></style>
<pageBody><style><defaultStyles><defaultStyle refStyle="pb"/></defaultStyles></style>
<contents>

<crosstab horizontalPagination="true" name="Crosstab1" refQuery="Query1">
<crosstabCorner>
<contents/>
<style>
<defaultStyles>
<defaultStyle refStyle="xm"/>
</defaultStyles>
</style>
</crosstabCorner>


<noDataHandler>
<contents>
<block>
<contents>
<textItem>
<dataSource>
<staticValue>No Data Available</staticValue>
</dataSource>
<style>
<CSS value="padding:10px 18px;"/>
</style>
</textItem>
</contents>
</block>
</contents>
</noDataHandler>
<style>
<CSS value="border-collapse:collapse"/>
<defaultStyles>
<defaultStyle refStyle="xt"/>
</defaultStyles>
</style>
<crosstabFactCell><contents><textItem><dataSource><cellValue/></dataSource></textItem></contents><style><defaultStyles><defaultStyle refStyle="mv"/></defaultStyles></style></crosstabFactCell><crosstabRows><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="LowestLevel" edgeLocation="e1"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabRows><crosstabColumns><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Revenue" edgeLocation="e2"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabColumns></crosstab></contents>
</pageBody>
<pageHeader>
<contents>
<block><style><defaultStyles><defaultStyle refStyle="ta"/></defaultStyles></style>
<contents>
<textItem><style><defaultStyles><defaultStyle refStyle="tt"/></defaultStyles></style>
<dataSource>
<staticValue/>
</dataSource>
</textItem>
</contents>
</block>
</contents>
<style>
<defaultStyles>
<defaultStyle refStyle="ph"/>
</defaultStyles>
<CSS value="padding-bottom:10px"/>
</style>
</pageHeader>
<pageFooter>
<contents>
<table>
<tableRows>
<tableRow>
<tableCells>
<tableCell>
<contents>
<date>
<style>
<dataFormat>
<dateFormat/>
</dataFormat>
</style>
</date>
</contents>
<style>
<CSS value="vertical-align:top;text-align:left;width:25%"/>
</style>
</tableCell>
<tableCell>
<contents>
<pageNumber/>
</contents>
<style>
<CSS value="vertical-align:top;text-align:center;width:50%"/>
</style>
</tableCell>
<tableCell>
<contents>
<time>
<style>
<dataFormat>
<timeFormat/>
</dataFormat>
</style>
</time>
</contents>
<style>
<CSS value="vertical-align:top;text-align:right;width:25%"/>
</style>
</tableCell>
</tableCells>
</tableRow>
</tableRows>
<style>
<defaultStyles>
<defaultStyle refStyle="tb"/>
</defaultStyles>
<CSS value="border-collapse:collapse;width:100%"/>
</style>
</table>
</contents>
<style>
<defaultStyles>
<defaultStyle refStyle="pf"/>
</defaultStyles>
<CSS value="padding-top:10px"/>
</style>
</pageFooter>
</page>
</reportPages>
</layout>
</layouts>
<XMLAttributes><XMLAttribute name="RS_CreateExtendedDataItems" value="true" output="no"/><XMLAttribute name="listSeparator" value="," output="no"/><XMLAttribute name="RS_modelModificationTime" value="2010-04-26T14:54:47.017Z" output="no"/></XMLAttributes></report>

[Ziid]

Ok, thanks... It works now eventhough the 3 different facts in the crosstab now returns strange values. Almost like my slicers have stopped working...

[Ziid]

This subject could be closed (I had a keymismatch in FM). Thanks all for help!