Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2005 Forums  Analysis Server and Reporting Services (2005)  aggreate on two dimension and show total

Author  Topic  nko Starting Member 1 Post Posted - 2011-06-10 : 11:04:33 Hi everybody, I have serious difficulty to define a MDX query. The query below show, in a correct way, the "Campagna" belonging to every"LINESPEED", i.e. for each LINESPEED the different Campagna which belong to it are displayed. with member [Measures].[Media KPI1] as 'CASE WHEN ([GRUPPO.name].[All Coils].CurrentMember.Level.Ordinal = 1.0) THEN AVG(Crossjoin({[GRUPPO.name].[Campagna].CurrentMember.Children}, {[Measures].[KPI1]}) WHEN ([GRUPPO.name].[All Coils].CurrentMember.Level.Ordinal = 2.0) THEN Sum({[Measures].[KPI1]}) WHEN ([GRUPPO.name].[All Coils].CurrentMember.Level.Ordinal = 3.0) THEN Sum({[Measures].[KPI1]}) END' select {[Measures].[Media KPI1], [Measures].[Media KPI2]} ON COLUMNS, NON EMPTY {Crossjoin([LINESPEED].Children, [GRUPPO.name].[All Coils].Children)} ON ROWS from [HDGL] With this second query: with member [Measures].[Media KPI1] as 'AVG(Crossjoin({[GRUPPO.name].[Campagna].CurrentMember.Children}, {[Measures].[KPI1]})' select {[Measures].[Media KPI1]} ON COLUMNS, NON EMPTY {[LINESPEED].Children} ON ROWS from [HDGL] I'm able to do a query that shows the LINESPEED values on the row axis, aggregating the KPI value over the "Campagna", by means of AVG function. But how I can do to jointly show the first query, with the last one in order to show the mean of the KPI related to each LINESPEED? What I do in order to aggregate the "Campagna" belonging to each LINESPEED, showing a single value (for every LINESPEED) which is the AVG "Campagna" for the particular LINESPEED? I would to show on the axis 1 firstly the LINESPEED with the mean of the "Campagna" belonging to it, and on the axis 2 the possibility to drill-down in order to show the Campagne that are the values which partecipate to the mean calculation for the LINESPEED. Thanks regards nico