Problem with drill through different hierarchieis.

[liorw]

Hi,

I'm facing a problem when i try to pass a data by drill through between two reports.
The data i'm trying to pass is a date, that in the first report is connected to one Date Dimension, and on the other one it's connected to a different Date Dimension. The Dimensions are hierarchical.
I set a drill through from the first one by chosing to drill the date as "Data item" and tried already to pass the member uniqe name, the member caption and the business key. However, i get the same problem about the new hierarchical dimension is not recognizing the date.
How can i pass a the Data itself that the new report will find the Data in the new hierarchy ??

for example :
[Package name][Time_Dimension1][year]--> 2012
[Package name][Time_Dimension2][year]--> 2012

i want the second one the use the 2012 data to filter the new report

thanks for your help :)

[evyalh]

Hi there - since i have the same problem, I can attach report XML that can help understanding the problem.
The reports are based on the Go Sales (Analysis) sample package.

After I choose a year in the filter, and try to operate the drill through, I get the next error : QE-DEF-0498

      The member '[Sales (analysis)].[Time].[Time].[Year]->[Time].[2012]' does not belong to '[Dimensional view].[Time (close date)].[Time (close date)].[Year (close date)]'

The next XML is of the Father & Son (drill through destination):

Father XML:

Code Select Expand


<report xmlns="http://developer.cognos.com/schemas/report/9.0/" useStyleVersion="10" expressionLocale="en">
<modelPath>/content/folder[@name='Samples']/folder[@name='Models']/package[@name='GO Sales (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><list horizontalPagination="true" name="List1" refQuery="Query1">



<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="ls"/>
</defaultStyles>
</style>
<listColumns><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles></style><contents><textItem><dataSource><dataItemLabel refDataItem="Year"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lc"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="Year"/></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles></style><contents><textItem><dataSource><dataItemLabel refDataItem="Product line"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lc"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="Product line"/></dataSource><style><defaultStyles><defaultStyle refStyle="hy"/></defaultStyles></style><reportDrills><reportDrill name="Drill-Through Definition1"><drillLabel><dataSource><staticValue/></dataSource></drillLabel><drillTarget method="execute" outputFormat="HTML" showInNewWindow="true"><reportPath path="/content/folder[@name='Time_Test']/report[@name='Son']"><XMLAttributes><XMLAttribute name="ReportName" value="Son" output="no"/><XMLAttribute name="class" value="report" output="no"/></XMLAttributes></reportPath><drillLinks><drillLink><drillTargetContext><parameterContext parameter="year"/></drillTargetContext><drillSourceContext><dataItemContext refDataItem="Year"/></drillSourceContext></drillLink></drillLinks></drillTarget></reportDrill></reportDrills></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles></style><contents><textItem><dataSource><dataItemLabel refDataItem="Quantity"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lc"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="Quantity"/></dataSource></textItem></contents></listColumnBody></listColumn></listColumns><sortList><sortItem refDataItem="Year" sortOrder="ascending"/></sortList></list></contents>
</pageBody>
</page>
</reportPages>
</layout>
</layouts>
<XMLAttributes><XMLAttribute name="RS_CreateExtendedDataItems" value="true" output="no"/><XMLAttribute name="listSeparator" value="," output="no"/><XMLAttribute name="RS_modelModificationTime" value="2012-05-25T17:26:46.960Z" output="no"/></XMLAttributes><queries><query name="Query1">
<source>
<model/>
</source>
<selection><dataItemLevelSet name="Year"><dmLevel><LUN>[Sales (analysis)].[Time].[Time].[Year]</LUN><itemCaption>Year</itemCaption></dmLevel><dmDimension><DUN>[Sales (analysis)].[Time]</DUN><itemCaption>Time</itemCaption></dmDimension><dmHierarchy><HUN>[Sales (analysis)].[Time].[Time]</HUN><itemCaption>Time</itemCaption></dmHierarchy></dataItemLevelSet><dataItemLevelSet name="Product line"><dmLevel><LUN>[Sales (analysis)].[Products].[Products].[Product line]</LUN><itemCaption>Product line</itemCaption></dmLevel><dmDimension><DUN>[Sales (analysis)].[Products]</DUN><itemCaption>Products</itemCaption></dmDimension><dmHierarchy><HUN>[Sales (analysis)].[Products].[Products]</HUN><itemCaption>Products</itemCaption></dmHierarchy></dataItemLevelSet><dataItemMeasure name="Quantity"><dmMember><MUN>[Sales (analysis)].[Sales].[Quantity]</MUN><itemCaption>Quantity</itemCaption></dmMember><dmDimension><DUN>[Sales (analysis)].[Sales]</DUN><itemCaption>Sales</itemCaption></dmDimension><XMLAttributes><XMLAttribute name="RS_dataType" value="1" output="no"/></XMLAttributes></dataItemMeasure></selection>
<detailFilters><detailFilter><filterExpression>[Year] in ?year?</filterExpression></detailFilter></detailFilters></query></queries><drillBehavior drillUpDown="true" modelBasedDrillThru="true"><drillQueryBehavior refQuery="Query1"><drillDataItemBehavior refDataItem="Product line" disableDrillDown="true"/></drillQueryBehavior></drillBehavior><reportName>Father</reportName></report>


Son XML:

Code Select Expand


<report xmlns="http://developer.cognos.com/schemas/report/9.0/" useStyleVersion="10" expressionLocale="en">
<modelPath>/content/folder[@name='Samples']/folder[@name='Models']/package[@name='GO Sales (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><list horizontalPagination="true" name="List1" refQuery="Query1">



<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="ls"/>
</defaultStyles>
</style>
<listColumns><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles></style><contents><textItem><dataSource><dataItemLabel refDataItem="Year (close date)"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lc"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="Year (close date)"/></dataSource></textItem></contents><listColumnRowSpan refDataItem="Year (close date)"/></listColumnBody></listColumn><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles></style><contents><textItem><dataSource><dataItemLabel refDataItem="Product line"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lc"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="Product line"/></dataSource></textItem></contents><listColumnRowSpan refDataItem="Product line"/></listColumnBody></listColumn><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles></style><contents><textItem><dataSource><dataItemLabel refDataItem="Product type"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lc"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="Product type"/></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles></style><contents><textItem><dataSource><dataItemLabel refDataItem="Quantity"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lc"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="Quantity"/></dataSource></textItem></contents></listColumnBody></listColumn></listColumns><listGroups><listGroup refDataItem="Year (close date)"><sortList><sortItem refDataItem="Year (close date)" sortOrder="ascending"/></sortList></listGroup><listGroup refDataItem="Product line"/></listGroups></list></contents>
</pageBody>
</page>
</reportPages>
</layout>
</layouts>
<XMLAttributes><XMLAttribute name="RS_CreateExtendedDataItems" value="true" output="no"/><XMLAttribute name="listSeparator" value="," output="no"/><XMLAttribute name="RS_modelModificationTime" value="2012-05-25T17:26:46.960Z" output="no"/></XMLAttributes><queries><query name="Query1">
<source>
<model/>
</source>
<selection><dataItemLevelSet name="Year (close date)"><dmLevel><LUN>[Sales (analysis)].[Time (close date)].[Time (close date)].[Year (close date)]</LUN><itemCaption>Year (close date)</itemCaption></dmLevel><dmDimension><DUN>[Sales (analysis)].[Time (close date)]</DUN><itemCaption>Time (close date)</itemCaption></dmDimension><dmHierarchy><HUN>[Sales (analysis)].[Time (close date)].[Time (close date)]</HUN><itemCaption>Time (close date)</itemCaption></dmHierarchy></dataItemLevelSet><dataItemLevelSet name="Product line"><dmLevel><LUN>[Sales (analysis)].[Products].[Products].[Product line]</LUN><itemCaption>Product line</itemCaption></dmLevel><dmDimension><DUN>[Sales (analysis)].[Products]</DUN><itemCaption>Products</itemCaption></dmDimension><dmHierarchy><HUN>[Sales (analysis)].[Products].[Products]</HUN><itemCaption>Products</itemCaption></dmHierarchy></dataItemLevelSet><dataItemLevelSet name="Product type"><dmLevel><LUN>[Sales (analysis)].[Products].[Products].[Product type]</LUN><itemCaption>Product type</itemCaption></dmLevel><dmDimension><DUN>[Sales (analysis)].[Products]</DUN><itemCaption>Products</itemCaption></dmDimension><dmHierarchy><HUN>[Sales (analysis)].[Products].[Products]</HUN><itemCaption>Products</itemCaption></dmHierarchy></dataItemLevelSet><dataItemMeasure name="Quantity"><dmMember><MUN>[Sales (analysis)].[Sales].[Quantity]</MUN><itemCaption>Quantity</itemCaption></dmMember><dmDimension><DUN>[Sales (analysis)].[Sales]</DUN><itemCaption>Sales</itemCaption></dmDimension><XMLAttributes><XMLAttribute name="RS_dataType" value="1" output="no"/></XMLAttributes></dataItemMeasure></selection>
<detailFilters><detailFilter><filterExpression>[Year (close date)] in ?year?</filterExpression></detailFilter></detailFilters></query></queries><drillBehavior drillUpDown="true" modelBasedDrillThru="true"><drillQueryBehavior refQuery="Query1"><drillDataItemBehavior refDataItem="Product type" disableDrillDown="true"/></drillQueryBehavior></drillBehavior><reportName>Son</reportName></report>


Hope you guys can help us..
Thanks in advance,
Evya

[MFGF]

Hi,

The issue you are facing is as a result of the different time dimensions in source and target. Although the year caption is displayed when you include Year in your reports, under the covers the year members are identified by their MUN (Member Unique Name), and the MUNs of the two hierarchies are different.

The approach to take is to use the caption of the year member from your source report, and in your target report, build a prompt macro that constructs the relevant MUN including the passed year caption.

Cheers!

MF.