Breadcrumbs in cognos using DMR package

Breadcrumbs in cognos using  DMR package

Follow the below steps...

Version 10.2, Package: Go Dataware house(analysis) package

This example uses the Sales and Marketing (cube) sample Package.

Step 1.

Create a blank report and add a table with 2 rows and 1 column and add 2 crosstabs, one in the top row, one in the bottom. Make sure both crosstabs Query are based on Query1. This can be done by selecting the crosstab and looking at the Properties tab under Data.

Step 2.

Select the Query1 in Query Explorer. Under the Properties pane, set Define Member Set to Yes. This will add a tab at the bottom of the Query1 main window, the added tab is labeled “Member Sets,” this is found next to Projected Data Item, we will use this later.

Step 3.

Add the Sales->Time->Year(level), Sales->sales fact->Revenue and Sales->Products->membersfolder->Products (this is found in the Members folder in Products.Products) to Query1.

Step 4.

Add a new data item, the expression will be “ancestor([Products],0)”, call this ancestor 0, for to make it easier to find. Create 4 more new data items or make a copy of ancestor 0, each of these data items will have an expression similar to ancestor 0, each will be labelled with a number from 1 to 4, each having ancestor([Products],#) where # is it's number.

Step 5.

Create a new data item, this data items expression will be “children([Products])”. This can be named child.

Step 6.

Click the Member Sets tab. Double click the Products data item. Grab ancestor 0 and drag it onto Products in the main window, this will add ancestor 0 as part of Products, if done correctly you will see a minimize icon next to Products, which will hide ancestor 0 if clicked. Add all the other ancestors and the 1 child data item under ancestor 0, again, if the minimize icon is clicked, the child and all the ancestor items will be hidden. 

Step 7.

In the menu bar at the top, click Data, then Drill Behavior. Click Enable drill-up and drill down.

Step 8.

Go to the Advanced tab in the Drill Behavior menu. Click the Products data item, and set both drill-up and drill-down behavior to Replace Item. For the child and all ancestor data items, set drill-up and drill-down behavior to Preserve.

Step 9.

On the bottom crosstab, add the child data item to Rows on the crosstab, add year to the Columns, add revenue to the Measure.

Step 10.

On the top crosstab, add ancestor 4 to ancestor 0, in that order, to the Columns section.

Do not add anything to the measures or rows section.

to test copy the following code and open in RS (tools menu->open repor from clipboard)

<report xmlns="http://developer.cognos.com/schemas/report/10.0/" expressionLocale="en"><!--RSU-SPC-0093 The report specification was upgraded from &quot;http://developer.cognos.com/schemas/report/6.0/&quot; to &quot;http://developer.cognos.com/schemas/report/10.0/&quot; at 2014-9-18. 19:27:39-->

<modelPath>/content/folder[@name='Samples']/folder[@name='Models']/package[@name='GO Data Warehouse (analysis)']/model[@name='model']</modelPath>

<layouts>

<layout>

<reportPages>

<page name="Page1">

<style>

<defaultStyles>

<defaultStyle refStyle="pg"/>

</defaultStyles>

</style>

<pageBody>

<style>

<defaultStyles>

<defaultStyle refStyle="pb"/>

</defaultStyles>

</style>

<contents><table><style><defaultStyles><defaultStyle refStyle="tb"/></defaultStyles><CSS value="border-collapse:collapse;width:100%"/></style><tableRows><tableRow><tableCells><tableCell><contents><crosstab horizontalPagination="true" name="Crosstab2" refQuery="Query1">

<crosstabCorner>

<contents><textItem><dataSource><staticValue>Level: </staticValue></dataSource></textItem></contents>

<style>

<defaultStyles>

<defaultStyle refStyle="xm"/>

</defaultStyles>

</style>

</crosstabCorner>

<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><crosstabColumns><crosstabNode><crosstabNodeMembers><crosstabNodeMember edgeLocation="e1" refDataItem="ancestor4"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode><crosstabNode><crosstabNodeMembers><crosstabNodeMember edgeLocation="e2" refDataItem="ancestor3"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode><crosstabNode><crosstabNodeMembers><crosstabNodeMember edgeLocation="e3" refDataItem="ancestor2"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode><crosstabNode><crosstabNodeMembers><crosstabNodeMember edgeLocation="e4" refDataItem="ancestor1"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode><crosstabNode><crosstabNodeMembers><crosstabNodeMember edgeLocation="e5" refDataItem="ancestor0"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabColumns></crosstab></contents></tableCell></tableCells></tableRow><tableRow><tableCells><tableCell><contents><crosstab horizontalPagination="true" name="Crosstab1" refQuery="Query1">

<crosstabCorner>

<contents><textItem><dataSource><dataItemLabel refDataItem="Revenue"/></dataSource></textItem></contents>

<style>

<defaultStyles>

<defaultStyle refStyle="xm"/>

</defaultStyles>

</style>

</crosstabCorner>

<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 edgeLocation="e1" refDataItem="Children"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabRows><crosstabColumns><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Year" edgeLocation="e2"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabColumns><defaultMeasure refDataItem="Revenue"/></crosstab></contents></tableCell></tableCells></tableRow></tableRows></table></contents>

</pageBody>

</page>

</reportPages>

</layout>

</layouts>

<XMLAttributes><XMLAttribute name="RS_CreateExtendedDataItems" output="no" value="true"/><XMLAttribute name="listSeparator" output="no" value=","/><XMLAttribute name="RS_modelModificationTime" value="2013-01-08T15:38:02.967Z" output="no"/></XMLAttributes><queries><query name="Query1"><source><model/></source><selection><dataItemMember name="Products"><dmMember><MUN>[Sales].[Products].[Products].[Products]-&gt;[all]</MUN><itemCaption>Products</itemCaption></dmMember><dmDimension><DUN>[Sales].[Products]</DUN><itemCaption>Products</itemCaption></dmDimension><dmHierarchy><HUN>[Sales].[Products].[Products]</HUN><itemCaption>Products</itemCaption></dmHierarchy></dataItemMember><dataItem name="ancestor0"><expression>ancestor([Products],0)</expression></dataItem><dataItem name="ancestor1"><expression>ancestor([Products],1)</expression></dataItem><dataItem name="ancestor2"><expression>ancestor([Products],2)</expression></dataItem><dataItem name="ancestor3"><expression>ancestor([Products],3)</expression></dataItem><dataItem name="ancestor4"><expression>ancestor([Products],4)</expression></dataItem><dataItem name="Children"><expression>children([Products])</expression></dataItem><dataItemLevelSet name="Year"><dmLevel><LUN>[Sales].[Time].[Time].[Year]</LUN><itemCaption>Year</itemCaption></dmLevel><dmDimension><DUN>[Sales].[Time]</DUN><itemCaption>Time</itemCaption></dmDimension><dmHierarchy><HUN>[Sales].[Time].[Time]</HUN><itemCaption>Time</itemCaption></dmHierarchy></dataItemLevelSet><dataItemMeasure name="Revenue"><dmMember><MUN>[Sales].[Sales fact].[Revenue]</MUN><itemCaption>Revenue</itemCaption></dmMember><dmDimension><DUN>[Sales].[Sales fact]</DUN><itemCaption>Sales fact</itemCaption></dmDimension><XMLAttributes><XMLAttribute name="RS_dataType" value="2" output="no"/></XMLAttributes></dataItemMeasure></selection><memberSetStructure><memberSet name="Products" refDataItem="Products"><memberSets><memberSet name="ancestor0" refDataItem="ancestor0"/><memberSet name="ancestor1" refDataItem="ancestor1"/><memberSet name="ancestor2" refDataItem="ancestor2"/><memberSet name="ancestor3" refDataItem="ancestor3"/><memberSet name="ancestor4" refDataItem="ancestor4"/><memberSet name="Children" refDataItem="Children"/></memberSets></memberSet></memberSetStructure></query></queries><reportName>BreadCrumb</reportName><drillBehavior drillUpDown="true" modelBasedDrillThru="true"><drillQueryBehavior refQuery="Query1"><drillDataItemBehavior refDataItem="Products"><drillUpBehavior><drillSimpleType type="replaceItem"/></drillUpBehavior><drillDownBehavior><drillSimpleType type="replaceItem"/></drillDownBehavior></drillDataItemBehavior><drillDataItemBehavior refDataItem="ancestor0"><drillUpBehavior><drillSimpleType type="preserve"/></drillUpBehavior><drillDownBehavior><drillSimpleType type="preserve"/></drillDownBehavior></drillDataItemBehavior><drillDataItemBehavior refDataItem="ancestor1"><drillUpBehavior><drillSimpleType type="preserve"/></drillUpBehavior><drillDownBehavior><drillSimpleType type="preserve"/></drillDownBehavior></drillDataItemBehavior><drillDataItemBehavior refDataItem="ancestor2"><drillUpBehavior><drillSimpleType type="preserve"/></drillUpBehavior><drillDownBehavior><drillSimpleType type="preserve"/></drillDownBehavior></drillDataItemBehavior><drillDataItemBehavior refDataItem="ancestor3"><drillUpBehavior><drillSimpleType type="preserve"/></drillUpBehavior><drillDownBehavior><drillSimpleType type="preserve"/></drillDownBehavior></drillDataItemBehavior><drillDataItemBehavior refDataItem="ancestor4"><drillUpBehavior><drillSimpleType type="preserve"/></drillUpBehavior><drillDownBehavior><drillSimpleType type="preserve"/></drillDownBehavior></drillDataItemBehavior><drillDataItemBehavior refDataItem="Children"><drillUpBehavior><drillSimpleType type="preserve"/></drillUpBehavior><drillDownBehavior><drillSimpleType type="preserve"/></drillDownBehavior></drillDataItemBehavior></drillQueryBehavior></drillBehavior></report>