set null for total line with IgnoreUnrelatedimensions

Question

Suppose, a measure group doesn't have any relation with Product dimension. We can easily make the report understandable by setting IgnoreUnrelatedDimensions = FALSE. But Total line is still showing the sum value. Is there any way so that i can set null for Total line too? I don't want to set the IsAggregatable = False of Product dimension as I need it for other measure groups. Any help?

Note: I want to have this effect in TOTAL line of any attribute of that dimension. Is there any generic way to do this?

Thanks in advance.

Answer 1

You could put the following into your calculation script:

SCOPE( { Root([Products]) } * MeasureGroupMeasures('name of your measure group'));
    This = NULL;
END SCOPE;

And if you want to have the same behavior for two more unrelated dimensions, say DimA and DimB, you would duplicate this block for each dimension like this:

SCOPE( { Root([DimA]) } * MeasureGroupMeasures('name of your measure group'));
    This = NULL;
END SCOPE;
SCOPE( { Root([DimB]) } * MeasureGroupMeasures('name of your measure group'));
    This = NULL;
END SCOPE;

You must keep these separate, as you want to have the value null for the All elements of DimA, irrespective if the DimB members are at the All level as well or not.