Filter data but not the categories

[esar]

Hello Experts,

I have a DMR DQM model in place and I am reporting on top of it. I have a scenario where i want to show all the categories under a dimension, but filter out data alone for some of the dimension values. So immaterial of which category i filter out, I should be able to see all the categories, but filter data for the selective categories.

I am fairly new to Dimensional reporting, so any help would be much appreciated.

Sample:

Country   Population
USA           2 mil
China          100 mil
India           90 mil
   
Filtered out India   
Country   Population
USA           2 mil
China           100 mil
India            0

[MFGF]

Hello Experts,

I have a DMR DQM model in place and I am reporting on top of it. I have a scenario where i want to show all the categories under a dimension, but filter out data alone for some of the dimension values. So immaterial of which category i filter out, I should be able to see all the categories, but filter data for the selective categories.

I am fairly new to Dimensional reporting, so any help would be much appreciated.

Sample:

Country   Population
USA           2 mil
China          100 mil
India           90 mil
   
Filtered out India   
Country   Population
USA           2 mil
China           100 mil
India            0

Looks like a good candidate for a slicer. A slicer will affect the measure values displayed in the cells but not the member sets used in the rows and columns.

Drag a slicer member set into the slicer area of the query and use the expression

set([Your Country level] -> ?Your Country parameter?)

Cheers!

MF.

[esar]

Thanks MFGF..

Your Expression filters the query for the selected member. How to do the vice versa. To filter out the selected Member?.

And also is there a way to do this in a data item. I need the total population amount (unfiltered) for elsewhere in the same query for calculating some %s. Putting a slicer would filter the entire query

[MFGF]

Thanks MFGF..

Your Expression filters the query for the selected member. How to do the vice versa. To filter out the selected Member?

except([Your Country level], [Your Country level] -> ?Your Country parameter?)

This will deliver all members from the country level except the one you choose in the prompt.

MF.

[esar]

Wow .. that was quick.. Thanks MFGF.. I have updated my previous post with another request...   About having the filter done in data item instead of slicer.

It dint strike me earlier that I shouldnt filter my entire qry.

[MFGF]

Wow .. that was quick.. Thanks MFGF.. I have updated my previous post with another request...   About having the filter done in data item instead of slicer.

It dint strike me earlier that I shouldnt filter my entire qry.

If you want to see all the members but with zero measure values, you need a slicer - it's as simple as that. If you use an expression in your rows with a dimensional operator or a filter() function you will remove some of the row members, and you indicated you didn't want that. A slicer it is, then.

MF.

[esar]

Fair Enough.. Thank you MFGF