planetzone Posted September 1, 2010 Share Posted September 1, 2010 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.RegardsSebastian Post Edited by planetzone at 09/01/2010 11:18 Link to comment Share on other sites More sharing options...
planetzone Posted September 3, 2010 Author Share Posted September 3, 2010 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 ROWSfrom [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 ROWSfrom [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 Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now