how to avoid the error "Category member XXX defined as an empty set" in icCube?

Question

I've got the following MDX code to create a category dimension with 4 members:

all members that make up of 0-50% of the measure (TopPercent)
members 50-80%
members 80-95%
members 95-100%

The code works perfectly on levels with a lot of members:

WITH
MEMBER [measures].[v] as eval([Tijd].[Tijd].[jaar].[2018],[Measures].[Bedrag])

set [selection] as Order( nonempty([Categorie].[Categorie].[categorie].members,[measures].[v]), [measures].[v], BDESC)  

CATEGORY HIERARCHY [Stats].[ABCD], DEFAULT_MEMBER_NAME = "Totaal", LEVEL_NAME_PATTERN="L - ABCD - ${levelDepth}"
CATEGORY MEMBER [Stats].[ABCD].[Totaal].[A (0-50%)] as
     TopPercent([selection],50, [measures].[v] ), ADD_CHILDREN=true
CATEGORY MEMBER [Stats].[ABCD].[Totaal].[B (50-80%)] as
    TopPercent([selection],80, [measures].[v] )
    - TopPercent([selection],50, [measures].[v]), ADD_CHILDREN=true
CATEGORY MEMBER [Stats].[ABCD].[Totaal].[C (80-95%)] as
    TopPercent([selection],95, [measures].[v])
    - TopPercent([selection],80, [measures].[v] ), ADD_CHILDREN=true
CATEGORY MEMBER [Stats].[ABCD].[Totaal].[D (95-100%)] as
    Order([selection], [measures].[v], BDESC)
    - TopPercent([selection],95, [measures].[v]), ADD_CHILDREN=true

SELECT
// Measures
{[measures].[v]} On 0,
// Columns    
[Stats].[ABCD].[L - ABCD - 1].members on 1,
// Rows   
[Stats].[ABCD].[L - ABCD - 2].members on 2
FROM (select  [Tijd].[jaar].[2018] on 0 from [Spendzoom])
/*ic3navigation*/

But when I run the MDX code with:

set [selection] as Order( nonempty([Categorie].[Categorie].[type].members,[measures].[v]), [measures].[v], BDESC)

I get the error: Category member "[Stats].[ABCD].[Totaal].[C (80-95%)]'defined as an empty set.

I have tried to rewrite the definitions, as:

subcubeminus(TopPercent .... , TopPercent)

But that gave completely strange results.

How can I overcome this error and have a generic approach that allways works regardless of the contents of the hierarchy & level in the [selection] definition?

ic3 · Answer 1 · 2019-06-28T14:17:53.857

1

To understand what happening you should check how TopPercent works (it's not really whay you expected).

Try this MDX :

WITH
  MEMBER [measures].[v] as eval([Tijd].[Tijd].[jaar].[2018],[Measures].[Bedrag])
  STATIC SET [selection] as [Categorie].[Categorie].[categorie].members
SELECT
  [measures].[v] On 0,
  TopPercent([selection],95, [measures].[v]) on 1,
  TopPercent([selection],80, [measures].[v] ) on 2
FROM 
  (select  [Tijd].[jaar].[2018] on 0 from [Spendzoom])

As you see both return the same set, and that is not what you are looking I guess.

edited Jun 28 '19 at 14:17

answered Jun 28 '19 at 14:10

ic3

7,917
14
67
115

Not sure if I understand. When I run the 1st TopPercent (on 1) I get 26 members, the 2nd (on 2) gives me 12 members. So not the same set? – Arthur Jun 28 '19 at 14:42
How can you get then an empty Set in the category ? .. play a bit the MDX and the debugger – ic3 Jun 29 '19 at 08:16
For example, when [selection] results in just one member (because of global filters). Then it fits in A while B,C and D are empty and result in an error (very nasty and I do not know how to solve that). – Arthur Jul 03 '19 at 11:14
Still don't see it in our scenario. – ic3 Jul 04 '19 at 06:37
That would be great. Thanks! – Arthur Jul 05 '19 at 07:15

score 1 · Accepted Answer · answered Jul 05 '19 at 11:08

As ic3 mentioned in the comments, as of icCube 6.8.10, icCube allows now to have empty categories. Y

For me this means, business wise, that regardless of the global filter settings the categories ALWAYS work. In case it is an empty set, it results a blank value in the dashboards.

example of a Parato analysis for just 1 vendor (bedrag = amount, #Fact = nr of invoices, #Lev = nr of suppliers)

Exactly as desired.

how to avoid the error "Category member XXX defined as an empty set" in icCube?

2 Answers2