The following post is be based on the Sales and Marketing cube on Cognos 10.1.1, but it should be true for all versions of Cognos.
One of the most common questions I am asked is why I am so intensely against detail filters on OLAP queries. Official Cognos guides show examples of detail filters on OLAP, and it’s much faster to develop when using detail filters, why shouldn’t I allow them?
To begin detail filters prevent authors from answering complex business questions. What is the profit margin per year for every product that had a profit margin exceeding 60% in the years 2006 and 2007? A filter of [Profit Margin]>=0.6 doesn’t allow us to specify which years. Let’s make the business question even easier: What is the profit margin, per year, for each project where the profit margin for all the years exceeds 60%?
These are the results I expect to receive:
The rows node has the following expression:
filter([sales_and_marketing].[Products].[Products].[Product],[Profit Margin]>=0.6)
Native MDX for the report:
WITH MEMBER [Time]..[@MEMBER].[COG_OQP_USR_Summary(Year)] AS 'IIF([MEASURES].CURRENTMEMBER IS [MEASURES]..[Profit Margin], ([Time]..[@MEMBER].[COG_OQP_INT_m1], [MEASURES]..[Profit Margin]), AGGREGATE([Time]..[Year 2].MEMBERS))', SOLVE_ORDER = 4, MEMBER_CAPTION = 'Summary(Year)' MEMBER [Time]..[@MEMBER].[COG_OQP_INT_m1] AS 'AGGREGATE([Time]..[Year 2].MEMBERS, [MEASURES]..[Profit Margin])', SOLVE_ORDER = 4, MEMBER_CAPTION = '[Time]..[@MEMBER].[COG_OQP_INT_m1]' SELECT UNION([Time]..[Year 2].MEMBERS, {[Time]..[@MEMBER].[COG_OQP_USR_Summary(Year)]}, ALL) DIMENSION PROPERTIES PARENT_LEVEL, CHILDREN_CARDINALITY ON AXIS(0), FILTER([Products]..[Product 4].MEMBERS, [MEASURES]..[Profit Margin] >= 0.6) DIMENSION PROPERTIES PARENT_LEVEL, CHILDREN_CARDINALITY ON AXIS(1), {[MEASURES]..[Profit Margin]} DIMENSION PROPERTIES PARENT_LEVEL, CHILDREN_CARDINALITY ON AXIS(2) FROM [Sales and Marketing]
The summary column makes it a bit complex, but otherwise the MDX is easy to read.
Let’s remove the filter function and apply the [Profit Margin]>=0.6 as a detail filter
MDX:
WITH MEMBER [Time]..[@MEMBER].[COG_OQP_USR_Summary(Year)] AS 'IIF([MEASURES].CURRENTMEMBER IS [MEASURES]..[@MEMBER].[COG_OQP_INT_m1], ([Time]..[@MEMBER].[COG_OQP_INT_m3], [MEASURES]..[Profit Margin]), IIF([MEASURES].CURRENTMEMBER IS [MEASURES]..[@MEMBER].[COG_OQP_USR_Profit Margin], ([Time]..[@MEMBER].[COG_OQP_INT_m3], [MEASURES]..[Profit Margin]), IIF([MEASURES].CURRENTMEMBER IS [MEASURES]..[Profit Margin], ([Time]..[@MEMBER].[COG_OQP_INT_m3], [MEASURES]..[Profit Margin]), AGGREGATE([Time]..[Year 2].MEMBERS))))', SOLVE_ORDER = 4, MEMBER_CAPTION = 'Summary(Year)' MEMBER [Time]..[@MEMBER].[COG_OQP_INT_m3] AS 'AGGREGATE(FILTER([Time]..[Year 2].MEMBERS, NOT ISEMPTY([Product brand]..[@MEMBER].[COG_OQP_INT_m2])), [MEASURES]..[Profit Margin])', SOLVE_ORDER = 4, MEMBER_CAPTION = '[Time]..[@MEMBER].[COG_OQP_INT_m3]' MEMBER [Product brand]..[@MEMBER].[COG_OQP_INT_m2] AS 'IIF(([MEASURES]..[Profit Margin], [Product brand].[].DEFAULTMEMBER) >= 0.6, 1, NULL)', SOLVE_ORDER = 2, MEMBER_CAPTION = '' MEMBER [MEASURES]..[@MEMBER].[COG_OQP_USR_Profit Margin] AS '[MEASURES]..[@MEMBER].[COG_OQP_INT_m1]', SOLVE_ORDER = 2, MEMBER_CAPTION = 'Profit Margin' MEMBER [MEASURES]..[@MEMBER].[COG_OQP_INT_m1] AS 'IIF([MEASURES]..[Profit Margin] >= 0.6, [MEASURES]..[Profit Margin], NULL)', SOLVE_ORDER = 2, MEMBER_CAPTION = 'Profit Margin' SET [COG_OQP_INT_s1] AS 'CROSSJOIN([Time]..[Year 2].MEMBERS, [Products]..[Product 4].MEMBERS)' SELECT UNION(FILTER([Time]..[Year 2].MEMBERS, COUNT(FILTER([Products]..[Product 4].MEMBERS, ([MEASURES]..[Profit Margin], [Product brand].[].DEFAULTMEMBER) >= 0.6), INCLUDEEMPTY) > 0), HEAD({[Time]..[@MEMBER].[COG_OQP_USR_Summary(Year)]}, IIF(COUNT(FILTER([COG_OQP_INT_s1], ([MEASURES]..[Profit Margin], [Product brand].[].DEFAULTMEMBER) >= 0.6), INCLUDEEMPTY) > 0, 1, 0)), ALL) DIMENSION PROPERTIES PARENT_LEVEL, CHILDREN_CARDINALITY ON AXIS(0), FILTER([Products]..[Product 4].MEMBERS, COUNT(FILTER([Time]..[Year 2].MEMBERS, ([MEASURES]..[Profit Margin], [Product brand].[].DEFAULTMEMBER) >= 0.6), INCLUDEEMPTY) > 0) DIMENSION PROPERTIES PARENT_LEVEL, CHILDREN_CARDINALITY ON AXIS(1), {[MEASURES]..[@MEMBER].[COG_OQP_USR_Profit Margin]} DIMENSION PROPERTIES PARENT_LEVEL, CHILDREN_CARDINALITY ON AXIS(2) FROM [Sales and Marketing]
Suddenly we have 16 products instead of 13. The values are also artificially inflated for some of the products that are in the correct crosstab. What’s going on? The detail filter is being applied on intersections of the year and product. Any intersection that is below 60% is being suppressed, so the summary is showing the values for the unsuppressed years for each product. Any product that had any year exceeding 60% is displayed, and the summary column would indicate that the calculated total for all the years also exceeds 60%. Bad business decisions might be made because of this faulty data.
Have a report XML:
<report xmlns="http://developer.cognos.com/schemas/report/8.0/" useStyleVersion="10" expressionLocale="en-us" interactivePageBreakByFrame="true"> <modelPath>/content/folder[@name='Samples']/folder[@name='Cubes']/package[@name='Sales and Marketing (cube)']/model[@name='2010-06-24T18:14:00.360Z']</modelPath> <drillBehavior modelBasedDrillThru="true"/> <queries> <query name="Query1"> <source> <model/> </source> <selection><dataItem name="Product" aggregate="none" rollupAggregate="none"><expression>filter([sales_and_marketing].[Products].[Products].[Product],[Profit Margin]>=0.6)</expression></dataItem><dataItem name="Year" aggregate="none" rollupAggregate="none"><expression>[sales_and_marketing].[Time].[Time].[Year]</expression></dataItem><dataItem name="Profit Margin"><expression>[sales_and_marketing].[Measures].[Profit Margin]</expression><XMLAttributes><XMLAttribute name="RS_dataType" value="9" output="no"/></XMLAttributes></dataItem><dataItem solveOrder="1" name="Summary(Year)" aggregate="none" rollupAggregate="none"><expression>aggregate(currentMeasure within set [Year])</expression></dataItem></selection> </query> <query name="Query2"> <source> <model/> </source> <selection><dataItem name="Product" aggregate="none" rollupAggregate="none"><expression>[sales_and_marketing].[Products].[Products].[Product]</expression></dataItem><dataItem name="Year" aggregate="none" rollupAggregate="none"><expression>[sales_and_marketing].[Time].[Time].[Year]</expression></dataItem><dataItem name="Profit Margin"><expression>[sales_and_marketing].[Measures].[Profit Margin]</expression><XMLAttributes><XMLAttribute name="RS_dataType" value="9" output="no"/></XMLAttributes></dataItem><dataItem solveOrder="1" name="Summary(Year)" aggregate="none" rollupAggregate="none"><expression>aggregate(currentMeasure within set [Year])</expression></dataItem></selection> <detailFilters><detailFilter><filterExpression>[Profit Margin]>=0.6</filterExpression></detailFilter></detailFilters></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 refQuery="Query1" horizontalPagination="true" name="Crosstab1"> <crosstabCorner><style><defaultStyles><defaultStyle refStyle="xm"/></defaultStyles></style><contents><textItem><dataSource><dataItemLabel refDataItem="Profit Margin"/></dataSource></textItem></contents></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> <defaultStyles> <defaultStyle refStyle="xt"/> </defaultStyles> <CSS value="border-collapse:collapse"/> </style> <crosstabRows><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Product" edgeLocation="e1"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><reportExpression>RowNumber()</reportExpression></dataSource></textItem><textItem><dataSource><staticValue>. </staticValue></dataSource></textItem><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><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Summary(Year)" edgeLocation="e3"><style><defaultStyles><defaultStyle refStyle="ol"/></defaultStyles></style><contents><textItem><dataSource><dataItemLabel refDataItem="Summary(Year)"/></dataSource></textItem></contents><factCell><style><defaultStyles><defaultStyle refStyle="ov"/></defaultStyles></style></factCell></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabColumns><defaultMeasure refDataItem="Profit Margin"/><crosstabFactCell><contents><textItem><dataSource><cellValue/></dataSource></textItem></contents><style><defaultStyles><defaultStyle refStyle="mv"/></defaultStyles></style></crosstabFactCell></crosstab> <crosstab refQuery="Query2" horizontalPagination="true" name="Crosstab2"> <crosstabCorner><style><defaultStyles><defaultStyle refStyle="xm"/></defaultStyles></style><contents><textItem><dataSource><dataItemLabel refDataItem="Profit Margin"/></dataSource></textItem></contents></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> <defaultStyles> <defaultStyle refStyle="xt"/> </defaultStyles> <CSS value="border-collapse:collapse"/> </style> <crosstabRows><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Product" edgeLocation="e1"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><reportExpression>RowNumber()</reportExpression></dataSource></textItem><textItem><dataSource><staticValue>. </staticValue></dataSource></textItem><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><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Summary(Year)" edgeLocation="e3"><style><defaultStyles><defaultStyle refStyle="ol"/></defaultStyles></style><contents><textItem><dataSource><dataItemLabel refDataItem="Summary(Year)"/></dataSource></textItem></contents><factCell><style><defaultStyles><defaultStyle refStyle="ov"/></defaultStyles></style></factCell></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabColumns><defaultMeasure refDataItem="Profit Margin"/><crosstabFactCell><contents><textItem><dataSource><cellValue/></dataSource></textItem></contents><style><defaultStyles><defaultStyle refStyle="mv"/></defaultStyles></style></crosstabFactCell></crosstab></contents> </pageBody> </page> </reportPages> </layout> </layouts> <XMLAttributes><XMLAttribute name="RS_CreateExtendedDataItems" value="false" output="no"/><XMLAttribute name="listSeparator" value="," output="no"/><XMLAttribute name="RS_modelModificationTime" value="2010-06-24T18:14:00.390Z" output="no"/></XMLAttributes></report>
Thank you for the explanation 🙂