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.