[Mondrian] VisualTotals issue.

Printable View

05-11-2011, 07:19 AM
Ati Rosselet

[Mondrian] VisualTotals issue.

Luc/Julian,

Using VisualTotals(), somehow the Time dimension (at least) is not being
handled correctly wrt aggregates.
Visual Totals sums up the values across its elements, but in the case of an
aggregate (e.g. time rollup), it should be summing up the underlying values
rather than the cell values
(if that makes sense).

I've reproduced the problem with 2 Foodmart queries to view
"sales to men for 1997, and by quarter, and a roll-up to show sales to
men cumulated over the year" (I'm not a male chauvinist, this just was the
first that came to mind :))

with
member [Gender].[YTD] as 'AGGREGATE(YTD(),[Gender].[M])', format_string =
"#,###00.0"
select
{[Time].[1997],

[Time].[1997].[Q1],[Time].[1997].[Q2],[Time].[1997].[Q3],[Time].[1997].[Q4]}
ON COLUMNS,
{[Gender].[M],[Gender].[YTD]} ON ROWS
FROM [Sales];

returns (correctly):
Axis #0:
{}
Axis #1:
{[Time].[1997]}
{[Time].[1997].[Q1]}
{[Time].[1997].[Q2]}
{[Time].[1997].[Q3]}
{[Time].[1997].[Q4]}
Axis #2:
{[Gender].[M]}
{[Gender].[YTD]}
Row #0: 135,215
Row #0: 33,381
Row #0: 31,618
Row #0: 33,249
Row #0: 36,967
Row #1: 135,215.0 <--- [Time].[1997] is aggregated correctly
Row #1: 33,381.0
Row #1: 64,999.0
Row #1: 98,248.0
Row #1: 135,215.0

whereas:

with
SET [VTime] AS 'VisualTotals({[Time].[1997],[Time].[1997].Children})'
member [Gender].[YTD] as 'AGGREGATE(YTD(),[Gender].[M])', format_string =
"#,###00.0"
select
{[VTime]} ON COLUMNS,
{[Gender].[M],[Gender].[YTD]} ON ROWS
FROM [Sales];

gives:
Axis #0:
{}
Axis #1:
{[Time].[1997]}
{[Time].[1997].[Q1]}
{[Time].[1997].[Q2]}
{[Time].[1997].[Q3]}
{[Time].[1997].[Q4]}
Axis #2:
{[Gender].[M]}
{[Gender].[YTD]}
Row #0: 135,215
Row #0: 33,381
Row #0: 31,618
Row #0: 33,249
Row #0: 36,967
Row #1: 331,843.0 <<<< this should be 135,125 as far as I can tell
Row #1: 33,381.0
Row #1: 64,999.0
Row #1: 98,248.0
Row #1: 135,215.0

note axis #2 and axis #2 are identical in both cases, so I'm assuming it has
to do with the internal handling of VisualTotals...

any ideas - or should I just create a testcase for this and open a new JIRA
issue?
Cheers
Ati

_______________________________________________
Mondrian mailing list
Mondrian (AT) pentaho (DOT) org
http://lists.pentaho.org/mailman/listinfo/mondrian
05-11-2011, 10:37 AM
Luc Boudreau

Re: [Mondrian] VisualTotals issue.

Ati,

Please log this in Jira. Someone will look into it and determine if this is
a bug.

Thanks for reporting this!

Luc

On Wed, May 11, 2011 at 7:19 AM, Ati Rosselet <ati.rosselet (AT) gmail (DOT) com>wrote:

> Luc/Julian,
>
> Using VisualTotals(), somehow the Time dimension (at least) is not being
> handled correctly wrt aggregates.
> Visual Totals sums up the values across its elements, but in the case of an
> aggregate (e.g. time rollup), it should be summing up the underlying values
> rather than the cell values
> (if that makes sense).
>
> I've reproduced the problem with 2 Foodmart queries to view
> "sales to men for 1997, and by quarter, and a roll-up to show sales to
> men cumulated over the year" (I'm not a male chauvinist, this just was the
> first that came to mind :))
>
> with
> member [Gender].[YTD] as 'AGGREGATE(YTD(),[Gender].[M])', format_string =
> "#,###00.0"
> select
> {[Time].[1997],
>
> [Time].[1997].[Q1],[Time].[1997].[Q2],[Time].[1997].[Q3],[Time].[1997].[Q4]}
> ON COLUMNS,
> {[Gender].[M],[Gender].[YTD]} ON ROWS
> FROM [Sales];
>
> returns (correctly):
> Axis #0:
> {}
> Axis #1:
> {[Time].[1997]}
> {[Time].[1997].[Q1]}
> {[Time].[1997].[Q2]}
> {[Time].[1997].[Q3]}
> {[Time].[1997].[Q4]}
> Axis #2:
> {[Gender].[M]}
> {[Gender].[YTD]}
> Row #0: 135,215
> Row #0: 33,381
> Row #0: 31,618
> Row #0: 33,249
> Row #0: 36,967
> Row #1: 135,215.0 <--- [Time].[1997] is aggregated correctly
> Row #1: 33,381.0
> Row #1: 64,999.0
> Row #1: 98,248.0
> Row #1: 135,215.0
>
> whereas:
>
> with
> SET [VTime] AS 'VisualTotals({[Time].[1997],[Time].[1997].Children})'
> member [Gender].[YTD] as 'AGGREGATE(YTD(),[Gender].[M])', format_string =
> "#,###00.0"
> select
> {[VTime]} ON COLUMNS,
> {[Gender].[M],[Gender].[YTD]} ON ROWS
> FROM [Sales];
>
> gives:
> Axis #0:
> {}
> Axis #1:
> {[Time].[1997]}
> {[Time].[1997].[Q1]}
> {[Time].[1997].[Q2]}
> {[Time].[1997].[Q3]}
> {[Time].[1997].[Q4]}
> Axis #2:
> {[Gender].[M]}
> {[Gender].[YTD]}
> Row #0: 135,215
> Row #0: 33,381
> Row #0: 31,618
> Row #0: 33,249
> Row #0: 36,967
> Row #1: 331,843.0 <<<< this should be 135,125 as far as I can tell
> Row #1: 33,381.0
> Row #1: 64,999.0
> Row #1: 98,248.0
> Row #1: 135,215.0
>
> note axis #2 and axis #2 are identical in both cases, so I'm assuming it
> has to do with the internal handling of VisualTotals...
>
> any ideas - or should I just create a testcase for this and open a new JIRA
> issue?
> Cheers
> Ati
>
> _______________________________________________
> Mondrian mailing list
> Mondrian (AT) pentaho (DOT) org
> http://lists.pentaho.org/mailman/listinfo/mondrian
>
>

_______________________________________________
Mondrian mailing list
Mondrian (AT) pentaho (DOT) org
http://lists.pentaho.org/mailman/listinfo/mondrian
05-11-2011, 01:10 PM
Ati Rosselet

Re: [Mondrian] VisualTotals issue.

JIRA issue opened :
http://jira.pentaho.com/browse/MONDRIAN-939

On Wed, May 11, 2011 at 4:37 PM, Luc Boudreau <lucboudreau (AT) gmail (DOT) com> wrote:

> Ati,
>
> Please log this in Jira. Someone will look into it and determine if this is
> a bug.
>
> Thanks for reporting this!
>
> Luc
>
> On Wed, May 11, 2011 at 7:19 AM, Ati Rosselet <ati.rosselet (AT) gmail (DOT) com>wrote:
>
>> Luc/Julian,
>>
>> Using VisualTotals(), somehow the Time dimension (at least) is not being
>> handled correctly wrt aggregates.
>> Visual Totals sums up the values across its elements, but in the case of
>> an aggregate (e.g. time rollup), it should be summing up the underlying
>> values rather than the cell values
>> (if that makes sense).
>>
>> I've reproduced the problem with 2 Foodmart queries to view
>> "sales to men for 1997, and by quarter, and a roll-up to show sales to
>> men cumulated over the year" (I'm not a male chauvinist, this just was the
>> first that came to mind :))
>>
>> with
>> member [Gender].[YTD] as 'AGGREGATE(YTD(),[Gender].[M])', format_string =
>> "#,###00.0"
>> select
>> {[Time].[1997],
>>
>> [Time].[1997].[Q1],[Time].[1997].[Q2],[Time].[1997].[Q3],[Time].[1997].[Q4]}
>> ON COLUMNS,
>> {[Gender].[M],[Gender].[YTD]} ON ROWS
>> FROM [Sales];
>>
>> returns (correctly):
>> Axis #0:
>> {}
>> Axis #1:
>> {[Time].[1997]}
>> {[Time].[1997].[Q1]}
>> {[Time].[1997].[Q2]}
>> {[Time].[1997].[Q3]}
>> {[Time].[1997].[Q4]}
>> Axis #2:
>> {[Gender].[M]}
>> {[Gender].[YTD]}
>> Row #0: 135,215
>> Row #0: 33,381
>> Row #0: 31,618
>> Row #0: 33,249
>> Row #0: 36,967
>> Row #1: 135,215.0 <--- [Time].[1997] is aggregated correctly
>> Row #1: 33,381.0
>> Row #1: 64,999.0
>> Row #1: 98,248.0
>> Row #1: 135,215.0
>>
>> whereas:
>>
>> with
>> SET [VTime] AS 'VisualTotals({[Time].[1997],[Time].[1997].Children})'
>> member [Gender].[YTD] as 'AGGREGATE(YTD(),[Gender].[M])', format_string =
>> "#,###00.0"
>> select
>> {[VTime]} ON COLUMNS,
>> {[Gender].[M],[Gender].[YTD]} ON ROWS
>> FROM [Sales];
>>
>> gives:
>> Axis #0:
>> {}
>> Axis #1:
>> {[Time].[1997]}
>> {[Time].[1997].[Q1]}
>> {[Time].[1997].[Q2]}
>> {[Time].[1997].[Q3]}
>> {[Time].[1997].[Q4]}
>> Axis #2:
>> {[Gender].[M]}
>> {[Gender].[YTD]}
>> Row #0: 135,215
>> Row #0: 33,381
>> Row #0: 31,618
>> Row #0: 33,249
>> Row #0: 36,967
>> Row #1: 331,843.0 <<<< this should be 135,125 as far as I can tell
>> Row #1: 33,381.0
>> Row #1: 64,999.0
>> Row #1: 98,248.0
>> Row #1: 135,215.0
>>
>> note axis #2 and axis #2 are identical in both cases, so I'm assuming it
>> has to do with the internal handling of VisualTotals...
>>
>> any ideas - or should I just create a testcase for this and open a new
>> JIRA issue?
>> Cheers
>> Ati
>>
>> _______________________________________________
>> Mondrian mailing list
>> Mondrian (AT) pentaho (DOT) org
>> http://lists.pentaho.org/mailman/listinfo/mondrian
>>
>>
>
> _______________________________________________
> Mondrian mailing list
> Mondrian (AT) pentaho (DOT) org
> http://lists.pentaho.org/mailman/listinfo/mondrian
>
>

_______________________________________________
Mondrian mailing list
Mondrian (AT) pentaho (DOT) org
http://lists.pentaho.org/mailman/listinfo/mondrian