Using the MDX Compatibility Property
Empty positions in a hierarchy can affect some functions in Microsoft® SQL Server™ version 7.0 OLAP Services. For example, DrillDownLevel([Romania]) returns an empty set because Romania has no states or provinces. For this reason, a connection string property, MDX COMPATIBILITY, is provided for backward compatibility with client applications developed using SQL Server 7.0 OLAP Services. The following table describes this property.
| MDX COMPATIBILITY property value | Description |
|---|---|
| 0 | (Default) The same as Value 1 |
| 1 | Compatible with SQL Server 7.0 OLAP Services |
| 2 | Compatible with SQL Server 2000 Analysis Services |
If the MDX COMPATIBILITY property value is set to 1, a client application using DrillDownLevel([Romania]) receives a single dummy member for the States/provinces level of Romania, which can then be drilled down again, incrementally, to provide access to the city members of the Romanian geography hierarchy. When this property is set to 2, PivotTable® Service returns an empty set for this function. The following table shows which functions are affected by empty positions in a hierarchy.
| Function called on empty positions |
Results (MDX COMPATIBILITY=1) |
Results (MDX COMPATIBILITY=2) |
|---|---|---|
| AllMembers Members Descendants |
Returns the name of the empty level in the hierarchy | Ignores empty positions on the resulting axis |
| Range (:) | Returns the name of the empty level in the hierarchy | Returns an error |
| Children | Returns the name of the empty level in the hierarchy | Returns all nonempty child positions and all children of any empty positions in the hierarchy |
| DrillDownLevel DrillDownLevelTop DrillDownLevelBottom DrillUpLevel |
Returns the name of the empty level in the hierarchy | Returns an empty set |
| DrillDownMember DrillDownMemberTop DrillDownMemberBottom DrillUpMember |
Returns the name of the empty level in the hierarchy | Skips empty positions in the hierarchy and returns the first position that is not empty |
| LastPeriods YTD QTD MTD WTD |
Returns the normal value of the requested cell | Returns an error |
| AddCalculatedMembers VisualTotals |
Returns the normal value of the requested cell | Skips empty positions in the hierarchy and returns the first position that is not empty |
| Parent Ancestor FirstChild LastChild |
Returns the normal value of the requested cell | Skips empty positions in the hierarchy |
| ClosingPeriod OpeningPeriod Cousin ParallelPeriod |
Returns the name of the empty level in the hierarchy | Returns an error |
| PrevMember NextMember Lead Lag |
Returns the normal value of the requested cell | Returns the resultant real members at that level |