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.

Need urgent help on Aggregate Function

Hi,

I need to do something similar to Proclarity in SSAS 2005. In Proclarity i can create a named member by selecting a few members from a dimension. The underlying mdx looks 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 i select the named member all the calculated measures reflect the result based on the members selected in the aggregate function.

How can I do something similar in SSAS 2005. I dont want a named set and calculated member does not work if i use the plain mdx as above. But i also tried using

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)

just to try to to see teh behaviour but to no avail. after processing the cube and selecting this meausre shows up empty cells.

How can I solve this problem.

thanks in adavance for helping

Could you explain why "calculated member does not work if i use the plain mdx as above" - what dimension/hiererchy did you create the member on, and what results did you get?|||

Thanks for asking this question. I think i made a mistake by leaving the parent hierarchy as default "Measure" and Parent Member as empty. I have modifed the Parent hirerachy to Channel.Channel and Parent Member to Channel.All Channels. I am processing the cube now and would get back to you soon with the results.

Thanks once again

|||

after i put the correct hierarchy and parent member the mdx works just fine.

Thanks a million for your help.