Hi guys,
Has anyone successfully achieved Optimized master detail relationships in reports in a DQM Master-Details report ?
http://www.ibm.com/support/knowledgecenter/SSEP7J_10.2.2/com.ibm.swg.ba.cognos.dyn_query.10.2.2.doc/c_dqm_master_detail.html%23master-detail?lang=en (http://www.ibm.com/support/knowledgecenter/SSEP7J_10.2.2/com.ibm.swg.ba.cognos.dyn_query.10.2.2.doc/c_dqm_master_detail.html%23master-detail?lang=en)
I am attaching:
1) md.xml - report source
<report xmlns="http://developer.cognos.com/schemas/report/12.0/" useStyleVersion="10" expressionLocale="en-ca">
<modelPath>/content/folder[@name='Cognos Samples']/folder[@name='Samples_DQ']/folder[@name='Models']/package[@name='GO Data Warehouse (query)']/model[@name='model']</modelPath>
<drillBehavior modelBasedDrillThru="true"/>
<queries>
<query name="detail">
<source>
<model/>
</source>
<selection><dataItem name="Product line" aggregate="none" rollupAggregate="none"><expression>[Sales (query)].[Products].[Product line]</expression><XMLAttributes><XMLAttribute name="RS_dataType" value="3" output="no"/><XMLAttribute name="RS_dataUsage" value="attribute" output="no"/></XMLAttributes></dataItem><dataItem name="Product type" aggregate="none" rollupAggregate="none"><expression>[Sales (query)].[Products].[Product type]</expression><XMLAttributes><XMLAttribute name="RS_dataType" value="3" output="no"/><XMLAttribute name="RS_dataUsage" value="attribute" output="no"/></XMLAttributes></dataItem><dataItem name="Quantity" aggregate="total"><expression>[Sales (query)].[Sales fact].[Quantity]</expression><XMLAttributes><XMLAttribute name="RS_dataType" value="1" output="no"/><XMLAttribute name="RS_dataUsage" value="fact" output="no"/></XMLAttributes></dataItem><dataItem name="Year" aggregate="none" rollupAggregate="none"><expression>[Sales (query)].[Time].[Year]</expression><XMLAttributes><XMLAttribute name="RS_dataType" value="1" output="no"/><XMLAttribute name="RS_dataUsage" value="attribute" output="no"/></XMLAttributes></dataItem><dataItem name="Revenue" aggregate="total"><expression>[Sales (query)].[Sales fact].[Revenue]</expression><XMLAttributes><XMLAttribute name="RS_dataType" value="2" output="no"/><XMLAttribute name="RS_dataUsage" value="fact" output="no"/></XMLAttributes></dataItem><dataItemListSummary refDataItem="Quantity" aggregateMethod="aggregate" name="Summary(Quantity)"/><dataItemListSummary refDataItem="Revenue" aggregateMethod="aggregate" name="Summary(Revenue)"/></selection>
</query>
<query name="master">
<source>
<model/>
</source>
<selection><dataItem name="Year" aggregate="none" rollupAggregate="none"><expression>[Sales (query)].[Time].[Year]</expression><XMLAttributes><XMLAttribute name="RS_dataType" value="1" output="no"/><XMLAttribute name="RS_dataUsage" value="attribute" output="no"/></XMLAttributes></dataItem></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>
<list showColumnTitles="hide" name="List2" refQuery="master">
<listColumns>
<listColumn>
<listColumnTitle>
<contents/>
</listColumnTitle>
<listColumnBody>
<contents><list horizontalPagination="true" name="List1" refQuery="detail">
<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>
<defaultStyles>
<defaultStyle refStyle="ls"/>
</defaultStyles>
<CSS value="border-collapse:collapse"/>
</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><listColumnRowSpan refDataItem="Year"/></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><listColumnRowSpan refDataItem="Product type"/></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="lm"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="Quantity"/></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles></style><contents><textItem><dataSource><dataItemLabel refDataItem="Revenue"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lm"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="Revenue"/></dataSource></textItem></contents></listColumnBody></listColumn></listColumns><masterDetailLinks><masterDetailLink><masterContext><dataItemContext refDataItem="Year"/></masterContext><detailContext><dataItemContext refDataItem="Year"/></detailContext></masterDetailLink></masterDetailLinks><listOverallGroup><listFooter><listRows><listRow><rowCells><rowCell colSpan="1"><contents/><style><defaultStyles><defaultStyle refStyle="if"/></defaultStyles></style></rowCell><rowCell colSpan="2"><contents><textItem><dataSource><staticValue>Overall</staticValue></dataSource></textItem><textItem><dataSource><staticValue> - </staticValue></dataSource></textItem><textItem><dataSource><staticValue>Summary</staticValue></dataSource></textItem></contents><style><defaultStyles><defaultStyle refStyle="if"/></defaultStyles></style></rowCell><rowCell><contents><textItem><dataSource><dataItemValue refDataItem="Summary(Quantity)"/></dataSource></textItem></contents><style><defaultStyles><defaultStyle refStyle="is"/></defaultStyles></style></rowCell><rowCell><contents><textItem><dataSource><dataItemValue refDataItem="Summary(Revenue)"/></dataSource></textItem></contents><style><defaultStyles><defaultStyle refStyle="is"/></defaultStyles></style></rowCell></rowCells></listRow></listRows></listFooter></listOverallGroup><listGroups><listGroup refDataItem="Year"/><listGroup refDataItem="Product line"/><listGroup refDataItem="Product type"/></listGroups></list></contents>
<style>
<defaultStyles>
<defaultStyle refStyle="sb"/>
</defaultStyles>
</style>
</listColumnBody>
</listColumn>
</listColumns>
<style>
<CSS value="border-collapse:collapse; width:100%"/>
<defaultStyles>
<defaultStyle refStyle="sc"/>
</defaultStyles>
</style>
<listGroups><listGroup refDataItem="Year"><listHeader><listRows><listRow><rowCells><rowCell><contents><textItem><dataSource><dataItemLabel refDataItem="Year"/></dataSource></textItem><textItem><dataSource><staticValue>: </staticValue></dataSource></textItem><textItem><dataSource><dataItemValue refDataItem="Year"/></dataSource></textItem></contents><style><defaultStyles><defaultStyle refStyle="sh"/></defaultStyles></style></rowCell></rowCells></listRow></listRows></listHeader><XMLAttributes><XMLAttribute name="RS_SectionedEdge" value="columns" output="no"/></XMLAttributes><sortList><sortItem refDataItem="Year" sortOrder="ascending"/></sortList></listGroup></listGroups><sectionsFrame refFrame="List1" sectionedEdge="columns"/></list>
</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="2013-02-11T15:27:07.760Z" output="no"/></XMLAttributes><reportName>md-issue</reportName></report>
2) md-dqm-log.zip - DQM logs for this report
3) master-detail-package.zip - Cognos export package (the report in the folder Samples_DQ > Models > GO Data Warehouse (query) > Master-detail )
Can you please try this in your environments?
Or just confirm that you did make it work with your projects and have ONE Detail query only for a Master-detail report.
My environment:
Cognos 10.2.2
C8CRN_version=C8-AW64-ML-RTM-10.2.6100.235-0
DB: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0
Thanks,
Andrei