none
forced row in query

    Question

  • I am trying to have a fixed 5-row dataset in MDX query as below. However the [Accounts].[RANGE].&[4] has no data. The query only returns 4 rows. Is there any way to force the [Accounts].[RANGE].&[4] showup?
     
    Thanks,
     

    Code Snippet

    SELECT {[Measures].[Accounts Count],[Measures].[BALANCE]} on 0,

    { [Accounts].[RANGE].&[0],

    [Accounts].[RANGE].&[1],

    [Accounts].[RANGE].&[2],

    [Accounts].[RANGE].&[3],

    [Accounts].[RANGE].&[4]

    } ON 1

    FROM [Accounting]

    WHERE ([Accounts].[ACC CATEGORY].[BUSINESS])

     

     

    Thursday, July 10, 2008 6:55 PM

Answers

  •  

    Hi,

     

    to be able to display all 5 members you need to modify your query:

     

    Code Snippet

    WITH

    MEMBER MEASURES.X AS

    ' ( [Accounts].[ACC CATEGORY].[BUSINESS], [Measures].[Accounts Count] ) '

    MEMBER MEASURES.Y AS

    ' ( [Accounts].[ACC CATEGORY].[BUSINESS], [Measures].[BALANCE] ) '

    SELECT

    { [Measures].[X], [Measures].[Y] } ON 0,

    { [Accounts].[RANGE].&[0],

      [Accounts].[RANGE].&[1],

      [Accounts].[RANGE].&[2],

      [Accounts].[RANGE].&[3],

      [Accounts].[RANGE].&[4]

    } ON 1

    FROM [Accounting]

     

     

    If you leave that attribute in filter (or in subselect i.e.), SSAS will automatically reduce your set to only members with data in current context (both attributes, RANGE and ACC CATEGORY, are members of Accounts dimension, hence). When removed to a measure, query behaves differently but should return the same result (with a null for member &4).

     

    Another, more pretty way of achieving the same thing is to put that member from filter to columns:

     

    Code Snippet

    SELECT

    { { [Accounts].[ACC CATEGORY].[BUSINESS] } *

      { [Measures].[Accounts Count], [Measures].[BALANCE] }

    } on 0,

    { [Accounts].[RANGE].&[0],

      [Accounts].[RANGE].&[1],

      [Accounts].[RANGE].&[2],

      [Accounts].[RANGE].&[3],

      [Accounts].[RANGE].&[4]

    } ON 1

    FROM [Accounting]

     

    Bottomline is - member must go away from filter/subselect. To columns, or to calculations (measures). In order for current context to be free.

     

     

    Tomislav Piasevoli

    Business Intelligence specialist

    http://www.softpro.hr

    Thursday, July 10, 2008 9:16 PM
    Answerer