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])

 

 

Answer 1

 

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