We've been trying to use MDX to set up ad hoc groups based
on members. We have successfully used named sets to return
information on individual members of that named set,
however, we need to rollup to a grouping of all of the
members of the named set. Sean Boon responded to one of
us, with the suggestion to use the aggregate function.
One of the problems with this approach is we have to
perform this on calculated members. Also, we've been
requested to do this on four ad hoc groups, so we would
like some way to provide rollups on each of the four
groups. Currently we have defined each of these as a
named set, and then a named set that includes the rest of
the four. While we could redesign our cube for these
groups, we expect to often receive requests for analyses
based on ad hoc groups.

Example:

Select
crossjoin ( {[Report_Period].[All
Report_Period].children},[Measures].[Total Paid],
[Measures].[PMPM],[Measures].[TotalPMPMChng],[PKPY]}) on
columns,

//[Reports] is my named set
crossjoin ({ ([reports]},{[Category].children}) on rows
from Professional.

Hi Howard,

You can use ADDCALCULATEDMEMBERS() MDX function in your set definition so
that the calculated members will be included.

Hope that helps.

Sean


--
Sean

--
Sean Boon
SQL Server BI Product Unit

--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm.

"Howard Brill" <howardnospam (AT) online (DOT) com> wrote

Howard

Do you mind where the aggregation takes place and also if the set is not an
MS defined set, but an optimised set for MS AS? We allow for ad hoc groups
for users.

Howard.Taylor@ domain below
www.o2olap.com


"Howard Brill" <howardnospam (AT) online (DOT) com> wrote

Do you have a U.S.-based reseller?