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: more than 1 column in static rowgroups ?

Hello,

I am working with a matrix report item and would like to display something like that :

DataGroup1
DataGroup2
StaticText11 StaticText12 Data1
StaticText21 StaticText22 Data2
StaticText31 StaticText32 Data3

But I cannot find a simple way to have 2 columns of static text on the left. The only way I've found is inserting a table report item in the matrix cells, but it cannot be exported in Excel.

The only samples I found do'nt have multiple colums in the static rowgroup.

Is it possible ?

Kind regards,
Xavier Miller.

Xavier,

This is a known issue since Ms release Reporting Services 2000, they promised to fix it on a service pack... but it seems that this has not been fixed even in 2005.

Apart from the table inside the matrix... there's another nasty trick, place a rectanlge and inside textboxes with the headers (you have to adjust the size pixel by pixel), BUT.... you will get horrible columns merged when you export into excel and you cannot use dynamic column sizes....

I'm looking as well for a better solution... but it seems that nobody cares about this.

Regards

Braulio

|||oh cool, that's what I expected...

Thanks anyway for the answer ;)|||

Hi,

It is also possible to add a second Row Group and set the group expression the same value as the first. This way you will receive an additional column. In this column, you can then set the expression to the required value.

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||

Geert Verhoeven wrote:

Hi,

It is also possible to add a second Row Group and set the group expression the same value as the first. This way you will receive an additional column. In this column, you can then set the expression to the required value.

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

Hello,

The expression is static text. How do you proceed, please ?|||

Mmm... but I guess this is only valid in the data area (the right hand side of the matrix report), in the columns that identify the rows (left hand side), I guess you can not set that columns on top of the left area, isn't it ?

|||

Brauliod wrote:

Mmm... but I guess this is only valid in the data area (the right hand side of the matrix report), in the columns that identify the rows (left hand side), I guess you can not set that columns on top of the left area, isn't it ?

Yes, that's why I posted that message to confirm that it is impossible.

In fact, I can double the static rows in the RDL source. The designer shows well 2 columns, but the renderer refuses to show them.|||

Gert I'm trying to group rows, but I get them as an addition column not as a row above the left hand side of columns, could you tell us which steps must be performed ?

Thanks in advance, Regards

Braulio

|||

Hi,

I'll try to give an example. I'm using the following query on the AdventureWorks db:
SELECT Production.Product.Name, Production.ProductSubcategory.Name AS ProductSubCategory
FROM Production.Product INNER JOIN Production.ProductSubcategory ON Production.Product.ProductSubcategoryID = Production.ProductSubcategory.ProductSubcategoryID

If I get it right you want to have something like this:

ProductSubCategory
Product statictext

To do this, you need to follow these steps:

add a matrix to your report|||Yes, that works for dynamic data and grouping, but NOT for static data, multiple columns by row, which was my initial question...|||

Sorry, what I want to get is something like:

May 07 June 07

Project Name Location

Tiger Spain 20 40 (...)

I need the header on top ( the matrix reports that I'm using is more an horizontal report, the data columns are dynamic, but the left hand side columns are static and have the ID column and some attributes).Having the data columns dynamic does not allow me to use tabular reports :-(.

Any idea about how to do this?

Thanks a lot for your help.

Braulio

|||

Sorry, I didn't understand well the query (my problem is putting columns on top)... you want columns in the left hand side... mmm... for horizontal reports there is an interesting post in a blog tell me if this is what you are looking for

Does Reporting Services support horizontal tables (fixed rows and dynamic columns)http://blogs.msdn.com/chrishays/archive/2004/07/23/HorizontalTables.aspx

HTH

Braulio

|||

Brauliod wrote:

Sorry, I didn't understand well the query (my problem is putting columns on top)... you want columns in the left hand side... mmm... for horizontal reports there is an interesting post in a blog tell me if this is what you are looking for

Does Reporting Services support horizontal tables (fixed rows and dynamic columns)http://blogs.msdn.com/chrishays/archive/2004/07/23/HorizontalTables.aspx

HTH

Braulio

Hmm, and does this hack work well for Excel exporting ?|||

Mmmm...

I'm not sure I found this solution meanwhile I was looking for another, I just bookmarked it :-(.

I think it's worth to give a try, it doesn't use subtables or strange stuff, and the guy that wrote that stuff owns a lot of kudos, must be good stuff.

Good luck

Braulio

Matrix problems:any help needed urgent

Hi,
Im breaking my head trying to use a matrix...... :((
My first problem in having column headers for the static columns in the
matrix,
i solved it by using a table within the corner tag in the matrix,thanks to
one of the posts here...
but when i export it to excel it throws a message
Data Regions within table/matrix cells are ignored.
what im missing here...
Im displaying a date in the format
dd-MMM ddd
But when exported to excel it shows ####
Please any1 help me out...
Thanks,Last thing first:
##### for date: I think you are getting date correctly ,just increase the
width of date column in excel after you export and see ...
Matrix: For Static column add another row of columns under the column
grouping and put your Header text there..
"CCP" wrote:
> Hi,
> Im breaking my head trying to use a matrix...... :((
> My first problem in having column headers for the static columns in the
> matrix,
> i solved it by using a table within the corner tag in the matrix,thanks to
> one of the posts here...
> but when i export it to excel it throws a message
> Data Regions within table/matrix cells are ignored.
> what im missing here...
> Im displaying a date in the format
> dd-MMM ddd
> But when exported to excel it shows ####
> Please any1 help me out...
> Thanks,

Matrix column visibility

Hello,

I have one column group and three static columns. I'd like to control static column visibility property depending on a field value. I.e. Hidden: =Fields!GroupId<>5.

But I'm not able to find visibility property in column object. Neither in properties explorer nor RDL documentation. I'm only able to set hidden property in textbox objects.

I have RS 2005.

Thank you for any idea.

Jirka Nouza

Currently, the visibility of static matrix columns cannot be controlled. We are considering to lift this restriction in a future release.

-- Robert

|||

Well, nothing doing. Thank you for your replay.

Jirka

|||This would definitely be nice to have... is there any way to hack around this?

Matrix column visibility

Hello,

I have one column group and three static columns. I'd like to control static column visibility property depending on a field value. I.e. Hidden: =Fields!GroupId<>5.

But I'm not able to find visibility property in column object. Neither in properties explorer nor RDL documentation. I'm only able to set hidden property in textbox objects.

I have RS 2005.

Thank you for any idea.

Jirka Nouza

Currently, the visibility of static matrix columns cannot be controlled. We are considering to lift this restriction in a future release.

-- Robert

|||

Well, nothing doing. Thank you for your replay.

Jirka

|||This would definitely be nice to have... is there any way to hack around this?

Matrix break to next row (not span across pages)

I have a 8.5 X 11 report (portrait)
I have a matrix with two static rows and dynamic columns. The no. of
columns are anywhere between 4 to 8. Under that I have a graph.
What I want is simple. In my report, if the columns go beyond 4, they
seem to span across pages. While it keeps the graph on the first page.
I don't want that. Instead, I want the whole matrix to continue on the
same page (in two matrices, if needed). The graph can pushed out to
the next page.
Here is a visual:
THIS IS WHAT IS HAPPENING:
========================== Page 1
|Col1|Col2|Col3|Col4|
--
Row1| | | | |
--
Row2| | | | |
--
[Graph]
Page 2
|Col5|Col6|Col7|Col8|
--
Row1| | | | |
--
Row2| | | | |
--
THIS IS WHAT I WANT:
==================== Page 1
|Col1|Col2|Col3|Col4|
--
Row1| | | | |
--
Row2| | | | |
--
|Col5|Col6|Col7|Col8|
--
Row1| | | | |
--
Row2| | | | |
--
Page 2
[Graph]
Must be something simple... just don't seem find the answer.
ThxThis is not possible. Based on the runtime matrix size we will produce
multiple horizontal pages (the matrix witdth > page width) and multiple
vertical pages (matrix height > page height). We will not split at runtime
the matrix in multiple small matrices.
You need to create a report with 2 matrices or a matrix inside of a list and
filter/hide the columns accordinglly.
--
Nico Cristache [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Harsh" <creative@.mailcity.com> wrote in message
news:fa671a26.0407080821.2ead760@.posting.google.com...
> I have a 8.5 X 11 report (portrait)
> I have a matrix with two static rows and dynamic columns. The no. of
> columns are anywhere between 4 to 8. Under that I have a graph.
> What I want is simple. In my report, if the columns go beyond 4, they
> seem to span across pages. While it keeps the graph on the first page.
> I don't want that. Instead, I want the whole matrix to continue on the
> same page (in two matrices, if needed). The graph can pushed out to
> the next page.
>
> Here is a visual:
> THIS IS WHAT IS HAPPENING:
> ==========================> Page 1
> |Col1|Col2|Col3|Col4|
> --
> Row1| | | | |
> --
> Row2| | | | |
> --
> [Graph]
>
> Page 2
>
> |Col5|Col6|Col7|Col8|
> --
> Row1| | | | |
> --
> Row2| | | | |
> --
>
> THIS IS WHAT I WANT:
> ====================> Page 1
> |Col1|Col2|Col3|Col4|
> --
> Row1| | | | |
> --
> Row2| | | | |
> --
> |Col5|Col6|Col7|Col8|
> --
> Row1| | | | |
> --
> Row2| | | | |
> --
> Page 2
> [Graph]
>
> Must be something simple... just don't seem find the answer.
> Thx