Need urgent MDX help on using Aggreate funtion in SSAS 2005

Hi,

I need a functionality similar to Named Member in ProClarity in SSAS 2005.

In Proclarity I can define a named memeber by selecting a few levels of a dimension. The underlying MDX uses the Aggregate function something like this

Aggregate({ [Channel].[Channel].[CON - Contractor], [Channel].[Channel].[DIS - Distributor], [Channel].[Channel].[END - End-User], [Channel].[Channel].[GRP - Group], [Channel].[Channel].[OEM - OEM], [Channel].[Channel].[PLA - Private Label], [Channel].[Channel].[SER - Service Provider], [Channel].[Channel].[SYS - System builder] })

Once the named member is selected the calculated measures show the aggreated (sum) results based on the named member and the underlying selected levels in the aggreate function.

How can I do something like this in SSAS 2005. I dont want a named set. And I tired created a calculated member (which i knew will not work but still gave a shot)

Aggregate({ [Channel].[Channel].[CON - Contractor], [Channel].[Channel].[DIS - Distributor], [Channel].[Channel].[END - End-User], [Channel].[Channel].[GRP - Group], [Channel].[Channel].[OEM - OEM], [Channel].[Channel].[PLA - Private Label], [Channel].[Channel].[SER - Service Provider], [Channel].[Channel].[SYS - System builder] })

I also tried specifying the measure in the second parameter

Aggregate({ [Channel].[Channel].[CON - Contractor], [Channel].[Channel].[DIS - Distributor], [Channel].[Channel].[END - End-User], [Channel].[Channel].[GRP - Group], [Channel].[Channel].[OEM - OEM], [Channel].[Channel].[PLA - Private Label], [Channel].[Channel].[SER - Service Provider], [Channel].[Channel].[SYS - System builder] }

, [Measures].[Orders Received Local])

Is it possible to use the aggreate function dynamically as its used in ProClarity?

Thanks in advance for help

The aggregate function should work fine, can you tell us exactly what error you are getting. One thing about the aggregate function is that you would need to create the calculated member on a dimension other than the measures dimension.

Something like the following should report on a dynamically created aggregate member:

Code Snippet

WITH

MEMBER [Channel].[Channel].[AggTest]

as Aggregate({

[Channel].[Channel].[CON - Contractor]

, [Channel].[Channel].[DIS - Distributor]

, [Channel].[Channel].[END - End-User]

, [Channel].[Channel].[GRP - Group]

, [Channel].[Channel].[OEM - OEM]

, [Channel].[Channel].[PLA - Private Label]

, [Channel].[Channel].[SER - Service Provider]

, [Channel].[Channel].[SYS - System builder]

})

SELECT

{[Channel].[Channel].[AggTest]} ON COLUMNS

FROM <Cube>

|||Thanks. yes it worked this way. i made the mistake of creating it on the measures dimension. after changing the dimension to Channel it works fine.