How to create optional slicers

Check list of all posts

Optional slicers are requested in many business situations. 4 different cases are listed below.

1. Single select prompt without default

This case is simplest case; we can simply use #prompt with MUN with rootMember. As rootMember represents the “All” member of the hierarchy, use the “All” Member in place of this. In OLAP, partial tuples are resolved into complete tuple by the OLAP engine when the query is executed. For all dimensions not explicitly referenced in the partial tuple, the “All” member is used. In this case we have to slice by something, or report will generate error, so we are explicitly telling the cube to use the “All” member in place of a member or member set passed in the parameter. This results in the same resolved tuple that the OLAP engine would have generated on its own.

#prompt ('Region1','memberuniquename','rootMember ([Sales (analysis)].[Retailers].[Retailers])')#

2. Single select prompt with default

In this case, we do have a default value. In order to make consistent between prompt page and report, we need to define default values in prompt

The slicer itself in query is

#prompt ('Region2','memberuniquename','[Sales (analysis)].[Retailers].[Retailers].[Region]->[Retailers].[710]')#

3. Multi select prompt without default

The multi select slicer is similar as the one of the single select prompt, by changing from prompt to promptmany.

#promptmany ('Region3','memberuniquename','rootMember ([Sales (analysis)].[Retailers].[Retailers])','set(','',')')#

Please note that we should add the 4th parameter as display above. If we don’t define as above, we will experience errors when select some regions as below, somehow, Cognos interprets values with semicolon.

4. Multi select prompt with default

In this case, we do have a default values. In order to make consistent between prompt page and report, we need to define default values in prompt

The slicer itself in query is

#promptmany ('Region4','memberuniquename','set([Sales (analysis)].[Retailers].[Retailers].[Region]->[Retailers].[710],[Sales (analysis)].[Retailers].[Retailers].[Region]->[Retailers].[740])','set(','',')')#