0
votes

I want to RANK Customer by Volume and AvgCost in MDX query. However, when I use the query by below:

WITH 
SET TiedRanksbyVol AS 
ORDER( [Dim Customer].[Customer Category].[Customer Category].Members, [Measures].[Quantity], BDESC)

SET TiedRanksbyAvgCost AS 
ORDER( [Dim Customer].[Customer Category].[Customer Category].Members, [Measures].[AvgCost], BDESC)

MEMBER [Measures].[TIED_RANKbyVol] AS
RANK( [Dim Customer].[Customer Category].CURRENTMEMBER, TiedRanksbyVol, [Measures].[Quantity])

MEMBER [Measures].[TIED_RANKbyAvgCost] AS
RANK( [Dim Customer].[Customer Category].CURRENTMEMBER, TiedRanksbyAvgCost, [Measures].[AvgCost])

SELECT 
NOT EMPTY
{
[Measures].[Quantity],
[Measures].[TIED_RANKbyVol],
[Measures].[AvgCost],
[Measures].[TIED_RANKbyAvgCost]
} ON COLUMNS
NOT EMPTY
{
TiedRanksbyVol
} ON ROWS
FROM cube

Result set looks like:

*************************************************************|
       |Quantity |TIED_RANKbyVol| AvgCost| TIED_RANKbyAvgCost|
Alliaz | 26      | 1            | 128    | 4                 |
AXER   | 25      | 2            | 225    | 1                 |
Global | 20      | 3            | 200    | 3                 |
Direct | 5       | 4            | 210    | 2                 |
**************************************************************

I get the ranks without Nulls. But the moment I added a dimension to the ROWS as show below, I get nulls in the return set

SET TiedRanksbyAvgCost AS 
ORDER( [Dim Customer].[Customer Category].[Customer Category].Members, [Measures].[AvgCost], BDESC)

MEMBER [Measures].[TIED_RANKbyVol] AS
RANK( [Dim Customer].[Customer Category].CURRENTMEMBER, TiedRanksbyVol, [Measures].[Quantity])

MEMBER [Measures].[TIED_RANKbyAvgCost] AS
RANK( [Dim Customer].[Customer Category].CURRENTMEMBER, TiedRanksbyAvgCost, [Measures].[AvgCost])

SELECT 
NOT EMPTY
{
[Measures].[Quantity],
[Measures].[TIED_RANKbyVol],
[Measures].[AvgCost],
[Measures].[TIED_RANKbyAvgCost]
} ON COLUMNS
NOT EMPTY
{([Dim Product].[Product].[Product].MEMBERS *
TiedRanksbyVol)
} ON ROWS
FROM cube

Result set looks like:

********************************************************************|
              |Quantity |TIED_RANKbyVol| AvgCost| TIED_RANKbyAvgCost|
Bikes |Alliaz | 26      | 1            | 128    | 4                 |
Bikes |Alliaz | (null)  | 2            | (null) | 3                 |
Cups  |AXER   | 25      | 3            | 225    | 1                 |
Cups  |AXER   | (null)  | 2            | (null) | 1                 |
Marks |Global | 20      | 3            | 200    | 3                 |
Marks |Global | (null)  | 3            | (null) | 3                 |
Towel |Direct | 5       | 4            | 210    | 2                 |
Towel |Direct | (null)  | 4            | (null) | 2                 |
********************************************************************

The result set has (null) and mess the Ranking up. How can I remove the null from the result set to ensure I have multiple dimensions on the rows and measures and ranking on the Columns. Any help will be appreciated.

1

1 Answers

0
votes

In the Rows section try a more strict NONEMPTY:

...
NOT EMPTY
{ NONEMPTY(
      [Dim Product].[Product].[Product].MEMBERS * TiedRanksbyVol,
      [Measures].[Quantity] )
} ON ROWS
...