Matrix: static column which calculates from dynamic column

Hello,
I have a matrix for Turnover that looks like this:
Rows: Department
Columns: Status
Data: Count of employees
It runs beautifully to look like this:
Department Active Terminated
________________________________
Cleaners 6 2
Maintenance 5 1
Painters 4 0
TOTAL 15 3
I would like to add another column to take the # of Active employees
and divide it by the number of Terminated Employees to look like this:
Department Active Terminated Turnover
___________________________________________
Cleaners 6 2 33%
Maintenance 5 1 20%
Painters 4 0 0%
TOTAL 15 3 20%
Does anyone know how I can do that, if possible?
Thank you!
MichelleTo add a new column right click on the last column of the table header
and select *Insert column to the right*. Next, right click on the new
cell, select *Expression*, in the text box on the right enter:
=Fields!Active.Value / Fields.Terminated.Value,
and click ok. Next, right click on the new cell again and this time
select *Properties*, in the Format section on the right select
percentage, click ok and you are done!|||Hi Patrick,
Thank you for your quick reply, unfortunately this doesn't work in my
situation. I cannot have an expression of "=Fields!Active.Value /
Fields.Terminated.Value" because these fields do not exist. There is 1
field called Status which can be either Active or Inactive. Status is
a dynamic colunm on my matrix.
Thank you,
Michelle|||Then reference the value of the textbox,
i.e. =ReportItems!active.Value / ReportItems!terminated.Value|||Add a column to the right, then type = (Fields!Terminated.Value) /
(Fields!Active.Value) in the textbox expression. You may have to format the
percentage by right click the mouse and Select "Properties" and choose
percentage.
or = sum(Fields!Terminated.Value) / sum(Fields!Active.Value).
If you in matrix report, then the formula won't work. you have to
use difference function like the "InScope function" More information is
available at
http://msdn.microsoft.com/library/en-us/RSCREATE/htm/rcr_creating_expressions_v1_0jmt.asp
Good luck!
--
This posting is provided "AS IS" with no warranties, and confers no rights
"Michelle@.bwalk.com" wrote:
> Hello,
> I have a matrix for Turnover that looks like this:
>
> Rows: Department
> Columns: Status
> Data: Count of employees
>
> It runs beautifully to look like this:
>
> Department Active Terminated
> ________________________________
> Cleaners 6 2
> Maintenance 5 1
> Painters 4 0
> TOTAL 15 3
>
> I would like to add another column to take the # of Active employees
> and divide it by the number of Terminated Employees to look like this:
>
> Department Active Terminated Turnover
> ___________________________________________
> Cleaners 6 2 33%
> Maintenance 5 1 20%
> Painters 4 0 0%
> TOTAL 15 3 20%
>
> Does anyone know how I can do that, if possible?
>
> Thank you!
> Michelle
>

Matrix - Problem with Rounding in Subtotal Column

Hi,

I have a matrix report that calculates data and provides a subtotal at the end. The calculated value is displayed to one decimal place.

My problem is that when the subtotal appears, it appears to one decimal place also, and although it is the correct value, it is not actually a sum of the values above it.

Here's what I mean:

Client Formatted Data Unformatted Data A 1.6 1.578947368 B 0.8 0.789473684 C 0.8 0.789473684 D -0.8 -0.789473684 E 1.6 1.578947368 Total 3.9 3.947368421

The formatted data row is what I have on my matrix report - the Users are looking at this and complaining that the Total row = 3.9, but the sum of the values above it is 4.0. If you look at the raw data I have included (this is not shown on the report), 3.9 is actually the correct value, but you can understand where they are coming from when they can't see this.

It seems that the Subtotal is aggregated before any formatting applies, so its not actually a subtotal of the visible data in the cells in its group, but a subtotal of the raw data in the cells in its group.

Does anyone know how to solve this?

Thanks !

hi

if u used any formating for the fields like format(a,0),format(b,0) the same you need to use when u calculate total like format(a,0)+format(b,0) etc .i hope if u use this you will get the correct total.

|||

I think your problem is that you're doing the formatting after Aggregation.. for example you're probably using something like Format(Sum(<FieldName>)). Your problem in that case will be resolved if you do it before Aggregation that is, something like Sum(Format(<FieldName>))

-Aayush

|||If I use Sum(Format(<FieldName>)) I get an error saying that "the expression uses an aggregate function on data that is not numeric", even if I format it as a number.|||

That is because the Format functions convert data to String type. So you can probably use a conversion function after Formatting, that is something like

Sum(CDbl(Format(<FieldName>))). I know its a very crude kind of fix but I guess it doesn't matter as long as it works. There might be a better way but at least I'm not aware of one.

-Aayush

|||This worked, thanks !

Matrix - Problem with Rounding in Subtotal Column

Hi,

I have a matrix report that calculates data and provides a subtotal at the end. The calculated value is displayed to one decimal place.

My problem is that when the subtotal appears, it appears to one decimal place also, and although it is the correct value, it is not actually a sum of the values above it.

Here's what I mean:

Client Formatted Data Unformatted Data A 1.6 1.578947368 B 0.8 0.789473684 C 0.8 0.789473684 D -0.8 -0.789473684 E 1.6 1.578947368 Total 3.9 3.947368421

The formatted data row is what I have on my matrix report - the Users are looking at this and complaining that the Total row = 3.9, but the sum of the values above it is 4.0. If you look at the raw data I have included (this is not shown on the report), 3.9 is actually the correct value, but you can understand where they are coming from when they can't see this.

It seems that the Subtotal is aggregated before any formatting applies, so its not actually a subtotal of the visible data in the cells in its group, but a subtotal of the raw data in the cells in its group.

Does anyone know how to solve this?

Thanks !

hi

if u used any formating for the fields like format(a,0),format(b,0) the same you need to use when u calculate total like format(a,0)+format(b,0) etc .i hope if u use this you will get the correct total.

|||

I think your problem is that you're doing the formatting after Aggregation.. for example you're probably using something like Format(Sum(<FieldName>)). Your problem in that case will be resolved if you do it before Aggregation that is, something like Sum(Format(<FieldName>))

-Aayush

|||If I use Sum(Format(<FieldName>)) I get an error saying that "the expression uses an aggregate function on data that is not numeric", even if I format it as a number.|||

That is because the Format functions convert data to String type. So you can probably use a conversion function after Formatting, that is something like

Sum(CDbl(Format(<FieldName>))). I know its a very crude kind of fix but I guess it doesn't matter as long as it works. There might be a better way but at least I'm not aware of one.

-Aayush

|||This worked, thanks !