This post uses the Sales and Marketing cube as a source. An example report XML is attached as usual.

A while back I wrote an article about why you should never use filters in OLAP. I mentioned there were only two cases where I permitted developers to use them. This is one of those cases.

When you use a tree prompt, Cognos will send a query to the database consisting of the use item, filters, slicers. Whenever you open a node (such as 2005) it will send another query containing children(2005), filters, and slicers. It will then append the results of that query into the tree prompt.

This behavior makes it difficult to control exactly what is returned. The general method of hiding members is to use the filter function. But since the filter is ignored to create the children function, we have to use something else. Slicers will also not work, as they effect the measures.

Instead, we can use detail filters. Cognos will apply the detail filters to the edge nodes, and since the edge node in a tree prompt consists solely of the hierarchy we want filtered, this will work nicely.

The first thing to remember is that Cognos will readily allow you to mix dimensional and relational functions. This is bad as it will result in local processing. So the filter if(substring(caption(currentMember([sales_and_marketing].[Time].[Time])),6,3)
=’Jan’) then (0) else (1) = 1 will result in:

MDX

WITH MEMBER [MEASURES]..[@MEMBER].[COG_OQP_USR_COG_OQP_INT_m1noFact] AS 'NULL', SOLVE_ORDER = 1, MEMBER_CAPTION = '' SELECT HIERARCHIZE(UNION({[Time].[].CURRENTMEMBER}, [Time]..[Year 2].MEMBERS, ALL)) DIMENSION PROPERTIES PARENT_LEVEL, CHILDREN_CARDINALITY ON AXIS(0) FROM [Sales and Marketing] WHERE ([MEASURES]..[@MEMBER].[COG_OQP_USR_COG_OQP_INT_m1noFact])

SQL

with
Years4_0_oqpTabStream4 as
    (select
           Years4_0_oqpTabStream."Data Item11/_memberUniqueName"  as  Data_Item11__memberUniqueName,
           Years4_0_oqpTabStream."Data Item11"  as  Data_Item11,
           Years4_0_oqpTabStream."Year/_memberUniqueName"  as  Year__memberUniqueName,
           Years4_0_oqpTabStream."Year"  as  Year4
     from
           TABLE(_ROWSET("Years4.0_oqpTabStream")) Years4_0_oqpTabStream
    )
select distinct
       Years4_0_oqpTabStream4.Data_Item11__memberUniqueName  as  Data_Item1__memberUniqueName,
       case when (substring(Years4_0_oqpTabStream4.Data_Item11 from 6 for 3) = 'Jan') then 0 else 1 end   as  Data_Item1,
       Years4_0_oqpTabStream4.Year__memberUniqueName  as  Year__memberUniqueName,
       Years4_0_oqpTabStream4.Year4  as  Year4
 from
       Years4_0_oqpTabStream4
 where
       (case when (substring(Years4_0_oqpTabStream4.Data_Item11 from 6 for 3) = 'Jan') then 0 else 1 end  = 1)

It’s dumping the entire hierarchy into memory and processing it in SQL. If the hierarchy is large (I’ve got some clients with hundreds of thousands of members in their hierarchies) this can be problematic.

Instead, we need to use dimensional friendly functions. Let’s start with hiding all members where the Profit Margin is less than 0.405.

For this, it’s a very simple detail filter of [Profit Margin]<0.405. The results will then work on every level:

In the preceding image, March of 2004 is missing from the tree prompt as the Profit Margin for that month was 40.7%. Q1, 2005 is also missing, as the Profit Margin for the entire quarter was 40.7% as well.

Next, we can suppress an entire level. Note that this will not allow you to skip a level; you can’t find the children of a member which isn’t rendered. For this, all you need to do is to filter on the ordinal or levelNumber of the member. roleValue(‘_levelNumber’,currentMember([sales_and_marketing].[Time].[Time])) < 3

And finally we let’s try suppressing Januaries.
if(roleValue(‘_levelNumber’,currentMember([sales_and_marketing].[Time].[Time])) = 3 and roleValue(‘_memberCaption’,currentMember([sales_and_marketing].[Time].[Time])) contains ‘Jan’) then (0) else (1) = 1. In this case, it checks that the current level is 3, and that the caption contains ‘Jan’ and returns 0. The MDX is valid:

WITH SET [COG_OQP_INT_s1] AS 'FILTER([Time]..[Year 2].MEMBERS, IIF(([Time].[].CURRENTMEMBER.PROPERTIES("LEVEL_NUMBER") = 3 AND CONTAINS([Time].[].CURRENTMEMBER.PROPERTIES("MEMBER_CAPTION"), "Jan")), 0, 1) = 1)' SELECT [COG_OQP_INT_s1] DIMENSION PROPERTIES PARENT_LEVEL, CHILDREN_CARDINALITY ON AXIS(0) FROM [Sales and Marketing]

And the result:

In each case, the tree prompt will behave exactly as expected, while suppressing unwanted members.

Report XML:

<report xmlns="http://developer.cognos.com/schemas/report/8.0/" useStyleVersion="10" expressionLocale="en-us">
				<modelPath>/content/folder[@name='Samples']/folder[@name='Cubes']/package[@name='Sales and Marketing (cube)']/model[@name='2008-07-25T15:28:38.072Z']</modelPath>
				<drillBehavior modelBasedDrillThru="true"/>
				<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"/></style><tableRows><tableRow><tableCells><tableCell><contents><table><style><defaultStyles><defaultStyle refStyle="tb"/></defaultStyles><CSS value="border-collapse:collapse"/></style><tableRows><tableRow><tableCells><tableCell><contents><textItem><dataSource><staticValue>No suppression</staticValue></dataSource></textItem></contents><style><CSS value="white-space:normal"/></style></tableCell><tableCell><contents><selectWithTree parameter="Parameter1" refQuery="Years"><selectWithTreeItem refDataItem="Year"/><style><CSS value="height:175px"/></style></selectWithTree></contents></tableCell><tableCell><contents/></tableCell></tableCells></tableRow><tableRow><tableCells><tableCell><contents><textItem><dataSource><staticValue>Only members with Profit Margin &lt; 0.405</staticValue></dataSource></textItem></contents></tableCell><tableCell><contents><selectWithTree parameter="Parameter2" refQuery="Years2"><selectWithTreeItem refDataItem="Year"/><style><CSS value="height:175px"/></style></selectWithTree></contents></tableCell><tableCell><contents/></tableCell></tableCells></tableRow><tableRow><tableCells><tableCell><contents><textItem><dataSource><staticValue>No months</staticValue></dataSource></textItem></contents></tableCell><tableCell><contents><selectWithTree refQuery="Years3" parameter="Parameter3"><selectWithTreeItem refDataItem="Year"/><style><CSS value="height:175px"/></style></selectWithTree></contents></tableCell><tableCell><contents/></tableCell></tableCells></tableRow><tableRow><tableCells><tableCell><contents><textItem><dataSource><staticValue>No Januarys</staticValue></dataSource></textItem></contents></tableCell><tableCell><contents><selectWithTree refQuery="Years4" parameter="Parameter4"><selectWithTreeItem refDataItem="Year"/><style><CSS value="height:175px"/></style></selectWithTree></contents></tableCell><tableCell><contents/></tableCell></tableCells></tableRow><tableRow><tableCells><tableCell><contents/></tableCell><tableCell><contents/></tableCell><tableCell><contents/></tableCell></tableCells></tableRow><tableRow><tableCells><tableCell><contents/></tableCell><tableCell><contents/></tableCell><tableCell><contents/></tableCell></tableCells></tableRow><tableRow><tableCells><tableCell><contents/></tableCell><tableCell><contents/></tableCell><tableCell><contents/></tableCell></tableCells></tableRow><tableRow><tableCells><tableCell><contents/></tableCell><tableCell><contents/></tableCell><tableCell><contents/></tableCell></tableCells></tableRow><tableRow><tableCells><tableCell><contents/></tableCell><tableCell><contents/></tableCell><tableCell><contents/></tableCell></tableCells></tableRow><tableRow><tableCells><tableCell><contents/></tableCell><tableCell><contents/></tableCell><tableCell><contents/></tableCell></tableCells></tableRow><tableRow><tableCells><tableCell><contents/></tableCell><tableCell><contents/></tableCell><tableCell><contents/></tableCell></tableCells></tableRow><tableRow><tableCells><tableCell><contents/></tableCell><tableCell><contents/></tableCell><tableCell><contents/></tableCell></tableCells></tableRow></tableRows></table></contents><style><CSS value="vertical-align:top"/></style></tableCell><tableCell><contents><crosstab horizontalPagination="true" name="Crosstab1" rowsPerPage="99999" refQuery="Data">
			<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><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><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Profit Margin" edgeLocation="e3"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabColumns><crosstabRows><crosstabNode><crosstabNestedNodes><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Year" edgeLocation="e6"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode><crosstabNode><crosstabNestedNodes><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Quarter" edgeLocation="e7"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Month" edgeLocation="e5"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabNestedNodes><crosstabNodeMembers><crosstabNodeMember refDataItem="Quarter" edgeLocation="e4"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabNestedNodes><crosstabNodeMembers><crosstabNodeMember refDataItem="Year" edgeLocation="e1"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabRows></crosstab></contents><style><CSS value="vertical-align:top"/></style></tableCell></tableCells></tableRow></tableRows></table></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="2008-07-25T15:28:38.133Z" output="no"/></XMLAttributes><queries><query name="Data"><source><model/></source><selection><dataItem name="Revenue"><expression>[sales_and_marketing].[Measures].[Revenue]</expression><XMLAttributes><XMLAttribute name="RS_dataType" value="9" output="no"/></XMLAttributes></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 name="Year" aggregate="none" rollupAggregate="none"><expression>[sales_and_marketing].[Time].[Time].[Year]</expression></dataItem><dataItem name="Quarter" aggregate="none" rollupAggregate="none"><expression>[sales_and_marketing].[Time].[Time].[Quarter]</expression></dataItem><dataItem name="Month" aggregate="none" rollupAggregate="none"><expression>[sales_and_marketing].[Time].[Time].[Month]</expression></dataItem></selection></query><query name="Years"><source><model/></source><selection><dataItem name="Year" aggregate="none"><expression>[sales_and_marketing].[Time].[Time].[Year]</expression></dataItem></selection></query><query name="Years2"><source><model/></source><selection><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></selection><detailFilters><detailFilter><filterExpression>[Profit Margin]&lt;0.405</filterExpression></detailFilter></detailFilters></query><query name="Years3"><source><model/></source><selection><dataItem name="Year" aggregate="none" rollupAggregate="none"><expression>[sales_and_marketing].[Time].[Time].[Year]</expression></dataItem><dataItem name="Level Number"><expression>roleValue('_levelNumber',currentMember([sales_and_marketing].[Time].[Time]))</expression><XMLAttributes><XMLAttribute name="RS_dataType" value="9" output="no"/></XMLAttributes></dataItem></selection><detailFilters><detailFilter><filterExpression> [Level Number] &lt; 3</filterExpression></detailFilter></detailFilters></query><query name="Years4"><source><model/></source><selection><dataItem name="Data Item1"><expression>if(roleValue('_levelNumber',currentMember([sales_and_marketing].[Time].[Time])) = 3 and roleValue('_memberCaption',currentMember([sales_and_marketing].[Time].[Time])) contains 'Jan') then (0) else (1)</expression></dataItem><dataItem name="Year" aggregate="none" rollupAggregate="none"><expression>[sales_and_marketing].[Time].[Time].[Year]</expression></dataItem></selection><detailFilters><detailFilter><filterExpression>[Data Item1] = 1</filterExpression></detailFilter></detailFilters></query></queries><reportName>suppressing tree prompts</reportName></report>