CODEWITHSUNDEEP
sql-serveraggregate-functionsmdx
Aaron Palmer
Aaron Palmer

Reputation: 8982

In MDX how do I do a group by count(*) for a given dimension?

In T-SQL I would just use a group by clause and a count(*) in the select statement to give me the value I need. But with cubes it's different, because the count isn't just over rows, but dimensional combinations. So I've googled for an answer to no avail. Here is a detailed explanation of my problem:

My original MDX is:

SELECT 
  NON EMPTY 
    {
        [Measures].[Budget]
    } ON COLUMNS
 ,NON EMPTY 
    {
        [Location].[Category - Entity - Facility].[Facility].ALLMEMBERS*
        [Location].[Category - Facility - Unit].[Location].ALLMEMBERS*
        [Calendar].[Day].[Day].ALLMEMBERS
    } ON ROWS
FROM 
(
  SELECT 
    {[Location].[Category - Entity - Facility].[Category].&[3]} ON COLUMNS
  FROM 
  (
    SELECT 
        [Calendar].[Year  -  Quarter -  Month -  Day].[Day].&[2012-01-01T00:00:00]
      : [Calendar].[Year  -  Quarter -  Month -  Day].[Day].&[2012-05-31T00:00:00]
    ON COLUMNS
    FROM [PHI Census]
  )
)

Results look like this:
    Facility 1    Location 1    Day 1    100
    Facility 1    Location 1    Day 2    100
    Facility 1    Location 1    Day 3    100
    Facility 1    Location 1    Day 4    100
    Facility 1    Location 2    Day 1    80
    Facility 1    Location 2    Day 2    80
    Facility 1    Location 2    Day 3    80
    Facility 2    Location 1    Day 1    65
    Facility 2    Location 1    Day 2    65
    Facility 2    Location 1    Day 3    65
    Facility 2    Location 1    Day 4    65
    Facility 2    Location 2    Day 1    73
    Facility 2    Location 2    Day 2    73
    Facility 2    Location 2    Day 3    73

This gives me the [Budget] listed once for each Facility-Location-Day combination. I would like to remove [Calendar].[Day].[Day].ALLMEMBERS from the ON ROWS clause and simply use a calculate member that would return the count of the number of days for each Facility-Location combination along with each row. So basically,

The results would look like this:
    Facility      Location      Budget   DayCount
    Facility 1    Location 1    100      4
    Facility 1    Location 2    80       3
    Facility 2    Location 1    65       4
    Facility 2    Location 2    73       3

Upvotes: 3

Views: 3596

Answers (1)

Benoit
Benoit

Reputation: 1993

The expression of DayCount could be:

MEMBER [Measures].[DayCount] AS Count(NonEmpty([Calendar].[Day].[Day].ALLMEMBERS, [Measures].[Budget]))

Upvotes: 3

Related Questions