РАСЧЕТ MDX ПО ИЗМЕРЕНИЮ

Question

У меня есть запрос MDX, который может дать мне разные EID, регион и страну:

SELECT ([Measures].[Active Tiles Count]) ON 0,
NON EMPTY ([Employee].[Employee Id].Children,[Employee - Business Region].  [Region Code].Children,[Employee - Country].[Country Code].Children) ON 1
FROM [OLAP Pre]
WHERE ( 
{[Employee Statuses].[Status Id].&[1],[Employee Statuses].[Status Id].&[4],[Employee Statuses].[Status Id].&[3]},
{[Business Region].[Abbreviation].&[APJ],[Business Region].[Abbreviation].&[EMEA],[Business Region].[Abbreviation].&[Americas]},
{[Employee Types].[Bits].&[1],[Employee Types].[Bits].&[9],
[Employee Types].[Bits].&[25],[Employee Types].[Bits].&[5],
[Employee Types].[Bits].&[13],[Employee Types].[Bits].&[29]}
,{[Date].[Date].&[2015-03-18T00:00:00]:NULL})

Но я хочу, чтобы COUNT(DISTINCT(employeeEEid)) был сгруппирован по регионам и странам.
Как это сделать?

Answer 1

Создайте DISTINCT COUNT MEASURE, это предопределенные типы агрегации

Answer 2

Попробуйте использовать DISTINCTCOUNT функция

WITH MEMBER Measures.[CountOfDistinctEmployes] AS
DISTINCTCOUNT([Employee].[Employee Id].Children)

SELECT {[Measures].[Active Tiles Count], Measures.[CountOfDistinctEmployes]} ON 0,
NON EMPTY ([Employee].[Employee Id].Children,[Employee - Business Region].  [Region Code].Children,[Employee - Country].[Country Code].Children) ON 1
FROM [OLAP Pre]
WHERE ( 
{[Employee Statuses].[Status Id].&[1],[Employee Statuses].[Status Id].&[4],[Employee Statuses].[Status Id].&[3]},
{[Business Region].[Abbreviation].&[APJ],[Business Region].[Abbreviation].&[EMEA],[Business Region].[Abbreviation].&[Americas]},
{[Employee Types].[Bits].&[1],[Employee Types].[Bits].&[9],
[Employee Types].[Bits].&[25],[Employee Types].[Bits].&[5],
[Employee Types].[Bits].&[13],[Employee Types].[Bits].&[29]}
,{[Date].[Date].&[2015-03-18T00:00:00]:NULL})