We run proclarity 6.3 and have recently upgraded to SSAS 2008 SP1. When creating a simple query via proclarity with a custom member such as "my total" which proclarity creates as an aggregate of one or more members SSAS 2008 changes the mdx sent by proclarity and substitutes the Aggregate command with the Sum command.
The exact same proclarity command executed against SSAS 2005 SP3 leaves the aggregate intact and returns the correct result for averages and percentages etc.
The work around requires the custom meber to have a solve order other than zero (the default) this then stops SSAS 2008 from removing the aggregate function and replacing with SUM.
We have a number of users with hundreds of custom members, is there any plan to look at this issue with SSAS 2008 to stop users from getting incorrect results when using custom members?
below is example of output from profiler running against SSAS 2005 and also the same in 2008 where the SUM replaces the aggregate function.
WITH
MEMBER [Employee Grade].[KeyReportingGradeGroup].[All].[#<MEMBER!perryja!Total>#] AS 'AGGREGATE( { [Employee Grade].[KeyReportingGradeGroup].&[Partner], [Employee Grade].[KeyReportingGradeGroup].&[Assoc (incl Snr ____)], [Employee Grade].[KeyReportingGradeGroup].&[Asst Sol 3], [Employee Grade].[KeyReportingGradeGroup].&[Asst Sol 1/2], [Employee Grade].[KeyReportingGradeGroup].&[NQ], [Employee Grade].[KeyReportingGradeGroup].&[Trainees], [Employee Grade].[KeyReportingGradeGroup].&[Rebate], [Employee Grade].[KeyReportingGradeGroup].&[Other] } )'
SELECT
{ [Measures].[Avg Effective Rate] } PROPERTIES PARENT_UNIQUE_NAME, MEMBER_TYPE ON COLUMNS ,
{ [Employee Grade].[KeyReportingGradeGroup].[All], [Employee Grade].[KeyReportingGradeGroup].&[Partner], [Employee Grade].[KeyReportingGradeGroup].&[Assoc (incl Snr ____)], [Employee Grade].[KeyReportingGradeGroup].&[Asst Sol 3], [Employee Grade].[KeyReportingGradeGroup].&[Asst Sol 1/2], [Employee Grade].[KeyReportingGradeGroup].&[NQ], [Employee Grade].[KeyReportingGradeGroup].&[Trainees], [Employee Grade].[KeyReportingGradeGroup].&[Rebate], [Employee Grade].[KeyReportingGradeGroup].&[Other], [Employee Grade].[KeyReportingGradeGroup].[All].[#<MEMBER!perryja!Total>#] }
PROPERTIES PARENT_UNIQUE_NAME, MEMBER_TYPE ON ROWS
FROM
[Finance]
WHERE
( [Bill Period].[Fiscal].[Fiscal Period].&[201012] )
--2008 extract
WITH MEMBER [Employee Grade].[KeyReportingGradeGroup].[All].[#<MEMBER!perryja!Total>#] AS 'SUM( { [Employee Grade].[KeyReportingGradeGroup].&[Partner], [Employee Grade].[KeyReportingGradeGroup].&[Assoc (incl Snr ____)], [Employee Grade].[KeyReportingGradeGroup].&[Asst Sol 3], [Employee Grade].[KeyReportingGradeGroup].&[Asst Sol 1/2], [Employee Grade].[KeyReportingGradeGroup].&[NQ], [Employee Grade].[KeyReportingGradeGroup].&[Trainees], [Employee Grade].[KeyReportingGradeGroup].&[Rebate], [Employee Grade].[KeyReportingGradeGroup].&[Other] } )'
SELECT
{ [Measures].[Avg Effective Rate] } PROPERTIES PARENT_UNIQUE_NAME, MEMBER_TYPE ON COLUMNS ,
{ [Employee Grade].[KeyReportingGradeGroup].[All], [Employee Grade].[KeyReportingGradeGroup].&[Partner], [Employee Grade].[KeyReportingGradeGroup].&[Assoc (incl Snr ____)], [Employee Grade].[KeyReportingGradeGroup].&[Asst Sol 3], [Employee Grade].[KeyReportingGradeGroup].&[Asst Sol 1/2], [Employee Grade].[KeyReportingGradeGroup].&[NQ], [Employee Grade].[KeyReportingGradeGroup].&[Trainees], [Employee Grade].[KeyReportingGradeGroup].&[Rebate], [Employee Grade].[KeyReportingGradeGroup].&[Other], [Employee Grade].[KeyReportingGradeGroup].[All].[#<MEMBER!perryja!Total>#] }
PROPERTIES PARENT_UNIQUE_NAME, MEMBER_TYPE ON ROWS
FROM
[Finance]
WHERE
( [Bill Period].[Fiscal].[Fiscal Period].&[201012] )
Thanks
James