Sum of all selected elements

[planetzone]

Hello,

i want to get a sum of all selected elements of a dimension in my analysis.

It should be displayed like the "All" element of a dimension, which shows the sum of all elements, but it should consider my restrictions in the dimension. 

Basically I want the sum of all displayed Measures in a column.

Is something like that possible with an MDX statement?

 

Thanks.

Regards

Sebastian

 

Post Edited by planetzone at 09/01/2010 11:18

[planetzone]

UPDATE:
I found the MDX function 'VisualTotals' (msdn.microsoft.com/en-us/library/ms145527.aspx).

This is my MDX example that I tried:

select
{[Measures].[buchungsbetrag]} ON COLUMNS,
VisualTotals({ [Dim_Konto.Konto].[All],
  [Dim_Konto.Konto].[All].[63110],
  [Dim_Konto.Konto].[All].[63120]},"Subtotal")
  ON ROWS
 
from [Kontencube]
WHERE
[Dim_Zeit_Usage.Zeit].[All].[2010]

With this I got the correct totals.
When I executed the MDX statement in the Jasper workbench, I got the subtotal value, but not the "Subtotal" text left of it.
The I tried it with a Jasper Analysis view. There, the value and the text were displayed, but when I tried to add another member something weird happend.
The Subtotal became a member on the same level as the "All" Element:

select
{[Measures].[buchungsbetrag]} ON COLUMNS,
  {[Dim_Konto.Konto].[subtotal],
  [Dim_Konto.Konto].[All].[01500],
  [Dim_Konto.Konto].[All].[63110],
  [Dim_Konto.Konto].[All].[63120]}
  ON ROWS
from [Kontencube]
where [Dim_Zeit_Usage.Zeit].[All].[2010]

The subtotal is still displayed, but only with the two members of the first MDX statement. When I try to edit the "Subtotal" Element I get this error:

com.tonbeller.jpivot.olap.model.OlapException: mondrian.olap.MondrianException: Mondrian Error:Internal error: Error while executing query [select {[Measures].[buchungsbetrag]} ON COLUMNS,
  Hierarchize(Union({[Dim_Konto.Konto].[subtotal], [Dim_Konto.Konto].[All].[01500], [Dim_Konto.Konto].[All].[63110], [Dim_Konto.Konto].[All].[63120]}, [Dim_Konto.Konto].[subtotal].Children)) ON ROWS
from [Kontencube]
where [Dim_Zeit_Usage.Zeit].[All].[2010]


Has anybody of you the same problem or does know a solution?

Post Edited by planetzone at 09/10/2010 13:22