[RESOLVED ]How can I filter multiple elements of a dimension

[erwink]

Hi there

I've a hierarchy like Segment>Portfolio>Project>Location>Budget with budget the lowest element

I would like to have a prompt filtering on Location. The problem is that the same location exist several times
The prompt dropdown has  x times the same Location, and the user needs to select all of them to have the correct result.

Is there a way where the prompt would display each location only once and filtering on all elements in the dimension
Or to have a static prompt and transform this to be a member

see attachment

[MFGF]

Hi there

I've a hierarchy like Segment>Portfolio>Project>Location>Budget with budget the lowest element

I would like to have a prompt filtering on Location. The problem is that the same location exist several times
The prompt dropdown has  x times the same Location, and the user needs to select all of them to have the correct result.

Is there a way where the prompt would display each location only once and filtering on all elements in the dimension
Or to have a static prompt and transform this to be a member

see attachment

It looks like the issue here is that you have several location members with the same caption. As far as your model is concerned they are not the same location though - each is a different member with a different Member Unique Name (MUN). You can see this if you right-click on a couple of the members in turn in the package tree and choose "Properties".

How are you referencing the prompt parameter in your report? Are you using an expression such as [Your location level] -> ?Your Parameter?

If so, you could try changing the expression to be

filter([Your location level], caption([Your location level]) = ?Your Parameter?)

You'd then need to change the prompt to use a calculated item with the expression caption([Your location level])

The big drawback of this is that it will give you much worse performance than the original approach. It may or may not be an acceptable trade-off.

Cheers!

MF.

[erwink]

Hi

Thank you for your feedback. Sounds good but dumb as I am , I was not able to have it running

First I created a filter using BusinessView element like as follow

[Plan-Actual_MS].[VCRPortfolio].[VCRPortfolioH].[Location] IN ?p_loc?

That worked fine.

Then I tried your approach
filter([Plan-Actual_MS].[VCRPortfolio].[VCRPortfolioH].[Location],caption([Plan-Actual_MS].[VCRPortfolio].[VCRPortfolioH].[Location]) IN ?p_loc?)

Wrong expression type: filter(members([Plan-Actual_MS].[VCRPortfolio].[VCRPortfolioH].[Location]),rolevalue('_memberCaption',[Plan-Actual_MS].[VCRPortfolio].[VCRPortfolioH].[Location]) in (?p_loc?)).RQP-DEF-0149 The query specification is incorrect.RQP-DEF-0457 Referenced Query 'Query1' is not defined or its query items contain unresolved references.

Any Idea?

[MFGF]

Hi

Thank you for your feedback. Sounds good but dumb as I am , I was not able to have it running

First I created a filter using BusinessView element like as follow

[Plan-Actual_MS].[VCRPortfolio].[VCRPortfolioH].[Location] IN ?p_loc?

That worked fine.

Then I tried your approach
filter([Plan-Actual_MS].[VCRPortfolio].[VCRPortfolioH].[Location],caption([Plan-Actual_MS].[VCRPortfolio].[VCRPortfolioH].[Location]) IN ?p_loc?)

Wrong expression type: filter(members([Plan-Actual_MS].[VCRPortfolio].[VCRPortfolioH].[Location]),rolevalue('_memberCaption',[Plan-Actual_MS].[VCRPortfolio].[VCRPortfolioH].[Location]) in (?p_loc?)).RQP-DEF-0149 The query specification is incorrect.RQP-DEF-0457 Referenced Query 'Query1' is not defined or its query items contain unresolved references.

Any Idea?

Hi,

A filter? As in a detail filter? Nooooooo!!!! Don't go down that route when using a dimensional package!

You need to use dimensional expressions to get accurate, predictable results with a dimensional source. Are you displaying the location members in your crosstab, or do you want the numbers to be in context of locations without them being displayed?

If the former, then replace the location level in your crosstab with the filter() dimensional expression from my previous post.

If the latter, go to your query and drag a slicer member set into the slicer filter area. Populate the expression using the filter() dimensional expression from my previous post.

Oh, and delete your detail filter. If you have any other detail filters you need to replace these too. For an example of why, take a look at FAQ #4 in the FAQs thread below:

http://www.cognoise.com/index.php/topic,27563.0.html

Cheers!

MF.

[erwink]

Oups. I went away from Dimensional reporting due to performance issues (I have DMR). But you're probably right and I should go back to it

Now I've a slicer with hardcoded the location

filter([Dimensions].[VCRPortfolio].[VCRPortfolioH].[Location], caption([Dimensions].[VCRPortfolio].[VCRPortfolioH].[Location])='ZUG')

Is that wrong? because  it does not filter anything ?! Everything is returned

For test purpose I display the Location as dimension and as caption..and caption is as expected. So no clue why the slicer does not work

[MFGF]

Oups. I went away from Dimensional reporting due to performance issues (I have DMR). But you're probably right and I should go back to it

Now I've a slicer with hardcoded the location

filter([Dimensions].[VCRPortfolio].[VCRPortfolioH].[Location], caption([Dimensions].[VCRPortfolio].[VCRPortfolioH].[Location])='ZUG')

Is that wrong? because  it does not filter anything ?! Everything is returned

For test purpose I display the Location as dimension and as caption..and caption is as expected. So no clue why the slicer does not work

Hi,

As above, if you are displaying the members in question, replace the level in your crosstab with the dimensional filter expression. In this case you don't need a slicer.  if you are not displaying the members but just want the measure values to reflect the location context, then use a slicer filter.

Cheers!

MF.

[erwink]

Thank you so much!!
It works when replacing the member