Search code examples
reporting-servicesssasmdxcube

Usingdifferent measures in MDX


I want my SSRS report to contain a couple of calculated measures. First of all I have a measure of time in my cube (Average Time in minutes). I want to filter this measure on certain event types (Events like breaks, lunches and Toilet breaks). These filters work fine in the cube.
After these calculated measures I want some regular measures like Calls, Average waiting time etc.
Following up I want to sort these by Department and Employee so my Table will look like the following:

                          ----------------------------------
                         |Calls| Event Type 1| Event Type 2|
 -----------------------------------------------------------
| Department  | Employee |Data |    Data2    | Data 3      |
 -----------------------------------------------------------

If I use the following Query I will get my Event Types but I can't seem to add any other measure to my query:

SELECT 
  NON EMPTY 
      [Measures].[Average Time (in minutes)]
    * 
      [Event Type].[Event Type].MEMBERS ON 0
 ,NON EMPTY 
    {
        [Employee].[Department].[Department].ALLMEMBERS
      * 
        [Employee].[Employee].ALLMEMBERS
    } ON 1
FROM [Model];

Solution

  • You could do something like this:

    WITH 
      MEMBER [Measures].[Average Time Type1] AS 
        (
          [Measures].[Average Time (in minutes)]
         ,[Event Type].[Event Type].[Event Type 1]
        ) 
      MEMBER [Measures].[Average Time Type2] AS 
        (
          [Measures].[Average Time (in minutes)]
         ,[Event Type].[Event Type].[Event Type 2]
        ) 
    SELECT 
      NON EMPTY 
        {
          [Measures].[Average Time Type1]
         ,[Measures].[Average Time Type2]
        } ON 0
     ,NON EMPTY 
        {
            [Employee].[Department].[Department].ALLMEMBERS
          * 
            [Employee].[Employee].ALLMEMBERS
        } ON 1
    FROM [Model];