How to display hierarchy member where measure is null (mdx)

Question

I want to display hierarchy in SSRS which include the members with null measure. The problem in mdx query is that when I remove NON EMPTY clouse I got 'System.OutOfMemoryException' due to the large crossjoin between time, products and hierarchy dimension. Any idea how to soleve it? The query is as follow:

SELECT 
NON EMPTY  { (
[Measures].[SOME MEASURE]
   )} ON COLUMNS,
   NON EMPTY { (
[Organization Structure].[Description].[Description].Allmembers
* [Organization Structure].[ID].[ID]
* [Organization Structure].[ParentID].[ParentID]
*[Products].[Name].[Name]
*[Time].[Hierarchy].[MothsAndDays]
)} 
DIMENSION
 PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME, PARENT_UNIQUE_NAME, 
 LEVEL_NUMBER ON ROWS 
 FROM [MyCube]

Answer 1

Can you use the NonEmpty (without gap) function instead on the hierarchies you are ok to get rid of nulls? So for example:

SELECT 
//NON EMPTY  
{ (
[Measures].[SOME MEASURE]
   )} ON COLUMNS,
//NON EMPTY 
{ (
[Organization Structure].[Description].[Description].Allmembers
* NONEMPTY([Organization Structure].[ID].[ID],[Measures].[SOME MEASURE])
* [Organization Structure].[ParentID].[ParentID]
* NONEMPTY([Products].[Name].[Name],[Measures].[SOME MEASURE])
* [Time].[Hierarchy].[MothsAndDays]
)} 
DIMENSION
 PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME, PARENT_UNIQUE_NAME, 
 LEVEL_NUMBER ON ROWS 
FROM [MyCube]

Answer 2

Assuming the memory problems that you have appear on the Reporting Services server, not the Analysis Services server, you can use the HAVING clause at the end of the ROWS select part instead of NON EMPTY at the start of it to restrict the rows:

SELECT 
NON EMPTY  { (
[Measures].[SOME MEASURE]
   )} ON COLUMNS,

[Organization Structure].[Description].[Description].Allmembers
* [Organization Structure].[ID].[ID]
* [Organization Structure].[ParentID].[ParentID]
*[Products].[Name].[Name]
*[Time].[Hierarchy].[MothsAndDays]

DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME, PARENT_UNIQUE_NAME, LEVEL_NUMBER 

HAVING <whatever the condition is to display a row>

ON ROWS 
 FROM [MyCube]

As you did not state exactly how you want to determine the rows that you want to display, I left that as <whatever the condition is to display a row> above. The way this works is that Analysis Services first builds up the result set for the complete cross product of all row hierarchies, and then evaluates the condition to remove rows from this.