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
Quote from: esar on 21 Apr 2014 10:20:44 AM
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.
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
Quote from: esar on 21 Apr 2014 11:45:47 AM
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.
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.
Quote from: esar on 21 Apr 2014 11:54:07 AM
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.
Fair Enough.. Thank you MFGF