SSAS: Hide/Unhide a Measure irrespective of 'IgnoreUnrelatedDimensions' Property of its Measure Group
Introduction to 'IgnoreUnrelatedDimensions' Property
'IgnoreUnrelatedDimensions' is a measure group property which when set to ‘False’ hides the measured value when seen against an unrelated dimension attribute.
To understand this better let’s consider an example from ‘Adventure Works’ cube. Consider the measure group ‘Sales Summary’ and ’Geography’ dimension from ‘Adventure Works’ cube, there no relationship defined between them.
Dimension Usage: No relationship between dimension ‘Geography’ and measure group ‘Sales Summary’
Note: 'IgnoreUnrelatedDimensions' property is by default set to true.
In the above figure, we can notice that ‘IgnoreUnrelatedDimensions’ for ‘Sales Summary’ measure group is set to ‘True’. While it is set to ‘True’, Let’s have a look at ‘Sales Summary’ measures against ’Geography’ dimension attributes.
Since there is no relationship defined between ‘Sales Summary’ measure group and ’Geography’ dimension, as seen in the above figure ‘Sales Summary’ measure values remain same irrespective of ’Geography’ dimension attribute members.
Now let us again look at ‘Sales Summary’ measures against ’Geography’ dimension attributes, after setting the ‘IgnoreUnrelatedDimensions’ property to ‘False’ for ‘Sales Summary’ measure group.
'IgnoreUnrelatedDimensions' property for ‘Sales Summary’ measure group is set to ‘False’
Therefore by setting the ‘IgnoreUnrelatedDimensions’ property to ‘False’ for ‘Sales Summary’ measure group, all the measure values disappears against any ’Geography’ dimension attribute or any unrelated dimension attribute.
Unhide a measure against unrelated dimension attribute, while its measure group property ‘IgnoreUnrelatedDimensions’ is set to ‘False’
Now that we know the usage of ‘IgnoreUnrelatedDimensions’ property, let’s move further. ‘IgnoreUnrelatedDimensions’ property is a measure group level property, whether set to ‘False’ or ‘True’, the scope of this property is all measures within a particular measure group and all the unrelated dimension. There is no facility to enable/disable this property at measure level or at a dimension level.
Recently I came across a requirement, wherein users wanted to look at a measured value against a single unrelated dimension attribute, while the measured value remained null against any other unrelated dimension attribute.
For better understanding let us again consider the ‘Adventure Works’ cube. The requirement here is the users would like to look at the ‘Sales Amount’ measure from ‘Sales Summary’ measure group against ‘Country’ attribute of ‘Geography’ dimension. While the ‘Sales Amount’ is hidden or null against any other unrelated dimension attribute, for example, consider ‘State-Province’ attribute of ‘Geography’ dimension.
Solution:
Step 1: Set the ‘IgnoreUnrelatedDimensions’ property of ‘Sales Summary’ measure group to ‘False’.
Step 2: Introduce a ‘Scope statement’ as shown below as a part script command in cube Calculations.
Scope( [Geography].[Country].Children,[Measures].[Sales Amount]);
this = [Geography].[Country].CurrentMember.Paren;
End Scope;
Here for every member of ‘Country’ attribute, we assign the value of its parent i.e. the ‘All’ member.
In the above figure, we can notice that measures ‘Sales Amount’ as well as calculated member ‘Gross Profit’ are visible against ‘Country’ attribute of ‘Geography’ dimension. Ideally, we expected to see only ‘Sales Amount’ measure. This is because ‘Gross Profit’ is calculated member that calculates the value based on ‘Sales Amount’ Value. If we want the calculated member ‘Gross Profit’ to remain null then we will have to explicitly define it within the scope as shown below.
Scope( [Geography].[Country].Children,[Measures].[Sales Amount]);
[Measures].[Sales Amount] = ([Measures].[Sales Amount],[Geography].[Country].CurrentMember.Parent);
[Measures].[Gross Profit] = null;
End Scope;
Handling Multiple Measures:
Below script is used to enable ‘Sales Amount’ and ‘Total Product Cost’ measure against unrelated dimension attribute ‘Country’, while ‘Gross Profit’ is still null.
Scope( [Geography].[Country].Children,{[Measures].[Sales Amount],[Measures].[Total Product Cost]});
this = [Geography].[Country].CurrentMember.Parent;
[Measures].[Gross Profit] = null;
End Scope;
Hide a measure against unrelated dimension attribute, while its measure group property ‘IgnoreUnrelatedDimensions’ is set to ‘True’
Let’s again consider ‘Sales Summary’ measure group from ‘Adventure Works’ cube, but this time with ‘IgnoreUnrelatedDimensions’ property set to ‘True’. Now the requirement is to hide the ‘Sales Amount’ and ‘Total Product Cost’ measure values while browsing them against ‘Country’ attribute of ‘Geography’ dimension, while both the measures remain seen against any other unrelated dimension attribute.
Solution:
Again introduce a ‘Scope statement’ as shown below as a part script command in cube Calculations.
Scope( [Geography].[Country].Children,{[Measures].[Sales Amount],[Measures].[Total Product Cost]});
[Measures].[Sales Amount] = null;
[Measures].[Total Product Cost] = null;
End Scope;
As you see from the above figure that ‘Sales Amount’ and ‘Total Product Cost’ measure values have disappeared from ‘Country’ attribute as required. But in addition to ‘Sales Amount’ and ‘Total Product Cost’, values of calculated member ‘Gross Profit’ has also disappeared. To avoid this we have to again explicitly define the ‘Gross Profit’ within the scope but without much efforts, just assign the value of ‘Gross Profit’ to itself before assigning null to ‘Sales Amount’ and ‘Total Product Cost’ as shown below.
Scope( [Geography].[Country].Children,{[Measures].[Sales Amount],[Measures].[Total Product Cost]});
[Measures].[Gross Prof= [Measures].[Gross Profit];
[Measures].[Sales Amount] = null;
[Measures].[Total Product Cost] = null;
End Scope;
