How to filter dynamically? Detail filter or Slicer?

[qvixote]

Hello!

Here I am again with a newbie question. I have a report using a Transformer cube as data source. I have a hierarchy on a dimension and I want to filter the report by one level of this hierarchy based on a prompt.

My intention is to have a multiselect value prompt with only one static option saying "Show only sentinel". If user doesn't select the option, no filter will be made. If user selects the option, then the report will filter by the hierarchy level.

The hierarchy is like: Sentinel type -> Institution. The "Sentinel type" level has only 2 members and when the user select the only-one option in the prompt, I need to show members of level "institution", but only the children of on member of level "Sentinel type".

How can I make it? using detail filters of slicer?

Thanks.

[qvixote]

Well, searching a little in Google I've realized that when using OLAP I must use slicer instead of detail filters. But that resolves just half my problem.

I've tested slicers and it work fine, but I don't know how to make an conditional slicer. When the user selects the only option in the value prompt, it have to apply the slicer, else no slicer must be applied.

Any idea of how can I do that?

[MFGF]

Well, searching a little in Google I've realized that when using OLAP I must use slicer instead of detail filters. But that resolves just half my problem.

I've tested slicers and it work fine, but I don't know how to make an conditional slicer. When the user selects the only option in the value prompt, it have to apply the slicer, else no slicer must be applied.

Any idea of how can I do that?

Hi,

If you're using a Transformer cube, your dimension will actually have three levels of members - there will be an extra member above the two Sentinel members to define the root of the hierarchy.

If you find the MUN of the root member (right-click on the member in the package tree and select Properties), you can use this as a default value for your prompt, and the report will behave as though you are not filtering on the dimension.

Cheers!

MF.

[qvixote]

Thanks, MFGF!

Let me see if I understand your idea. I need to put in my value prompt only one static choice pointing to the level I want the user can filter, and put as default option the root level of the hierarchy, so when the user do not select the one choice, the slicer will filter the entire hierarchy. That's it?

But now I don't know how to construct the slicer.

[MFGF]

Thanks, MFGF!

Let me see if I understand your idea. I need to put in my value prompt only one static choice pointing to the level I want the user can filter, and put as default option the root level of the hierarchy, so when the user do not select the one choice, the slicer will filter the entire hierarchy. That's it?

But now I don't know how to construct the slicer.

Hi,

Your slicer filter will be a slicer member set with the expression [your hierarchy] -> ?Your Parameter?

This will allow members from any level of the hierarchy to be used as a slicer.

You will have a Value Prompt that sets the same parameter as the expression, and the value prompt will be based on the Sentinel Type level. It will have a default selection value defined as the MUN of the top level member of the hierarchy.

One thing that is confusing here is that you refer to using a slicer filter, but you also refer to "show members of level "institution". Are you showing the members in your report or are you just filtering the numbers and not showing the members explicitly?

MF.

[qvixote]

Thanks again!

Answering your question: I show the institutions too, in another multiselect value prompt.

[MFGF]

Thanks again!

Answering your question: I show the institutions too, in another multiselect value prompt.

Hi,

So are they showing in your report object (crosstab, I assume) or are they just being used to drive the numbers in the crosstab cells?

MF.

[svb]

you need to create an optional slicer.

drop a slicer member set in slicer with the following expression definition:
For Multi-select:
set(#promptmany('promptXYZ','mun','[blue item under members in that hierarchy]')#)

single select:
#prompt('promptparameter','mun','[blue item under members in that hierarchy]')#

then u can proceed with creating your value prompt as usual and use existing parameter ('promptXYZ'). Select required level from the hierarchy.

OR

watch this video
https://www.youtube.com/watch?v=LjyKk5CcomM