Problem with Dimensional Metrics and filters.

[dinzana]

Thanks for checking this thread.

Division is an attribute that belongs to the following hierarchy structure. Parent to Child repectively

Chain > Division > Group > Department > etc....


I have 2 metrics

-Sales = (sum(sales), dimensionality Report Level)

-Sales for Total Group = (sum(sales), dimensionality Group (ignore filtering) and (standard grouping))

In a report with a filter on Division (Division = 2) and Month (Month = 'Jan'), there are three passes of SQL, which I expect.

metrics: Sales Sales for Total Group
-------------------------------------------------------------------
MONTH | DIVISION | GROUP


Pass 1. For the Sales metric
(select month, group, sum(sales) from fact table
where division = 2 and month = 'Jan')

Pass 2. For the Sales for Total Group Metric
(select month, group, sum(sales) from fact table
where month = 'Jan')

Pass 3. Joins the two previous passes together for the result.


Notice that the Division filter condition is not included in Pass 2. I know that setting the metric dimensionality to ignore filtering is causing this but in some cases, I do not want the filter to be included, namely when the filter indicates a condition for an attribute that is a child or grandchild of Group.

In short, I want the filter to be there if it is above Group in the hierarchy and I want the filter to be ignored if it is below Group in the hierarchy.

Any ideas how I can achieve this?


Thanks,

-dinzana

 

[iolaper]

I just skimmed through your question, and hopefully I am giving you the right solution here:
Go to your Condition Tab in metric, and click on Advanced.
Uncheck 'Remove related report filter elements'
This should do the trick!