none
sum of a column in matrix

    Question

  • 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?



    Saturday, June 24, 2006 4:10 AM

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]

    Monday, June 26, 2006 2:38 PM

All replies

  • Dear,

    Ur question is very confusing.Will u pls explain in detail.

    from

    sufian

    Saturday, June 24, 2006 8:22 AM
  • 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!!!

    Saturday, June 24, 2006 9:29 AM
  • Dear,

     u can count the number of columns from the sys.syscolumns  table in master database and use that value in ur matrix.

    HTH

    from

    Sufian

    Saturday, June 24, 2006 10:10 AM
  • 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)


    Saturday, June 24, 2006 10:22 AM
  • 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 datacolumn 

    Column 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

    Saturday, June 24, 2006 2:49 PM
  • 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.




    Saturday, June 24, 2006 7:24 PM
  • 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.

     

    Sunday, June 25, 2006 3:36 PM
  • Yicong Shen,

    what is the XXX. i couldn't understand.
    Sunday, June 25, 2006 5:20 PM
  • XXX is whatever number you want to use.

    you can define an expression in the IIF function so that the value in the detail textbox is different when in subtotal. For example, you may display the value divided by three as

    sum(fields!columnA.value)/3

    Monday, June 26, 2006 10:04 AM
  • 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]

    Monday, June 26, 2006 2:38 PM
  • thanks a lot mohd sufian and Yicong Shen

    sufian's solution solved my problem,


    sum(Fields!OrderQty.Value)/Sum(Fields!OrderQty.Value, "MatrixSource")


    this is what i wanted,

    appreciate ur big help, mohd sufian



    Monday, June 26, 2006 4:42 PM
  • It's cool this solution

    Thursday, November 01, 2007 4:08 PM