Our Blog

Creating Slicers in Report Studio

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:

A simple unfiltered report

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.

query explorer

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):

only data is returned for Email and Web orders

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?)

Order Method

Order Method = Mail, Special

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.

except () function

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

This entry was posted in Tips and tricks and tagged , , , , , , , . Bookmark the permalink.

Comments are closed.

PerformanceG2 Menu