Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2005 Forums  Analysis Server and Reporting Services (2005)  MDX RANK function: unexpected results

Author  Topic  AntonioP Starting Member 1 Post Posted - 2009-08-31 : 06:20:44 Hi all, i'm using SQL Server 2005 (build 9.00.3239.00). In one of our cubes (called "Cube 1") we have defined an MDX calculation script. This script does a number of things, among these it creates an hidden set based on our shared [Country] dimension: CREATE HIDDEN SET [CountryComponents] AS GENERATE( [CalculatedCountries] , { [Country].[Country].CurrentMember , StrToSet([Country].[Country].CurrentMember.PROPERTIES("Component Countries MDX")) , [Country].[Country].CurrentMember } ,ALL ); A simple inspection of the [CalculatedCountries] set through the Management Studio gives me these members (i'm not listing all members, just the first ones to give an idea): Belgium-Luxembourg Belgium Luxembourg Belgium-Luxembourg BRICs Brazil Russia India China BRICs EU Developed countries United Kingdom Belgium France Germany Italy Netherlands Sweden Austria Luxembourg Finland Greece Ireland Portugal Spain Denmark Slovenia EU Developed countries EU Developing countries Bulgaria Cyprus Czech Republic Estonia Hungary Latvia Lithuania Malta Poland Romania Slovakia EU Developing countries ... So far so good, no unexpected results. The cube script then contains this statement: CREATE MEMBER CURRENTCUBE.[Measures].[CountryFirstOccurance] AS RANK([Country].[Country].CurrentMember, [CountryComponents]) , VISIBLE = 0; I view the [Measures].[CountryFirstOccurance] calculated member through Management Studio with this simple MDX query: select [CalculatedCountries] on rows , [Measures].[CountryFirstOccurance] on columns from [Cube 1]; to see the value of [Measures].[CountryFirstOccurance] for each member in the [CalculatedCountries] set. Below are the top results from this query: Belgium-Luxembourg -> 1 BRICs -> 5 EU Developed countries -> 28 EU Developing countries -> 29 ... Here is where i'm having some strange results: for member "EU Developed countries" the value of [Measures].[CountryFirstOccurance] is 28, when in fact it should be 11, right ? The value of [Measures].[CountryFirstOccurance] is correct for all other members in the [CalculatedCountries] set. For "EU Developed countries" the rank function should return 11 because that is the 1st occurrance of member "EU Developed countries" in the [CalculatedCountries] set. Instead it returns 28, which is in fact the 2nd occurrance of "EU Developed countries" in the set. For all other members in the set the [Measures].[CountryFirstOccurance] member rightly shows the first occurrance of each country in the set. I don't understand this behaviour, if anyone could provide any hint it would be much appreciated. Thanks in advance Antonio