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 Subtotal but not all colums

Hello,

I have this problem that I do not now how to solve it:

I have a report (matrix) where in the lines I have an order status, while in the columns I have the number of orders, the number of lines and the average time of delivery (calculated measure on as 2005 cube).

If put the subtotals the system will add the number of orders, the number of lines but instead of doing the average time of delivery, it will sum all the average of each order status.

Nr. Of Orders Nr. of Lines Average Delivery Time

Open Order 10 20 3.2

Closed Order 15 30 5

Total 25 50 8.2 (it should be, for example, 4.5)

Is there a way in which I can tell the system to not calculate the total for the third column?

If I use excel there is no problem because it will use the server formatting of AS2005 while Reporting Services is not doing it.

Thanks to all!

Andrea

You would need to use the InScope RS function in the matrix cell's expression to determine if the cell is neither in the row group's nor in the column group's scope (i.e. the overall total cell).

Please search this forum for threads related to the InScope function.

-- Robert

|||

Robert, as the subtotal is on the rows, and the subtotal cell appears in a column calculated in the dataset, then surely in this case he does want to be in scope of the column group?

Is there an expression that will help determine what the current column is? That could then be applied to the visibility. Something like for Visibility->Hidden:

= (Not InScope("row_group")) And (Fields!Measure_Name.Value = "Average Delivery Time")

|||

I am playing a little bit with the inscope functions but it doesn't seem to work. How can I change the scope of the subtotal that is placed automatically by RS?

Basically all my columns are in the row group scope and I just need the subtotals to act differently.

Thanks again for your help!

Matrix Subtotal but not all colums

Hello,

I have this problem that I do not now how to solve it:

I have a report (matrix) where in the lines I have an order status, while in the columns I have the number of orders, the number of lines and the average time of delivery (calculated measure on as 2005 cube).

If put the subtotals the system will add the number of orders, the number of lines but instead of doing the average time of delivery, it will sum all the average of each order status.

Nr. Of Orders Nr. of Lines Average Delivery Time

Open Order 10 20 3.2

Closed Order 15 30 5

Total 25 50 8.2 (it should be, for example, 4.5)

Is there a way in which I can tell the system to not calculate the total for the third column?

If I use excel there is no problem because it will use the server formatting of AS2005 while Reporting Services is not doing it.

Thanks to all!

Andrea

You would need to use the InScope RS function in the matrix cell's expression to determine if the cell is neither in the row group's nor in the column group's scope (i.e. the overall total cell).

Please search this forum for threads related to the InScope function.

-- Robert

|||

Robert, as the subtotal is on the rows, and the subtotal cell appears in a column calculated in the dataset, then surely in this case he does want to be in scope of the column group?

Is there an expression that will help determine what the current column is? That could then be applied to the visibility. Something like for Visibility->Hidden:

= (Not InScope("row_group")) And (Fields!Measure_Name.Value = "Average Delivery Time")

|||

I am playing a little bit with the inscope functions but it doesn't seem to work. How can I change the scope of the subtotal that is placed automatically by RS?

Basically all my columns are in the row group scope and I just need the subtotals to act differently.

Thanks again for your help!

Matrix Report - Add Pct of Total Column

How could I add a percentage of total column to a matrix that looks
like this:
Employment Status Owned Rented Total
Full-Time 10 10 20
Part Time 20 20 40
Totals 30 30 60
I want it to look like:
Employment Status Owned Rented Total Pct of Tot
Full-Time 10 10 20 33.3
Part Time 20 20 40 66.7
Totals 30 30 60 100.0
Any help would be appreciated.
Regards,
Tom OlthoffWrite your expression, format the textox using P or P2
<OTSolutions@.shaw.ca> wrote in message
news:1123180864.018293.197390@.g49g2000cwa.googlegroups.com...
> How could I add a percentage of total column to a matrix that looks
> like this:
> Employment Status Owned Rented Total
> Full-Time 10 10 20
> Part Time 20 20 40
> Totals 30 30 60
> I want it to look like:
> Employment Status Owned Rented Total Pct of Tot
> Full-Time 10 10 20 33.3
> Part Time 20 20 40 66.7
> Totals 30 30 60 100.0
> Any help would be appreciated.
> Regards,
> Tom Olthoff
>|||Ok, once I have the expression how do I add the column to the end of
the matrix? There is no Add Column when I right click the last column
on the matrix. Just Add Column Group, which does not do what I need it
to do.
Regards,
Tom

Master..sysdatabases.status column

Hi
sysdatabases.status column, is given some fixed values like:
WHEN status = 512 THEN 'offline'
WHEN status = 1024 THEN 'read only'
WHEN status = 2048 THEN 'dbo use only' and so on...
However when I put on the of the user databases in dbo use only, and I query
the
select * from master..sysdatabases
Then the value for status column for that database is not 2048, but it is
4112.
How do I interpret this code'
I am trying to write some job based on the status of the database.Actually, that's a bitmask -- multiple bits can be on at the same time.
So it's really:
WHEN (status & 512) <> 0 THEN 'offline'
WHEN (status & 1024) <> 0 THEN 'read only'
... etc
Here is the complete list (cut from BOL):
1 = autoclose; set with ALTER DATABASE.
4 = select into/bulkcopy; set with ALTER DATABASE RECOVERY.
8 = trunc. log on chkpt; set with ALTER DATABASE RECOVERY.
16 = torn page detection, set with ALTER DATABASE.
32 = loading.
64 = pre recovery.
128 = recovering.
256 = not recovered.
512 = offline; set with ALTER DATABASE.
1024 = read only; set with ALTER DATABASE.
2048 = dbo use only; set with ALTER DATABASE RESTRICTED_USER.
4096 = single user; set with ALTER DATABASE.
32768 = emergency mode.
4194304 = autoshrink , set with ALTER DATABASE.
1073741824 = cleanly shutdown.
4112 = 4096 + 16 = torn page detection and single user ... not dbo use only
according to this?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Pari" <Pari@.discussions.microsoft.com> wrote in message
news:C8D00A32-ADE7-4601-8297-648801AB1727@.microsoft.com...
> Hi
> sysdatabases.status column, is given some fixed values like:
> WHEN status = 512 THEN 'offline'
> WHEN status = 1024 THEN 'read only'
> WHEN status = 2048 THEN 'dbo use only' and so on...
> However when I put on the of the user databases in dbo use only, and I
query
> the
> select * from master..sysdatabases
> Then the value for status column for that database is not 2048, but it is
> 4112.
> How do I interpret this code'
> I am trying to write some job based on the status of the database.
>
>

Master..sysdatabases.status column

Hi
sysdatabases.status column, is given some fixed values like:
WHEN status = 512 THEN 'offline'
WHEN status = 1024 THEN 'read only'
WHEN status = 2048 THEN 'dbo use only'and so on...
However when I put on the of the user databases in dbo use only, and I query
the
select * from master..sysdatabases
Then the value for status column for that database is not 2048, but it is
4112.
How do I interpret this code?
I am trying to write some job based on the status of the database.
Actually, that's a bitmask -- multiple bits can be on at the same time.
So it's really:
WHEN (status & 512) <> 0 THEN 'offline'
WHEN (status & 1024) <> 0 THEN 'read only'
... etc
Here is the complete list (cut from BOL):
1 = autoclose; set with ALTER DATABASE.
4 = select into/bulkcopy; set with ALTER DATABASE RECOVERY.
8 = trunc. log on chkpt; set with ALTER DATABASE RECOVERY.
16 = torn page detection, set with ALTER DATABASE.
32 = loading.
64 = pre recovery.
128 = recovering.
256 = not recovered.
512 = offline; set with ALTER DATABASE.
1024 = read only; set with ALTER DATABASE.
2048 = dbo use only; set with ALTER DATABASE RESTRICTED_USER.
4096 = single user; set with ALTER DATABASE.
32768 = emergency mode.
4194304 = autoshrink , set with ALTER DATABASE.
1073741824 = cleanly shutdown.
4112 = 4096 + 16 = torn page detection and single user ... not dbo use only
according to this?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Pari" <Pari@.discussions.microsoft.com> wrote in message
news:C8D00A32-ADE7-4601-8297-648801AB1727@.microsoft.com...
> Hi
> sysdatabases.status column, is given some fixed values like:
> WHEN status = 512 THEN 'offline'
> WHEN status = 1024 THEN 'read only'
> WHEN status = 2048 THEN 'dbo use only' and so on...
> However when I put on the of the user databases in dbo use only, and I
query
> the
> select * from master..sysdatabases
> Then the value for status column for that database is not 2048, but it is
> 4112.
> How do I interpret this code?
> I am trying to write some job based on the status of the database.
>
>

Master..sysdatabases.status column

Hi
sysdatabases.status column, is given some fixed values like:
WHEN status = 512 THEN 'offline'
WHEN status = 1024 THEN 'read only'
WHEN status = 2048 THEN 'dbo use only' and so on...
However when I put on the of the user databases in dbo use only, and I query
the
select * from master..sysdatabases
Then the value for status column for that database is not 2048, but it is
4112.
How do I interpret this code'
I am trying to write some job based on the status of the database.Actually, that's a bitmask -- multiple bits can be on at the same time.
So it's really:
WHEN (status & 512) <> 0 THEN 'offline'
WHEN (status & 1024) <> 0 THEN 'read only'
... etc
Here is the complete list (cut from BOL):
1 = autoclose; set with ALTER DATABASE.
4 = select into/bulkcopy; set with ALTER DATABASE RECOVERY.
8 = trunc. log on chkpt; set with ALTER DATABASE RECOVERY.
16 = torn page detection, set with ALTER DATABASE.
32 = loading.
64 = pre recovery.
128 = recovering.
256 = not recovered.
512 = offline; set with ALTER DATABASE.
1024 = read only; set with ALTER DATABASE.
2048 = dbo use only; set with ALTER DATABASE RESTRICTED_USER.
4096 = single user; set with ALTER DATABASE.
32768 = emergency mode.
4194304 = autoshrink , set with ALTER DATABASE.
1073741824 = cleanly shutdown.
4112 = 4096 + 16 = torn page detection and single user ... not dbo use only
according to this?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Pari" <Pari@.discussions.microsoft.com> wrote in message
news:C8D00A32-ADE7-4601-8297-648801AB1727@.microsoft.com...
> Hi
> sysdatabases.status column, is given some fixed values like:
> WHEN status = 512 THEN 'offline'
> WHEN status = 1024 THEN 'read only'
> WHEN status = 2048 THEN 'dbo use only' and so on...
> However when I put on the of the user databases in dbo use only, and I
query
> the
> select * from master..sysdatabases
> Then the value for status column for that database is not 2048, but it is
> 4112.
> How do I interpret this code'
> I am trying to write some job based on the status of the database.
>
>