How to exclude or use Format_String for null values in MDX

1
votes

I have this simple MDX query that brings back all elements under the customer hierarchy dimension (Customer Description, Customer Business, Customer ID). The query executes fine but I need to exclude the rows that return #null. I have tried using Non Empty but it is not working.

   WITH 
   MEMBER [Measures].[Label] AS [Customer Hierarchy].CURRENTMEMBER
   MEMBER [Measures].[UniqueName] AS [Customer Hierarchy].[Customer ID].CURRENTMEMBER.UNIQUENAME 
SELECT 

    NON EMPTY {
    [Measures].[Label], 
        [Measures].[UniqueName]
        } ON COLUMNS 
    ,  NON EMPTY {[Customer Hierarchy].[Customer ID].ALLMEMBERS } ON ROWS 


FROM [SUMMARY]

Query Results (Customer, Customer Description, Customer ID)

enter image description here

1
mdxmondrian
Could you please post the output? – Danylo Korostil
where is this being rendered? Excel? I'm not familiar with an output of #null from mdx – whytheq
how can you have a uniquename for a member that is null - looks to me like there is something wrong with the cube – whytheq
This is being rendered in Pentaho Report Designer. It's a Mondrian Schema. – Tone

1 Answers

0
votes

To try to help diagnose what is happening - what do you get returned if you run this:

WITH 
  MEMBER [Measures].[Label] AS 
    [Customer Hierarchy].CurrentMember 
  MEMBER [Measures].[Label2] AS 
    [Customer Hierarchy].CurrentMember.Member_Caption
  MEMBER [Measures].[UniqueName] AS 
    [Customer Hierarchy].[Customer ID].CurrentMember.UniqueName 
SELECT 
  NON EMPTY 
    {
      [Measures].[Label2]
    } ON COLUMNS
 ,NON EMPTY 
    {[Customer Hierarchy].[Customer ID].ALLMEMBERS} ON ROWS
FROM [SUMMARY];