[SOLVED] Getting a count of users each day in Mondrian MDX

Getting a count of users each day in Mondrian MDX

I'm trying to write a query to give me the total number of users for each customer per day.

Here is what I have so far, which for each customer/day combination is giving the total number of user dimension entries without splitting them up by customer/day.

WITH MEMBER [Measures].[MyUserCount]
  AS COUNT(Descendants([User].CurrentMember, [User].[User Name]), INCLUDEEMPTY)
SELECT
  NON EMPTY CrossJoin([Date].[Date].Members, [Customer].[Customer Name].Members) ON ROWS,
  {[Measures].[MyUserCount]} on COLUMNS
FROM
  [Users]

Solution

The problem with your calculated member is that [User].CurrentMember is set to the All member for every row tuple, and thus the count is the total. What you need is a way for the [Customer].CurrentMember and [Date].CurrentMember to effectively filter the [User] dimension.

You need to use a measure that makes sense, i.e. that will have a non-empty value for meaningful joins of the dimension members that you're interested in.

To find this out, you could start by running a query like this:

SELECT
  NON EMPTY CrossJoin(
    [User].[User Name].Members, 
    [Measures].[Some measuse]
  ) ON COLUMNS,
  NON EMPTY CrossJoin(
    [Date].[Date].Members, 
    [Customer].[Customer Name].Members
  ) ON ROWS
FROM [Project]

You would have selected Some measure adequately. The results of that query will be a lot of empty cells, but in a given row, the columns that do have a value correspond to the Users that are related to a given Customer x Date tuple (on the row). You want to count those columns for every row. COUNT and FILTER are what you need, then the query with the calculated member will be

WITH MEMBER [Measures].[User count] AS
  COUNT(
    FILTER(
      [User].[User Name].Members, 
      NOT ISEMPTY([Measures].[Some measure])
    )
  )
SELECT
  NON EMPTY {[Measures].[User count]} ON COLUMNS,
  NON EMPTY CrossJoin(
    [Date].[Date].Members, 
    [Customer].[Customer Name].Members
  ) ON ROWS
FROM [Users]

I am assuming a fair bit here, but with some experimentation you should be able to work it out.