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];
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];