Home moreMDX – NON EMPTY returning different number of columns with no data
MDX – NON EMPTY returning different number of columns with no data

MDX – NON EMPTY returning different number of columns with no data

So I have a query that, if there is no data for today, won’t return Store and Product columns, it will be only the measures returned. Is it possible to always return fixed columns, even if they’re empty? I mean only columns, empty rows should be omitted.

SELECT {
       [Measures].[Hourly Sales %]
       ,[Measures].[Pack Count Hourly]
       ,[Measures].[Packs Canceled Hourly]
       ,[Measures].[Packs Done Hourly]
} ON COLUMNS
,NON EMPTY(
     [Store].[Store].[Store Number]
     ,[Store].[Store].[Chain]
     ,[Product].[Class].[Product Class]
    ,[Time Date].[Date].[Date].&[TODAY]
            ,
            { 
                  [Time Hour].[Hour].&[9]
                , [Time Hour].[Hour].&[10]
            }
) ON ROWS
FROM [Cube]    
)

What I want is a table with such columns:

Store Number Store chain Product Class Date Hour Sales % Count Canceled Done
First row data
Second row data

But with no data I got only a table with such columns that messes entire query:

Sales % Count Canceled Done