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.