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 pivot column question

I have a report that I'm setting up as a matrix.
The dataset is returning:
Description | Total | Available | Section | SectionQty
Item 1 | 4 | 4 | |
Item 2 | 8 | 6 | DIS | 1
Item 2 | 8 | 6 | FNC | 1
Item 3 | 4667 | 4651 | FNC | 16
Which shows, for each item, the total # and the available #, along with
which section has how many.
The Item, Total & Available columns are set up as row groups, the Section
column is the column group, and the SectionQty is the detail.
When the report renders, I get results of:
Item | Total | Available | | DIS | FNC
Item 1 | 4 | 4 |
Item 2 | 8 | 6 | | 1 | 1
Item 3 | 4667 | 4651 | | | 16
Note the empty column showing up between the Available and DIS columns.
How do I get rid of that empty column caused by the null for the first item
(which still needs to be on the report to show the total / available qty)?
Thanks,
Waynecreate a data set to get above data then create a matix to pipulate
data
On Jan 30, 3:27=A0pm, Wayne <Wa...@.discussions.microsoft.com> wrote:
> I have a report that I'm setting up as a matrix.
> The dataset is returning:
> Description | Total | Available | Section | SectionQty
> Item 1 =A0 =A0 =A0 | =A0 =A0 =A04 =A0| 4 =A0 =A0 =A0 =A0 =A0 =A0| =A0 =A0 ==A0 =A0 =A0 =A0 |
> Item 2 =A0 =A0 =A0 | =A0 =A0 =A08 =A0| 6 =A0 =A0 =A0 =A0 =A0 =A0| DIS =A0 ==A0 =A0 | 1
> Item 2 =A0 =A0 =A0 | =A0 =A0 =A08 =A0| 6 =A0 =A0 =A0 =A0 =A0 =A0| FNC =A0 ==A0 =A0| 1
> Item 3 =A0 =A0 =A0 | 4667 =A0| 4651 =A0 =A0 =A0 | FNC =A0 =A0 =A0| 16
> Which shows, for each item, the total # and the available #, along with
> which section has how many.
> The Item, Total & Available columns are set up as row groups, the Section
> column is the column group, and the SectionQty is the detail.
> When the report renders, I get results of:
> Item =A0 =A0| Total | Available | =A0 =A0 | DIS | FNC
> Item 1 | =A0 =A04 =A0 =A0| =A0 =A0 4 =A0 =A0 =A0 =A0|
> Item 2 | =A0 =A08 =A0 =A0| =A0 =A0 6 =A0 =A0 =A0 =A0| =A0 =A0 | =A0 =A01 |= =A0 =A0 1
> Item 3 | 4667 =A0| 4651 =A0 =A0 =A0 | =A0 =A0 | =A0 =A0 =A0| =A0 =A016
> Note the empty column showing up between the Available and DIS columns.
> How do I get rid of that empty column caused by the null for the first ite=m
> (which still needs to be on the report to show the total / available qty)?=
> Thanks,
> Wayne

matrix moving other controls

RS 2005

I have a matrix item in a report. It is only about 7cm in width in design mode but obviously explodes out to about 30cm in when the report is run as thats the way the query behind it works.

The problem is any control e.g. a chart placed above and to the right of the matrix in design mode gets pushed across to the end of the matrix's width when the report is run, which causes extra pages in the report.

Other controls placed directly above the matrix in design mode hold their position when the report is run, it is only those controls placed further right than the end of the matrix (and above the matrix) in design mode that get pushed across. Why is this, is there a way to fix it?

Thanks

N

Have you looked into grouping the items above / below the matrix with a rectangle so that all these items are contained within that rectangle and the rectangle is closer to the left edge of the report than the left edge of the matrix?

Once the other items are contained within a rectangle - only the layout dependencies between the matrix and the rectangle remain. The position of the items contained within the rectangle will be unaffected by the growth of the matrix.

-- Robert

|||

I have the same problem

how do you group the rectangle to the controls ?

I have 2 images the top image near the table header stays put but the one below that moves with the table..

I place a rectangle but the image still moves

matrix moving other controls

RS 2005

I have a matrix item in a report. It is only about 7cm in width in design mode but obviously explodes out to about 30cm in when the report is run as thats the way the query behind it works.

The problem is any control e.g. a chart placed above and to the right of the matrix in design mode gets pushed across to the end of the matrix's width when the report is run, which causes extra pages in the report.

Other controls placed directly above the matrix in design mode hold their position when the report is run, it is only those controls placed further right than the end of the matrix (and above the matrix) in design mode that get pushed across. Why is this, is there a way to fix it?

Thanks

N

Have you looked into grouping the items above / below the matrix with a rectangle so that all these items are contained within that rectangle and the rectangle is closer to the left edge of the report than the left edge of the matrix?

Once the other items are contained within a rectangle - only the layout dependencies between the matrix and the rectangle remain. The position of the items contained within the rectangle will be unaffected by the growth of the matrix.

-- Robert

|||

I have the same problem

how do you group the rectangle to the controls ?

I have 2 images the top image near the table header stays put but the one below that moves with the table..

I place a rectangle but the image still moves

Matrix calculation

Hi,
I have a matrix which looks as follows:
Actuals Budget
Item 1 20 50
Item 2 45 30
Item 3 20 15
I would like to add a column with the difference between actuals and budget.
Actuals/budget is 1 group in the matrix being the dimensionname.
How can I calculate the difference, because if I just use the subtotal
function it adds both totals together and I need to have the difference
between them?
Please help...Right click on the cells that contain the data ("Details" as they are
called). Press on the "Add Column" button. In the new column, click
on the details part where the data goes and then put and expression in
there that will take the two data points you want and subtract them in
the way that you want.|||Thanks for the update, I have both actuals and budget in the
"matrix1_ColumnGroup4", how can I then subtract both fields from eachother?
How can I indicate to which field the database needs to look at?
Please help as this is important for me...
"SQL Guy" wrote:
> Right click on the cells that contain the data ("Details" as they are
> called). Press on the "Add Column" button. In the new column, click
> on the details part where the data goes and then put and expression in
> there that will take the two data points you want and subtract them in
> the way that you want.
>|||Ok so you have a new column, in the cell put an expression that will
subtract the two like this:
=Fields!Budget.Value-Fields!Actuals.Value
That should automatically take the budget and actual from the current
row and subtract them from each other. You would also put this in your
select statement. For example
Select actual,budget,(actual-budget) as difference
from YOUR_TABLE_NAME
where ......
Then you would have an already calculated field to use in your
matrix. Really they both go the same way. My preference is to do it
on the SQL side rather than put a formula in the matrix. It's up to
you. Hope it helps!|||I had a question, why would you even need a matrix for this? I think
a table would suffice.|||I had a question, why would you even need a matrix for this? I think
a table would suffice.|||On Nov 5, 5:41 am, DHL <D...@.discussions.microsoft.com> wrote:
> Hi,
> I have a matrix which looks as follows:
> Actuals Budget
> Item 1 20 50
> Item 2 45 30
> Item 3 20 15
> I would like to add a column with the difference between actuals and budget.
> Actuals/budget is 1 group in the matrix being the dimensionname.
> How can I calculate the difference, because if I just use the subtotal
> function it adds both totals together and I need to have the difference
> between them?
> Please help...
If this is a Matrix, this implies your data is set up like:
Items, Type, Amount
Item 1, Actuals, 20
Item 1, Budget, 50
Item 2, Actuals, 45
...
which means you can't actually do a single expression of Fields!
Actual.Value - Fields!Budget.Value.
Two ways I would do this:
1. I would add a new field to the dataset, Amount2, with an
expression of
=IIF( Fields!Type = "Budget", Fields!Amount, IIF( Fields!Type ="Actuals", -1 * Fields!Amount, Nothing ) )
This flips the sign of your budget, so that the Sum of types Actuals
and Budget gets you the difference you asked for. Then, in the
Matrix, add a subtotal on the Columns (group matrix1_Type) and use the
following expression in the Details:
=IIF( InScope("matrix1_Type"), Sum(Fields!Amount.Value), Sum(Fields!
Amount2.Value) )
What happens here is that when the Matrix is calculating the Sum() in
each column, it's doing so under the Scope of that column
("matrix1_Type"). The Total Sum at the end of the Column is done at
the Matrix Scope, so InScope() returns false, and returns the Sum of
the custom field instead.
2. Or, change the Data tab's SQL SELECT to include a union with the
calculated amount
SELECT T.ITEMS, T.TYPE, T.AMOUNT FROM TABLE_NAME T
UNION
SELECT T.ITEMS, 'Total' AS TYPE,
SUM( CASE T.TYPE
WHEN 'Actuals' THEN T.AMOUNT
WHEN 'Budget' THEN -T.AMOUNT ELSE NULL END ) AS AMOUNT
FROM TABLE_NAME T
WHERE T.TYPE IN ( 'Actuals', 'Budget' )
GROUP BY T.ITEMS
then just refresh your existing Matrix and a Totals column will appear
with the total you requested.
-- Scott