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!
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.
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...
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:
set([great_outdoors_sales_en].[Products].[Products]->?Prod?)
Lowest childrent
descendants ([Selected],[great_outdoors_sales_en].[Products].[Products].[Product])
Place the second one on the rows of the crosstab
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... :)
In order to have multiselect parameter the following expression should be used (in OLAP data sources)
set([great_outdoors_sales_en].[Products].[Products]->?Prod?)
and not
set([great_outdoors_sales_en].[Products].[Products]in?Prod?)
Take a look in the following report specification
<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]->?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>
Ok, thanks... It works now eventhough the 3 different facts in the crosstab now returns strange values. Almost like my slicers have stopped working...
This subject could be closed (I had a keymismatch in FM). Thanks all for help!