Suppressing tree prompts

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:

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: