Grand Total

I am having a hard time figuring out how to total a column. If I have
January Febuary March
Type1 10 4 3
Type2 4 8 2
Type3 1 9 1
I want a total for each month, NOT each type. I have tried right
clicking on Type and choosing subtotal but that only gives me 10, if I
try to change the expression to a Sum, I get an error that aggregates
can't live in groups.
I have read that I should add a textbox and enter an expression. I
have tried that too and got closer but it seems the only scope that is
actually allowed (dispite what BOL says) is the dataset, which does
give me the total for the whole matix, but I only want each column
totaled. I have tried to replace DataSet1 in the expression with the
name of the Month group, but that errors too.
Am I just blind, this shouldn't be this difficult, right?I have a similar problem but in my case I can't add the columns. My
idea ould be to adjust the MDX statement to get the total row. Just add
the root element to the columns of your query and you should get the
total line.
root element example: [Organisation].[Business Area
Code].CurrentMember.Parent
pjcwik@.gmail.com schrieb:
> I am having a hard time figuring out how to total a column. If I have
> January Febuary March
> Type1 10 4 3
> Type2 4 8 2
> Type3 1 9 1
> I want a total for each month, NOT each type. I have tried right
> clicking on Type and choosing subtotal but that only gives me 10, if I
> try to change the expression to a Sum, I get an error that aggregates
> can't live in groups.
> I have read that I should add a textbox and enter an expression. I
> have tried that too and got closer but it seems the only scope that is
> actually allowed (dispite what BOL says) is the dataset, which does
> give me the total for the whole matix, but I only want each column
> totaled. I have tried to replace DataSet1 in the expression with the
> name of the Month group, but that errors too.
> Am I just blind, this shouldn't be this difficult, right?|||What kind of control are you using - Table or Matrix?
"pjcwik@.gmail.com" wrote:
> I am having a hard time figuring out how to total a column. If I have
> January Febuary March
> Type1 10 4 3
> Type2 4 8 2
> Type3 1 9 1
> I want a total for each month, NOT each type. I have tried right
> clicking on Type and choosing subtotal but that only gives me 10, if I
> try to change the expression to a Sum, I get an error that aggregates
> can't live in groups.
> I have read that I should add a textbox and enter an expression. I
> have tried that too and got closer but it seems the only scope that is
> actually allowed (dispite what BOL says) is the dataset, which does
> give me the total for the whole matix, but I only want each column
> totaled. I have tried to replace DataSet1 in the expression with the
> name of the Month group, but that errors too.
> Am I just blind, this shouldn't be this difficult, right?
>|||OK I got it figured out.
When looking at the matrix report the field names were in the field
spots, but... What needed to happen was to make the field names a SUM.
So instead of just =(Fields!allitmonthclosedcalls.Value) what was
needed was =Sum(Fields!allitmonthclosedcalls.Value) Then the SubTotal
feature worked. The SubTotal can be seen when you right click on the
row group header and choose SubTotal. (I hate it when specifics aren't
given)
Without the SUM in front of the field I only got 10 in the SubTotal
field, but with the =Sum(Fields!allitmonthclosedcalls.Value) then it
added the whole column.
I am assuming that the way it is being read is that the Sum of 10 is
10, the Sum of 4 is 4 and the Sum of 1 is 1. Then the SubTotal is the
Sum of the Sums. A screwy way to get where I needed to be, but it
works.
Hope this helps.
--Pete
pjcwik@.gmail.com wrote:
> I am having a hard time figuring out how to total a column. If I have
> January Febuary March
> Type1 10 4 3
> Type2 4 8 2
> Type3 1 9 1
> I want a total for each month, NOT each type. I have tried right
> clicking on Type and choosing subtotal but that only gives me 10, if I
> try to change the expression to a Sum, I get an error that aggregates
> can't live in groups.
> I have read that I should add a textbox and enter an expression. I
> have tried that too and got closer but it seems the only scope that is
> actually allowed (dispite what BOL says) is the dataset, which does
> give me the total for the whole matix, but I only want each column
> totaled. I have tried to replace DataSet1 in the expression with the
> name of the Month group, but that errors too.
> Am I just blind, this shouldn't be this difficult, right?