Note: this feature is NOT supported in the 1200 compatibility mode for SSSAS Tabular 2016 as Microsoft removed the undocumented hooks that we were using to expose this functionality
SQL Server Data Tools 2012 (formerly BI Development Studio) Tabular projects allow adding hierarchies to tables. However, a UI for setting the HideMemberIf property is not provided. This prevents Tabular projects from creating ragged hierarchies. For example,
in the Adventure Works Employee hierarchy, Stephen Jiang not only is a manager, but has also sold some products. Without setting HideMemberIf, a blank shows up in the hierarchy (left PivotTable). After setting HideMemberIf on Level3 to NoName, the blank employee
name row drops out as desired (right PivotTable):
Warning: While the HideMemberIf setting works in Tabular models, it is not officially supported by Microsoft. If you encounter a bug in how Tabular handles HideMemberIf and open a support case, Microsoft may not provide support. For example, there are reports
that Tabular HideMemberIf will cause Visual Studio to
. There are other reports that
HideMemberIf doesn't work in PerformancePoint
with Tabular models.
In order to set HideMemberIf, switch to the diagram view where you manage hierarchies. Highlight one or more levels then right click and choose Set HideMemberIf...
When the dialog pops up set HideMemberIf. When you click OK the change is applied to the workspace database immediately. ProcessFull is required during this operation due to a bug in SSAS 2012.
Note that some changes like renaming levels will wipe out the HideMemberIf setting. BIDS Helper backs up the HideMemberIf setting in an annotation on the database. That way, the
feature can prompt you to fix this setting.
After changing a HideMemberIf setting, BIDS Helper may prompt you for the credentials to a data source which is using stored credentials if you have not already entered those credentials during this SSDT editing session. This prompt ensures that Tabular HideMemberIf
succeeds (since it needs to ProcessFull the table) and also that data source credentials in the workspace database do not get wiped out as HideMemberIf settings are applied.