In the previous recipe, we saw that we can filter the whole report by a member or member set, using Slicer. This recipe will show you how to add a prompt into Slicer to make it dynamic.
Create a simple crosstab report with Organization Code 2 on rows and Current Year and Current Months on columns.
- Open the Query Explorer and explore the query being used by crosstab.
- From Insertable Objects pane, drag a new Set Expression under Slicer.
- Define the set as:
[GO Finance Fact].[Submission Dim].[Submission Dim].[Submission Currency En]->?SubmissionCcy?.
- Run the report to test it.
When you run the report, you will set the Cognos to automatically prompt you to select a Submission Currency. When you select one, the report runs and pulls data for selected Submission Currency.
Here, we are using Slicer for the same application as in the previous recipe. However, instead of hard-coding a particular member, we are making it prompt driven. The first part of the expression (before this pointer ->) defines the hierarchy. The second part is the parameter name which is dynamically replaced by Member Unique Name based on your selection.
You will notice that you can select only value for Submission Currency. This is because the expression expects only one member. However, it is possible to make it multi-select. For that, put the expression within the set () function.
Now when you run the report, you will be prompted for Submission Currency and you will be able to select more than one currency.
It is good practice to define your own prompt on the prompt page instead of letting Cognos generate the prompt at run-time. That way you can have control on the appearance, ordering, and performance of prompt page.
The biggest issue with Slicer is that you cannot make it Optional. It works like a mandatory filter. There is a workaround to this which involves using the Prompt Macro. However, that adds complexity to the code and sometimes I have seen other problems cropping up after using the Prompt Macro for this.
Hence, if you need to have optional filter, you might decide to go with the traditional detail filters where you can set the Usage property.