3

Using the following mdx, I'm able to retrieve correct data dynamically.

create CATEGORY MEMBER [Stats].[Top].[All Etabs].[Top 5 Etablissements] 
    as topcount( [Etablissement].[Etablissement].[Etablissement].allMEMBERS, 5, [Measures].[Nbsejours]),ADD_CHILDREN=true

create CATEGORY MEMBER [Stats].[Top].[All Etabs].[Autres Etablissements (>5)] 
    as Except([Etablissement].[Etablissement].[Etablissement].members,  TopCount( [Etablissement].[Etablissement].[Etablissement].MEMBERS, 5, [Measures].[Nbsejours])),ADD_CHILDREN=false

create dynamic set [Top 5 & Others] 
    as {[Stats].[Top].[Top 5 Etablissements], [Stats].[Top].[Autres Etablissements (>5)]}

Select {[Measures].[NbSejours]} on 0,
nonempty ([Top 5 & Others]) on 1
From //[Cube]
( SELECT { {[Geographique].[Zone].[All-M].&[1006]} } ON 0 FROM [Cube])

But, the topCount is not dynamic itself. In this example, the top 5 etablissement never change, only the values do change...

Is there a way to get this with dynamic topCount/topPercent ? Txs, Bertrand.

ic3
  • 7,917
  • 14
  • 67
  • 115
Bertrand Miot
  • 929
  • 5
  • 12
  • What do you mean by dynamic ? – ic3 Jan 21 '15 at 12:49
  • I mean that the top 5 etablissements and their order is established once by the 3 create commands... it is not calculated with regard to the filter. The Etablissements and their order are the same if I query from [Cube] or if I query from only part of Cube (from ( SELECT { {[Geographique].[Zone].[All-M].&[1006]} } ON 0 FROM [Cube])) – Bertrand Miot Jan 21 '15 at 13:07

1 Answers1

2

Categories (*) have not yet the dynamic flag so it's not possible to define a category that will be calculated for each MDX request once as it happens for a set.

So it's going to be something more like (note I've use the SubCubeComplement that is a lot - may be really a lot - faster )

create dynamic set [Top 5] as 
      topcount( [Etablissement].[Etablissement].[Etablissement].members, 5, [Measures].[Nbsejours])

*** End script ***

WITH 
 CATEGORY HIERARCHY [Stats].[Top], DEFAULT_MEMBER_NAME = "All Etabs"
 CATEGORY MEMBER [Stats].[Top].[All Etabs].[Top 5 Etablissements] as 
      [Top 5],ADD_CHILDREN=true
 CATEGORY MEMBER [Stats].[Top].[All Etabs].[Autres Etablissements (>5)] as
      SubCubeComplement([Top 5]),ADD_CHILDREN=false
SELECT
  {[Measures].[NbSejours]} on 0,
  { [Stats].[Top].[Top 5 Etablissements], 
    [Stats].[Top].[Autres Etablissements (>5)] } on 1
From [Cube]

(*) For the people that are not used to icCube, Categories is a way defining a 'new' member as a set of members (they might have different dimensionalities). This ensures for complex calculations, schemas with many-to-many relations that the values are correctly calculated. Otherwise it might be a little nightmare to ensure calculation correctness.

ic3
  • 7,917
  • 14
  • 67
  • 115