MDX query does not return any values, which it should - possibly due to the where clause

Question

I'm trying to create a data set in SSRS with the following MDX Query:

 SELECT NON EMPTY { [Measures].[Quantity],[Measures].[Total Price],
[Measures]Margin],[Measures].[Profit Margin] } on columns,
NON EMPTY {([Dim Product2].[Product Group], [Dim Product2].[Summary],
[Dim Time].[Open Hour Bucket].allmembers)} on rows
from [theyseemecubin]
where {([Dim Store2].[Store Key].&[1046],[Dim Date].[Calender].[Date].&[2014-11-20])}

where I want to summarize some store's Daily sales, for a specific date. When I only had:

where [Dim Store2].[Store Key].&[1046]

in the where clause, the Query returned the correct data. But when I attempted to insert the Date as well, then the result set becomes nothing, although I have checked in the cube that there should have been 14 units sold that day.

What am I doing wrong? Have I misinterpreted how to use the SQL equivallence Where StoreKey = 1046 AND Date=2014-11-20 in MDX?

Answer 1

It looks ok as it is:

SELECT 
  NON EMPTY 
    {
      [Measures].[Quantity]
     ,[Measures].[Total Price]
     ,[Measures].[Margin]
     ,[Measures].[Profit Margin]
    } ON COLUMNS
 ,NON EMPTY 
    {
      (
        [Dim Product2].[Product Group]
       ,[Dim Product2].[Summary]
       ,[Dim Time].[Open Hour Bucket].ALLMEMBERS
      )
    } ON ROWS
FROM [theyseemecubin]
WHERE 
  {
    (
      [Dim Store2].[Store Key].&[1046]
     ,[Dim Date].[Calender].[Date].&[2014-11-20]
    )
  };

You don't need the curly braces as just the tuple will be sufficient:

SELECT 
  NON EMPTY 
    {
      [Measures].[Quantity]
     ,[Measures].[Total Price]
     ,[Measures].[Margin]
     ,[Measures].[Profit Margin]
    } ON COLUMNS
 ,NON EMPTY 
    {
      (
        [Dim Product2].[Product Group]
       ,[Dim Product2].[Summary]
       ,[Dim Time].[Open Hour Bucket].ALLMEMBERS
      )
    } ON ROWS
FROM [theyseemecubin]
WHERE 
    (
      [Dim Store2].[Store Key].&[1046]
     ,[Dim Date].[Calender].[Date].&[2014-11-20]
    );

A tuple is a logical AND so yes it is equivalent to the sql. If you want to do a logical OR you need to change things a little and make use of a set.

Maybe one thing to check - is this defintiely the full name of the member? [Dim Date].[Calender].[Date].&[2014-11-20] ... most mdx tools (SSMS and MDXstudio) you should be able to just drag and drop the member into the query pane to see the full name.