Slicers – Slicer member set is a set expression that returns members from a single hierarchy of a single dimension.

Since Cognos does not recommend using relational style filters against OLAP data sources, I decided to show you a few techniques for working with Slicers. You can think of Slicers as one of the dimensional techniques to filter your data (the filter () function being another).

A simple unfiltered report:

image1

What I want to do now is have this report only return data for the Order Methods Email and Web. Since this is an OLAP data source I am going to use a slicer. From query explorer, simply select both the email and web members and drag to the slicer pane.

image2

Report Studio automatically makes this a set. Since only data is returned for Email and Web orders now, the results returned look like (much lower revenue amounts):

image3

That was a little too easy so we can make it more dynamic with a prompt. Here is the syntax for a single select prompt:

[Great Outdoor Company].[Order Method].[Order Method].[Order Method1] -> ?om?

For a multi select prompt wrap the whole expression in the set() function:

Set([Great Outdoor Company].[Order Method].[Order Method].[Order Method1] -> ?om?)

image4

image5

If you are working with very large amounts of data, you might want all information returned except for a couple of specific selections. You can accomplish this using the except () function. The way it works is that it will return all of the first argument, except what is returned by the second argument.

image6

In this case all order methods would be returned, except for those chosen in the prompt.

 

Speak Your Mind

*