Search code examples
reporting-servicesssasssrs-2012

Count the average sales per open hour interval MDX


I'm still learning MDX and I want to conduct a rather complex (?) query. I've found each combination of Date and Open hour time where there has been any sales using:

 SELECT NON EMPTY 
    { 
        [Measures].[Quantity] 
    } ON COLUMNS, 
 NON EMPTY 
    {   
        (
            [Dim Date].[Calender].[Date].ALLMEMBERS 
            ,[Dim Time].[Open Hour Bucket].[Open Hour Bucket].ALLMEMBERS 
        ) 
    } ON ROWS 
 FROM 
    ( SELECT 
        ( 
            { 
                (
                    [Dim Store2].[Store Name].&[ActualStoreName]
                    ,[Dim Date].[Calender].[2014]
                ) 
            } 
        ) ON COLUMNS 
    FROM [iceCube]
    ) 

My result set then becomes like this:

                                Quantity
 2014-11-07     11:00 - 11:59       1
 2014-11-07     12:00 - 12:59       10
 2014-11-08     11:00 - 11:59       3
 2014-11-08     12:00 - 12:59       2
 2014-11-10     13:00 - 13:59       1

Which is what I want at this stage. However, getting from the above result set into a average between hour like this:

                    Average
 11:00 - 11:59      2
 12:00 - 12:59      6
 13:00 - 13:59      1

is a Connection I don't understand how I would conduct.

I want to find the average sales per the year 2014 as I want to create a chart in SSRS using a specific week's sales as a column chart in SSRS, while having a line of the average sales for 2014 as a separate line. This also complicates things, as I need to have the first query (albeit on a week's basis, and not in 2014 as it is now), and 2014's average in the same data set, which I do not know how to do.

I am thinking that the query to find the average sales per 2014 must be conducted in SSAS as a calculated measure, and then in SSRS I can try to find this week's sales and the average of 2014 in the same data set somehow.

Does anyone have any idea with this?

Regards,

EDIT

Running GregGalloway's answer I've got this output:

                        Quantity    YearlyHourAvg
2014-01-01  09:00-09:59 (null)      #Error
2014-01-01  10:00-10:59 (null)      #Error
.           .           .           .           
.           .           .           .
.           .           .           .
2014-12-31 21:00-21:59  (null)      #Error

(where the spots represent that it continues like that over the row), i.e. the Query gives every Open Hour Bucket value from the first to the last of January.

When I tried to run the Query, it actually worked using only With MEMBER AS... and the original query. However adding the [Measures].[YearlyHourAvg] in the Select clause caused these problems.

I am currently testing this query in SSRS and SSMS.

EDIT2

Perhaps I was not clear in the original post, but I need the average to be on per *time interval* basis, and not by per time *and* date basis. I.e. I need the average sale of the time spans 09:00-09:59,10:00-10:59...


Solution

  • Try the following. It should add a new column which is the average for that hour across the whole 2014 year. The average should be an average per day which had any sales during that hour. If you want a simple average by the number of days in 2014 please write back and we can discuss. If you have questions about the changes I made please ask.

    WITH
    MEMBER [Measures].[YearlyHourAvg] as
     Avg(
      Descendants(
       [Dim Date].[Calender].[2014],
       [Dim Date].[Calender].[Date]
      ),
      [Measures].[Quantity]
     )
     SELECT
        { 
            [Measures].[Quantity],
            [Measures].[YearlyHourAvg] 
        } ON COLUMNS, 
     NonEmpty(
        {   
            (
                [Dim Date].[Calender].[Date].ALLMEMBERS 
                ,[Dim Time].[Open Hour Bucket].[Open Hour Bucket].ALLMEMBERS 
            ) 
        } ,
       [Measures].[Quantity]
      )
     ON ROWS 
     FROM 
        ( SELECT 
            ( 
                { 
                    (
                        [Dim Store2].[Store Name].&[ActualStoreName]
                        ,[Dim Date].[Calender].[2014]
                    ) 
                } 
            ) ON COLUMNS 
        FROM [iceCube]
        )