Hi,

I would like to write in MDX the following SQL query:

SELECT Left(DTA.a,3) AS Expr1, Sum(DTA.M)
FROM DTA
GROUP BY Left(DTA.a,3);

in order to aggregate on new column Left(DTA.a,3).


The best method i've found in MDX is a query like this:

WITH MEMBER [Measures].[Short name] AS
left( [DTA].[a].CurrentMember.Name, 3 )
SELECT
{ [Measures].[M],[Measures].[Short name]} ON 0,
{ [DTA].[a]} ON 1
FROM Cube

but in this case i cannot aggregate over [Short name].

So how can i write in mdx the initial SQL query in order to aggregate over the new member?


I will appreciate any suggestion!
Thanks in advance