How to group record by range and put it on rows

Question

Below query compute the percent of work employee wise. i.e. employee on rows and percent of work on columns

With Member [Measures].[EmployeeWisePercent] AS
[Measures].[Hours] / ([Employee].[Employee].[All], [Measures].[Hours]) * 100

Member [Measures].[TotalHours] AS
([Employee].[Employee].[All], [Measures].[Hours])

 SELECT NON EMPTY 
 { [Measures].[Hours], [Measures].[EmployeeWisePercent], [Measures].[TotalHours] } ON COLUMNS,

  NON EMPTY 
  { ([Employee].[Employee].[Employee].ALLMEMBERS ) } 
  DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM
    ( SELECT ( STRTOSET("[Date].[Calendar].[All]", CONSTRAINED) ) ON COLUMNS FROM 
    ( SELECT ( STRTOSET("[Project].[Project Name].&[Self Study]", CONSTRAINED) ) ON COLUMNS FROM 
    [TimeSheetHours Cube])) 

  WHERE ( IIF( STRTOSET("[Project].[Project Name].&[Self Study]", CONSTRAINED).Count = 1,
   STRTOSET("[Project].[Project Name].&[Self Study]", CONSTRAINED), 
   [Project].[Project Name].currentmember ), 
   IIF( STRTOSET("[Date].[Calendar].[All]", CONSTRAINED).Count = 1, 
   STRTOSET("[Date].[Calendar].[All]", CONSTRAINED), [Date].[Calendar].currentmember ) )
   CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

Output is something like following

Employee Name  |    Hours   |  Percent   |   Total Hours
Employee1      |   975.86   |   1.32     |    73421.22
Employee2      |   (null)   |  (null)    |    73421.22
Employee3      |   (null)   |  (null)    |    73421.22
Employee4      |   (null)   |  (null)    |    73421.22
Employee5      |   (null)   |  (null)    |    73421.22
Employee6      |   (null)   |  (null)    |    73421.22
...
...
...

I need to update above query to get the employee count and group it like (0-20%, 20-40%, ... etc.) on rows and count of employee that falls in that range on columns. Something like below:

Employee Name  |  EmployeeCount
0-20%          |    5    
20-40%         |    8
40-60%         |    88
60-80%         |    2
80-100%        |    1

Is there any way we can group percent count on the range specified above? Please help me to prepare mdx query to get desired output.

Note: I don't need employee name on rows as it appears as of now.

Answer 1

Your requirement as I understand is to basically create two calculated measure(Count of Employees and percentage Bracket) and display the aggregated results of one measure against the other. This basically translates to something like(pseudo code below) -

SELECT distinct values of the measure [percentage bracket] on rows,

Corresponds counts on columns

from [YourCube]

While this is possible in a bit complicated SQL, it is not possible in MDX. The main reasons is that what you want here is a two-step calculation. The first step would calculate the bracket in which employee would fall and in the next step, use this result to get the counts per bracket. It might seem that this can be achieved using calculated members, but you have to keep in mind that measure values are not persistent. In absence of a scope, a measure is aggregated over the entire hierarchy. There is no way to deduce the unique values a measure can contain unless the MDX is run.

In SQL, this can be achieved using subquery or join because joins could be made at the aggregated values(and they are treated persistent), but that feature does not exist in MDX.

What IS possible is to have 5 separate calculated members which would hold counts per bracket. As this would be a one step calculation, it would be within the powers of MDX.

It would be something one the lines of below -

With Member [Measures].[EmployeeWisePercent] AS
[Measures].[Hours] / ([Employee].[Employee].[All], [Measures].[Hours]) * 100

Member [Measures].[TotalHours] AS
([Employee].[Employee].[All], [Measures].[Hours])

MEMBER MEASURES.[0-20%] AS
COUNT(FILTER(EXISTING [Employee].[Employee].CHILDREN, [Measures].EmployeeWisePercent >=0 AND [Measures].EmployeeWisePercent<20))

MEMBER [20-40%] AS
COUNT(FILTER(EXISTING [Employee].[Employee].CHILDREN, [Measures].EmployeeWisePercent >=20 AND [Measures].EmployeeWisePercent<40))

MEMBER [40-60%] AS
COUNT(FILTER(EXISTING [Employee].[Employee].CHILDREN, [Measures].EmployeeWisePercent >=40 AND [Measures].EmployeeWisePercent<60))

MEMBER [60-80%] AS
COUNT(FILTER(EXISTING [Employee].[Employee].CHILDREN, [Measures].EmployeeWisePercent >=60 AND [Measures].EmployeeWisePercent<80))

MEMBER [80-100%] AS
COUNT(FILTER(EXISTING [Employee].[Employee].CHILDREN, [Measures].EmployeeWisePercent >=80 AND [Measures].EmployeeWisePercent<=100))

 SELECT NON EMPTY 
 { MEASURES.[0-20%],  MEASURES.[20-40%],  MEASURES.[40-60%],  MEASURES.[60-80%],  MEASURES.[80-100%] } ON COLUMNS

 FROM
    ( SELECT ( STRTOSET("[Date].[Calendar].[All]", CONSTRAINED) ) ON COLUMNS FROM 
    ( SELECT ( STRTOSET("[Project].[Project Name].&[Self Study]", CONSTRAINED) ) ON COLUMNS FROM 
    [TimeSheetHours Cube])) 

  WHERE ( IIF( STRTOSET("[Project].[Project Name].&[Self Study]", CONSTRAINED).Count = 1,
   STRTOSET("[Project].[Project Name].&[Self Study]", CONSTRAINED), 
   [Project].[Project Name].currentmember ), 
   IIF( STRTOSET("[Date].[Calendar].[All]", CONSTRAINED).Count = 1, 
   STRTOSET("[Date].[Calendar].[All]", CONSTRAINED), [Date].[Calendar].currentmember ) )
   CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

Answer 2

I'm wondering how this tweek to Sourav's script affects the results: 1. Are they still valid? 2. Does the below run quicker?

It is using a twist to the COUNT(FILTER construct. This was blogged about by MoshaP here: http://sqlblog.com/blogs/mosha/archive/2007/11/22/optimizing-count-filter-expressions-in-mdx.aspx

With Member [Measures].[EmployeeWisePercent] AS
[Measures].[Hours] / ([Employee].[Employee].[All], [Measures].[Hours]) * 100

Member [Measures].[TotalHours] AS
([Employee].[Employee].[All], [Measures].[Hours])

MEMBER [Measures].[0-20%] AS 
 SUM(
   EXISTING [Employee].[Employee].CHILDREN, 
   IIF(
     [Measures].[EmployeeWisePercent]>=0 
     AND 
     [Measures].[EmployeeWisePercent]<20
    ,1
    ,NULL)
 )

MEMBER [Measures].[20-40%] AS 
 SUM(
   EXISTING [Employee].[Employee].CHILDREN, 
   IIF(
     [Measures].[EmployeeWisePercent]>=20 
     AND 
     [Measures].[EmployeeWisePercent]<40
    ,1
    ,NULL)
 )

MEMBER [Measures].[40-60%] AS 
 SUM(
   EXISTING [Employee].[Employee].CHILDREN, 
   IIF(
     [Measures].[EmployeeWisePercent]>=40 
     AND 
     [Measures].[EmployeeWisePercent]<60
    ,1
    ,NULL)
 )

MEMBER [Measures].[60-80%] AS 
 SUM(
   EXISTING [Employee].[Employee].CHILDREN, 
   IIF(
     [Measures].[EmployeeWisePercent]>=60 
     AND 
     [Measures].[EmployeeWisePercent]<80
    ,1
    ,NULL)
 )

MEMBER [Measures].[80-100%] AS 
 SUM(
   EXISTING [Employee].[Employee].CHILDREN, 
   IIF(
     [Measures].[EmployeeWisePercent]>=80 
     AND 
     [Measures].[EmployeeWisePercent]<=100
    ,1
    ,NULL)
 )

 SELECT 
 NON EMPTY 
 { 
    MEASURES.[0-20%]
 ,  MEASURES.[20-40%]
 ,  MEASURES.[40-60%]
 ,  MEASURES.[60-80%]
 ,  MEASURES.[80-100%] 
 } ON COLUMNS

 FROM
    ( 
     SELECT ( STRTOSET("[Date].[Calendar].[All]", CONSTRAINED) ) ON COLUMNS 
     FROM 
        ( 
          SELECT 
             ( STRTOSET("[Project].[Project Name].&[Self Study]", CONSTRAINED) ) ON COLUMNS 
          FROM [TimeSheetHours Cube]
        )
     ) 

  WHERE 
     ( IIF( STRTOSET("[Project].[Project Name].&[Self Study]", CONSTRAINED).Count = 1,
   STRTOSET("[Project].[Project Name].&[Self Study]", CONSTRAINED), 
   [Project].[Project Name].currentmember ), 
   IIF( STRTOSET("[Date].[Calendar].[All]", CONSTRAINED).Count = 1, 
   STRTOSET("[Date].[Calendar].[All]", CONSTRAINED), [Date].[Calendar].currentmember ) )
   CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS