MDX query

[aravindps]

Hi,
I am new to pentaho analysis services.
I have a query regarding MDX.

Here is the query:
with member [Measures].[test] as 'Count(Distinct({[fact tran MEMBER ID].Children, [fact tran MEMBER ID.fact tran MEMBER ID].[fact_transactions.FK_MEMBER_ID].CurrentMember.Parent}))'
select NON EMPTY Crossjoin(Hierarchize({([Measures].[COUNT OF EVER ACTIVE MEMBERS], [CITY.CITY].[All city]), ([Measures].[test], [CITY.CITY].[All city])}), {[YEAR.YEAR].[All year]}) ON COLUMNS,
NON EMPTY {([ENROLMENT SOURCE.ENROLMENT SOURCE].[All enrolment source], [MEMBER RECENCY.MEMBER RECENCY].[All member recency])} ON ROWS
from [test7]

Here, I am trying to get a distinct member count from a table which has more than 1 entry for 1 member.
Hence I have used distinct and count functions in the query.
But, the the value of the expression "[Measures].[test]" does not change as we drill down different levels in the pentaho_demo_mysql5-1.6.0.GA.863 solutions server(OLAP). The value is static
Foe Ex: If I drill down the year dimension to look at the data for a quarter, the distinct count still shows the entire count, not the distinct count for that quarter.

Does anyone know how to solve the problem??

[jhyde]

Count(Distinct(...)) might be the right construct to use in SQL, but MDX is not SQL. If you want to find the number of distinct members of a given dimension contributing to a given cell, use the distinct-count aggregation function.

Julian

[aravindps]

It works with distinct-count aggregator.
thanks.

[chandu21]

I also have same problem for Distinct Count in Cube
Cube Designer is do not have Distinct-count aggregate function.

Does anybody implementated Distinct Count functionality, Please Let me know.