none
"All Other Members" calculation that shows total of members NOT chosen by user

    Question

  • Hi

    We have a conference dimension with 4 members, with the following numbers:

    Conference   No.Patients   
    -----------------------
    A               100       
    B                50      
    C                60
    D                20      
    -----------------------
    Total           230     

    Users are able to see/choose all the conferences plus an aggregate of "All other conferences" that were not selected, e.g.

    Conference   No.Patients   
    -----------------------
    A               100     
    C                60     
    Others           70     
    -----------------------
    Total           230       

    If the user selects all the conferences then Others would be null or 0

    How do we do this?

    Cheers,
    Steve

    PS Dannie from my team has posted a similar problem before under a different heading, but that related to users being restricted to viewing just one conference.  In this case, the user can view some or all of the conferences.

    Thursday, June 14, 2012 2:56 AM

Answers

  • What tool are you using to browse this, as Philip mentions you will need two sets, however I am not sure how you will get the set of selected items if you don't have control over the selection panel.

    If you are working in reporting services for example, the parameter set (or chosen items) is passed back to you, so you know what to exclude from the [Others] set above.

    If however you have to do this in the cube, I'm not sure you can get the list of items selected. You could try and do this through the use of the AXIS funtion, but that may only work if you only have conference on the axis - will have to be Rows of Columns only - you specify, since the definition will be specified in that form. Something like the following may work?

    CREATE Member CURRENTCUBE.[conference DIM].[conference].[Others]
     AS SUM(Except({[conference DIM].[conference].[conference].members}, {  Axis(1).Members  }), [Measures].[No of Patients]) ;
    Thursday, June 14, 2012 6:55 AM

All replies

  • Hello,

    Try the following:

    Define 2 named sets:

    a) the first is static and contains all the members ( but not the "all members" member): 

     CREATE SET CURRENTCUBE.[Static All Conferences]
     AS [conference DIM].[conference].[conference].members, DISPLAY_FOLDER = 'Sets'  ;

    The definition of this set has to preceed the definition of the second one in the calculation script

    b) the second is dynamic:

     CREATE DYNAMIC SET CURRENTCUBE.[Ohters]
     AS Except({[Static All Conferences]},{[conference DIM].[conference].[conference].members}), DISPLAY_FOLDER = 'Sets'  ;

    I never try such a scenario, so if you give it a chance, let me know if it works

    Philip,

    Thursday, June 14, 2012 6:30 AM
  • What tool are you using to browse this, as Philip mentions you will need two sets, however I am not sure how you will get the set of selected items if you don't have control over the selection panel.

    If you are working in reporting services for example, the parameter set (or chosen items) is passed back to you, so you know what to exclude from the [Others] set above.

    If however you have to do this in the cube, I'm not sure you can get the list of items selected. You could try and do this through the use of the AXIS funtion, but that may only work if you only have conference on the axis - will have to be Rows of Columns only - you specify, since the definition will be specified in that form. Something like the following may work?

    CREATE Member CURRENTCUBE.[conference DIM].[conference].[Others]
     AS SUM(Except({[conference DIM].[conference].[conference].members}, {  Axis(1).Members  }), [Measures].[No of Patients]) ;
    Thursday, June 14, 2012 6:55 AM