Matrix Subtotals/Cells

I've seen in a fex posts on this forum that it's at this moment not yet possible to create a dynamic matrix where for each cell i can calculate his row-percentage, his column-percentage. His total percentage can be done.

A way i could still do this is to create another matrix that only holds the totals, but how can i reference a cell-value of a matrix from another field ? (If this can be done).

KG.

Harry.

Another approch i'm trying is make some hardcoded fields for the total of each row and column. In this case it is possible as i know that there will always be 7 rows (1week) and 5 performances ...

When referencing a fields as =Sum(Fields("DW_" + CStr(Fields!CompD_DayOfFilmWeek.Value)).Value, "SdwOlap") i'm getting it right.

But when i place tis formula in a cell of a matrix i'm getting in every column the grand total and not the total for that row, although, in the row below i entered the formula ="DW_" + CStr(Fields!CompD_DayOfFilmWeek.Value), and there i'm nicely seeing DW_1 to DW_7 depending on the right row.

Strange behaviour that i can't explain .....

|||

In the grouping scope parameter of the aggregate, you are specifying a value, "SdwOlap". What is this refering to? Is this the name of the dataset or matrix? If it is, then this explains why the totals are actually grand totals and not just the totals for the row. The aggregate is aggregating the data of this field for the entire dataset. Try setting this to the scope of the row grouping, or omit it altogether.

Ian

sql

Matrix calculation for percent

How do I write expression to calculate a value to show percentage of the
subtotal value in a Matrix?
I have already tried =Fields!name.value/sum(Fields!name.value) and this
does not work
Example:
Matrix has Coulmn group Matrix_col and a Row group Matrix_Row and I would
like to show the percentage of the subtotal for Matrix_Row in a column next
to Matrix Col.
Matrix_Col Percent
Matrix_Row
value 1 %25
value 2 %50
value 1 %25
Total 4Create a column, and set your expression to Fields!Name.Value /
First(Fields!Amount.Value, "MatrixColumnGroupName") - assuming that your
first row is a total.
If it's not, you might be able to use SUM if you add the Matrix column group
name = Fields!Name.Value / SUM(Fields!Amount.Value, "MatrixColumnGroupName")
Kaisa M. Lindahl
"Curtis" <gilbertson.curtis@.leg.wa.gov> wrote in message
news:uB5u4igFGHA.1028@.TK2MSFTNGP11.phx.gbl...
> How do I write expression to calculate a value to show percentage of the
> subtotal value in a Matrix?
> I have already tried =Fields!name.value/sum(Fields!name.value) and this
> does not work
> Example:
> Matrix has Coulmn group Matrix_col and a Row group Matrix_Row and I would
> like to show the percentage of the subtotal for Matrix_Row in a column
> next
> to Matrix Col.
> Matrix_Col Percent
> Matrix_Row
> value 1 %25
> value 2 %50
> value 1 %25
>
> Total 4
>|||Thank you very much for your answer, I've been looking for a solution for
months.
"Kaisa M. Lindahl" wrote:
> Create a column, and set your expression to Fields!Name.Value /
> First(Fields!Amount.Value, "MatrixColumnGroupName") - assuming that your
> first row is a total.
> If it's not, you might be able to use SUM if you add the Matrix column group
> name = Fields!Name.Value / SUM(Fields!Amount.Value, "MatrixColumnGroupName")
> Kaisa M. Lindahl
> "Curtis" <gilbertson.curtis@.leg.wa.gov> wrote in message
> news:uB5u4igFGHA.1028@.TK2MSFTNGP11.phx.gbl...
> > How do I write expression to calculate a value to show percentage of the
> > subtotal value in a Matrix?
> >
> > I have already tried =Fields!name.value/sum(Fields!name.value) and this
> > does not work
> >
> > Example:
> > Matrix has Coulmn group Matrix_col and a Row group Matrix_Row and I would
> > like to show the percentage of the subtotal for Matrix_Row in a column
> > next
> > to Matrix Col.
> >
> > Matrix_Col Percent
> >
> > Matrix_Row
> >
> > value 1 %25
> >
> > value 2 %50
> >
> > value 1 %25
> >
> >
> >
> > Total 4
> >
> >
>
>

Matrix - How to calculate % of row?

Hi,

I wish to create a matrix with multiple rows in the main data cell and a subtotal at the end of the row. The first row in my matrix main cell is just a count of records, whereas the 2nd row is a % of the value in the 1st row compared to the total of that row. I have 5 columns in the matrix as below (ignore rounding issues):

Status

A

B

C

D

E

Total

01/01/2007

Number

9

32

3

13

0

57

% of Total

15%

56%

5%

24%

0.00%

100.00%

Can someone advise the best way to calculate the % cells in this example?

Thanks

Can you do it in the dataset query and then just set that row equal to the result of the query? That seems to work the best for me.|||

Hi,

Unfortunately in this instance I need to find a solution that can be implemented within the report.

Thanks for the reply

|||

Don't know if this is any use but whilst trying to find out how to add totals to my first matrix report (still looking...) I came across

http://blogs.technet.com/mat_stephen/archive/2005/05/26/405407.aspx

Steven

|||

The reply to the comment on Matt Stephen's blog certainly helps:

"For matrix reports though, percentages can be calculated on the total for the report, but not for group totals on the column total or row total, since the column or row group cannot be referenced within the dataset. "

Suggesting that it is not possible, so the data will have to be calculated at source.

Thanks Steven.

|||Or you could just use the workaround of calculating the total and percentages in the source query.|||

If ur using the concept of SSAS i.e. if ur datasource is an analysis services data base then i can help you out.

By using the calculate in SSAS

calculate a new column with some name as %of total and specify the expression as

(A/Total,Number) or (A/Total,Number.All) {I dont remember correctly)

Where in your case if A is the column name and

Total ur entire total

and the Number is the "row name"

What this thing does is, it extends the scope of the expression (A/Total) to the entire row so you get the correct value.

Hope this is ur requirement .

Thanks

|||

Thanks for the response Mr.rajz

I had to change the query at source as I was not using SSAS.

Matrix - Calculate percentage column

How do I write expression to calculate a value to show percentage of the
subtotal value in a Matrix? example:
Matrix has Coulmn group Matrix_col and a Row group Matrix_Row and I would
like to show the percentage of the subtotal for Matrix_Row in a column next
to Matrix Col.
Matrix_Col Percent
Matrix_Row
value 1 %25
value 2 %50
value 1 %25
Total 4I would try this in the third column
Field!Name.Value/ SUM(Field!Name.Value)
I'd also give this third column a percentage format
Med bouchenafa
"Curtis" <gilbertson.curtis@.leg.wa.gov> a écrit dans le message de news:
%23tJnBVXFGHA.2320@.TK2MSFTNGP11.phx.gbl...
> How do I write expression to calculate a value to show percentage of the
> subtotal value in a Matrix? example:
> Matrix has Coulmn group Matrix_col and a Row group Matrix_Row and I would
> like to show the percentage of the subtotal for Matrix_Row in a column
> next to Matrix Col.
>
> Matrix_Col Percent
> Matrix_Row
> value 1 %25
> value 2 %50
> value 1 %25
>
> Total 4
>|||Med, Thanks for your reply. I have tried this already and it does not work.
I tried it in the third column as well and it still does not work. Any
other ideas?
"Med Bouchenafa" <com.hotmail@.bouchenafa> wrote in message
news:uuhpEWfFGHA.516@.TK2MSFTNGP15.phx.gbl...
>I would try this in the third column
> Field!Name.Value/ SUM(Field!Name.Value)
> I'd also give this third column a percentage format
> Med bouchenafa
>
> "Curtis" <gilbertson.curtis@.leg.wa.gov> a écrit dans le message de news:
> %23tJnBVXFGHA.2320@.TK2MSFTNGP11.phx.gbl...
>> How do I write expression to calculate a value to show percentage of the
>> subtotal value in a Matrix? example:
>> Matrix has Coulmn group Matrix_col and a Row group Matrix_Row and I
>> would like to show the percentage of the subtotal for Matrix_Row in a
>> column next to Matrix Col.
>>
>> Matrix_Col Percent
>> Matrix_Row
>> value 1 %25
>> value 2 %50
>> value 1 %25
>>
>> Total 4
>>
>

Matix: refering to TOT column

I Have a Matrix:

IN OUT TOT
num avg max % num avg max % num avg max %
r1
r2

I want calculate the "%" so: num(tot)/num(in/out)

I added the column tot wiht right-click.... etc
How I can refer to num of TOT?

Thanks

abc_abc

more details
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=673479&SiteID=1