Answered by:
sum of a column in matrix

hi reporters!
i m using matrix in my project and i want to calculate the (cell_value/sum_of_column). for data cell values we have to use sum or another aggregate function for subtotaling, so for the cell value i m wirting sum(column) / A. how to write A that points to sum of all cells on the column.
x_column y_column z_column
sum(y_column)/A
A?
Question
Answers

Dear,
Sorry for the late reply i tried ur question and got the result below
in the matrix add a column named (total)
suppose the column number having the numerical value names(OrderQty)
OrderQty  Total
2 2/26
6 6/26
8 8/26
10 10/26

26
u have values
(in this column u will see the result ) (2/2+6+8+10+13 and so on, if u need the total add a subtoal in the total column put this forumla
=iif(inscope(Sum(Fields!OrderQty.Value, "MatrixSource")),sum(Fields!OrderQty.Value),sum(Fields!OrderQty.Value)/Sum(Fields!OrderQty.Value, "MatrixSource"))
Hope this is what u need.
Regards
[Sufian]
All replies


ok, u r right, thanks for ur kind warning mohd sufian
i want to calculate the data/sum(column) for each row.
let say i have a number_column, contains 1, 2, 3.
i want to report this using matrix
number_column
1/(1+2+3)
2/(1+2+3)
3/(1+2+3)
it is not Fields!number_column.Value/Sum(Fields!number_column.Value)
because i also use grouping and collapsed view, so for the data cells in matrix i have to use aggregates. Fields!number_column.Value is not an aggregate value.
it's very unusual problem, hope someone can help me!!!


thanks mohs, but this is not i wanted
i don't want the count the number of columns, i want the sum of the datas in a column, i think i gave an wrong example
let say an x_column i have and contains 4, 3, 7.
i want to report
x_column
4/(4+3+7)
3/(4+3+7)
3/(4+3+7)

Dear,
If u are trying to do it in Report in Reporting Services then insert two columns next to the data columns.
for ex:
Column A is the datacolumnColumn A  Column B  Column C
2 9 2/9
3 9 3/9
4 9 4/9
the report will look like this
In column B insert the formula =sum(field1!column A.value)
In Cloumn C insert the formula =fields!ColumnA.value /sum(field!column b.value)
This will show the result in Column C.
I think this is what u need.
HTH
from
sufian

i can't use "fields!ColumnA.value /sum(field!column b.value)" because i m using matrix with grouping and subtotaling, data cells in matrix have to be aggregate. "fields!ColumnA.value" is not an aggregate value. if u try this with rs u will see the warning.

you can use the subtotal feature of matrix to achieve this.
assume you have a matrix1 defined like follows and the row group is matrix1_row:

ColumnA

Fields!Row.value  Sum(Fields!ColumnA.value)
 
Change the definition of the detail textbox to an expression:
=IIF(InScope("matrix1_row"),Sum(Fields!ColumnA.value),sum(Fields!ColumnA.value)/XXX)
Then, enable the subtotal on row. You will find the value in the subtotal what you want.



Dear,
Sorry for the late reply i tried ur question and got the result below
in the matrix add a column named (total)
suppose the column number having the numerical value names(OrderQty)
OrderQty  Total
2 2/26
6 6/26
8 8/26
10 10/26

26
u have values
(in this column u will see the result ) (2/2+6+8+10+13 and so on, if u need the total add a subtoal in the total column put this forumla
=iif(inscope(Sum(Fields!OrderQty.Value, "MatrixSource")),sum(Fields!OrderQty.Value),sum(Fields!OrderQty.Value)/Sum(Fields!OrderQty.Value, "MatrixSource"))
Hope this is what u need.
Regards
[Sufian]

