Matrix with 2 subtotal

Hi All,

I wish to have a report with 2 subtotal using matrix control given by reporting service.

CD

DVD

Total Price

Total Qty

2.00

5.00

JOHN

5

25.00

5

JOLIN

5

10.00

5

Total Price

10.00

25.00

Total Qty

5

5

I only can came out total price(subtotal) which provided by matrix control.

Does the matrix control able to come out as table above with 2 subtotal?

Thanks

Kendy

The grid above has the unit price in the header and only the quantity measure in the details area. You need to create and additional Price measure and display both measures in the details area either on the rows or the columns as a static group. E.g.

Measures on Columns

CD

DVD

Total

2.00

5.00

Quantity

Price

Quantity

Price

Quantity

Price

JOHN

5

25

5

25

JOLIN

5

10

5

10

Total

5

10

5

25

10

35

Measures on Rows

CD

DVD

Total

2.00

5.00

JOHN

Quantity

5

5

Price

25

25

JOLIN

Quantity

5

5

Price

10

10

Total

Quantity

5

5

10

Price

10

25

35

|||

Thanks for your helps and advice.

Its really give me an ideas

Thanks Adam

Matrix width

How do you find out the width of a matrix control, I need this to
enable me to size the width of a Textbox, as the width needs to be the
same size as the Matrix control which keeps growing depending on the
dataThe width of your textbox is changing dynamically? Wow! How did you get it
to do that? The "increase to accomidate contents" checkbox only increases
the height on my version RS. I haven't found out to dynamically change the
width yet. And I believe several other people on this forum are searching
for this same functionality.
BTW, the only place I've seen the size properties (height & width) are in
the properties screen of the individual boxes or the rows or the
tables/matrices/lists. I asked a while back for how to call those properties
in an expression (I know it's similar to "Type!Name.Value" - still don't know
what type to use for properties), but no one answered the question.
Hopefully, someone will help us both out and answer the question here.
Catadmin
"Bryan Avery" wrote:
> How do you find out the width of a matrix control, I need this to
> enable me to size the width of a Textbox, as the width needs to be the
> same size as the Matrix control which keeps growing depending on the
> data
>sql

Matrix Control Reports...

Hi Folks,
I am in the process of creating MSRS 2005 reports which run of a MSAS cube.
I have 2 matrix controls which feed of 2 different datasets. There are 3
columns in each matrix control which are part of a hierarchy. when the user
drills down a parent on the first control, is it possible for the child on
the second matrix control to be also visible.
For example when user clicks on the + sign before "A", "B" is made visible
on first matrix, at the same time "B" should be visible on the second matrix.
2. I have 2 matrix controls side by side, i have to show and hide the 2
based on a report parameter. Is it possible to dynamically alter the location
of the matrix control on the report. For example if the 1st matrix control is
made invisible, can i move 2 matrix control to take the place of the first
matrix cntrol.
Thank you
RamdasHi Ram,
I have the same problem (your second) - I want to decide the location of
the Matrix dynamically at run time. (Reporting Services)
If you've come to a solution, kindly contact me
DAVIDMA4@.YAHOO.COM
David
--
×?×?×?
"Ram" wrote:
> Hi Folks,
> I am in the process of creating MSRS 2005 reports which run of a MSAS cube.
> I have 2 matrix controls which feed of 2 different datasets. There are 3
> columns in each matrix control which are part of a hierarchy. when the user
> drills down a parent on the first control, is it possible for the child on
> the second matrix control to be also visible.
> For example when user clicks on the + sign before "A", "B" is made visible
> on first matrix, at the same time "B" should be visible on the second matrix.
> 2. I have 2 matrix controls side by side, i have to show and hide the 2
> based on a report parameter. Is it possible to dynamically alter the location
> of the matrix control on the report. For example if the 1st matrix control is
> made invisible, can i move 2 matrix control to take the place of the first
> matrix cntrol.
> Thank you
> Ramdas
>

Matrix Control pushes out Graphs on Report

I have a report with multiple graphs below each other (some bar, some line, some pie)
To the left of each graph, I have a few text boxes and tables tables displaying information about each graph to the right. (fits on A4 portait page)
For lack of being able to display a pic:
Table Graph
_______ ________________________________
| | | |
| | | |
|______ | |_______________________________|
_______ ________________________________
| | | |
| | | |
|______ | |_______________________________|
Right at the bottom of the report, just below the last table/graph combination, I have a simple matrix control.
In the preview pane, all is well, no problem. When I deploy the report to the report server, the matrix control pushes all graphs out for the entire length of the matrix.
Table Blank space Graph
_______ ________________________________
| | | |
| | | |
|______ | |_______________________________|
_______ ________________________________
| | | |
| | | |
|______ | |_______________________________|
_______________________
| |
|______________________|
Matrix /\
The only way I get the report to display correctly is when I specify that the matrix must start on a new page. Unfortunately, the customer wants all on one page.
Any ideas?As items grow vertically, they push items below them.
As they grow horizontally, they push items beside them on the page.
An easy way to prevent this is to make sure your graphs aren't considered to
be to the right of the matrix by grouping the table and graph together in a
rectangle:
--
| -- -- |
| |Table| |Graph| |
| -- -- |
--
--
|Matrix|
--
--
My employer's lawyers require me to say:
"This posting is provided 'AS IS' with no warranties, and confers no
rights."
"Michelle" <Michelle@.discussions.microsoft.com> wrote in message
news:363724F0-D9EE-4BD3-9769-032E79430C6F@.microsoft.com...
> I have a report with multiple graphs below each other (some bar, some
line, some pie)
> To the left of each graph, I have a few text boxes and tables tables
displaying information about each graph to the right. (fits on A4 portait
page)
> For lack of being able to display a pic:
> Table Graph
> _______ ________________________________
> | | | |
> | | | |
> |______ | |_______________________________|
> _______ ________________________________
> | | | |
> | | | |
> |______ | |_______________________________|
>
> Right at the bottom of the report, just below the last table/graph
combination, I have a simple matrix control.
> In the preview pane, all is well, no problem. When I deploy the report to
the report server, the matrix control pushes all graphs out for the entire
length of the matrix.
> Table Blank space Graph
> _______ ________________________________
> | | |
|
> | | |
|
> |______ | |_______________________________|
> _______ ________________________________
> | | |
|
> | | |
|
> |______ | |_______________________________|
> _______________________
> | |
> |______________________|
> Matrix /\
> The only way I get the report to display correctly is when I specify that
the matrix must start on a new page. Unfortunately, the customer wants all
on one page.
> Any ideas?

matrix control parent child grouping

does anybody know how to properly wokt out a parent child hierarchy in a matrix control?

*i already know that i have to work with the 'edit group' button and then

the 'general' tab and the values 'group on' : field.uniquename and 'parent group' .parentuniquename

*but fot some reason i always have to include 2 extra rows (my hierarchy is on a row)

one with field.uniquename and one with field.parentuniquename, i can make the invisible though but i can't remove them, otherwise it won't work

is this a kind of bug?)

*but finally what i really can't find out is how to properly toggle the visibility

the idea is that the hierarchy is collapsed (i have more then 8 levels, and more then 10000 membders) and that by clicking on a parent , the children appear

(the standard intuitive behavour of parent child controls)

anybody any clues?

on my way i found posting:

Creating report based on parent-child dimension

i think it's about the same subject

Matrix Control in SSRS

Just wondering when is it preferred to use Matrix control as opposed to a table? Also, on a related note, can a table control be used for displaying information from one data set or could information coming from multiple data sets be bound to a single table in such a way that there could be logically different sections within the same table?

Thanks.

I ask that question at times too

I think it's whenever you need to pivot table/data, say in Excel (convert columns to rows)

However, there are reports that I have to manually create a side-by-side comparison table in the database

as front-end matrix isn't exactly what I wanted

in fact, I have only about 2 reports that I used matrix so far

I think you can only return & use 1 dataset in a report (1st dataset if Query/Proc returns multiple)

so if you want to use different datasets, either use sub-report, or union them (vertically/horizontally) in the backend

|||

I think what I need is the (Dynamic Rows x Dynamic Columns), or essentially Dynamically generated Pivot Table. This is because, I do not know in advance how many rows or columns will be returned from the DB. All I have is an ID of a parent table which could be used to join and get rows from the child table, and then for each of those rows I need to get rows from another table and display them in columns of the report (Is this the same as Dynamic Cross Tabs/Privot?). Can I use the matrix control for this scenario and do I need to write some dynamic SQL for this?

Any suggestions will be appreciated.

DNG.

|||

DotNet_Guy wrote:

I think what I need is the (Dynamic Rows x Dynamic Columns), or essentially Dynamically generated Pivot Table. This is because, I do not know in advance how many rows or columns will be returned from the DB. All I have is an ID of a parent table which could be used to join and get rows from the child table, and then for each of those rows I need to get rows from another table and display them in columns of the report (Is this the same as Dynamic Cross Tabs/Privot?). Can I use the matrix control for this scenario and do I need to write some dynamic SQL for this?

Any suggestions will be appreciated.

DNG.

This is exactly where I use the Matrix report item in SSRS. You should not need any sort of dynamic SQL under the hood. Your SQL query should return a well-defined rowset (with a fixed set of fields) and the Matrix report item will turn some of the fields into columns, some into rows, and some into data based on how you set up your report.

It's totally trivial to test this - if you already have your query written, create a few sample reports and try it out with different settings.

The one obvious drawback I see is that the Matrix report item does not readily support a large number of data fields. It can do it, but the resultant report tends to get very large, very quickly.

|||Cool. I will give it a try. BTW, how come you have so few certs against your name? jus kidding |||

DotNet_Guy wrote:

Cool. I will give it a try. BTW, how come you have so few certs against your name? jus kidding

ROFL!

They used to require MCTs pass any exams related to a course before being allowed to teach that course, so I hat to take a lot of exams. The letters just sort of came along naturally.

Let us know how the Matrix works for you, ok?

|||Yes it does. Thanks for the help. I believe I can now add non-pivot rows and columns as well to the dynamic matrix. It should all be in the sql2k5 tutorial documentation, but I haven't had the time to dig in.|||

I am able to display the data in matrix, however I need to place in next to a table report item, so that the rows in table and matrix together would represent the data for same entity. In order to accomplish that, the Matrix rows need to be properly aligned with Table rows. But thats not happening. For some reason, in the Layout view they align properly, but when I Preview the report, the rows are not aligned. I have checked the cell sizes of both in the properties, they are both identical. So, I am very surprised why the Matrix row size is growing less than Tabel row size when the report is generated. Any ideas how to fix this?

|||

Can any body explain why I am having the above mentioned strange behavior upon rendering the matrix and table next to each other?

On a related note, how can I access the Sub Total fields of the Matrix to use them in calculation elsewhere?

Thanks.

Matrix control behavior

Anyone ...
Am I missing something regarding the matrix control?
I have set up a report with title information. I drag in and fill a matrix
with the report data and for some reason when I preview the report it looks
as if it widens the report to the right and even takes one of the text boxes
from my titles and shoves that way to the right as well.
The end result is that I get the matrix and the data on the report but with
the title/headers all messed up.Move all your title textboxes into one single rectangle (to "group" the
layout). Then make sure that the left corner of that rectangle is left of
the matrix left corner.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"David M via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:52BC637F3CF78@.SQLMonster.com...
> Anyone ...
> Am I missing something regarding the matrix control?
> I have set up a report with title information. I drag in and fill a
> matrix
> with the report data and for some reason when I preview the report it
> looks
> as if it widens the report to the right and even takes one of the text
> boxes
> from my titles and shoves that way to the right as well.
> The end result is that I get the matrix and the data on the report but
> with
> the title/headers all messed up.|||Robert;
Oh man, Thank you for the advice. I tried it on one of my reports and it
worked like a charm. I'm pretty new working with this product and would
never have thought of that.
Thanks Again.
Dave
Robert Bruckner [MSFT] wrote:
>Move all your title textboxes into one single rectangle (to "group" the
>layout). Then make sure that the left corner of that rectangle is left of
>the matrix left corner.
>-- Robert
>> Anyone ...
>[quoted text clipped - 11 lines]
>> with
>> the title/headers all messed up.
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200508/1

Matrix control

Hi all,

I'm using matrix control as my reporting tools.

But i found one trouble, how can i do my matrix become like this.

Product A

Product B

Customer

Price

Qty

Price

Qty

Customer A

10

10

140

160

Customer B

12

120

120

160

Customer C

10

10

110

140

The price and qty is the static column, and i would like to make the static row also. (show in red color) but i can't.

Please help...

Thanks

Kendy

1.

Add matrix contol

2.

in the row add Customer

3. in the columns add Product

4. Right click on Data and select add column

no you will have two column

add price to the left onw and Qty to the right onw

|||

Hi Idanle,

I have create the matrix control, for the qty and price i using static column.

For static row, i have do it also but the result will be like this

Product A

Product B

Price

Qty

Price

Qty

Customer

Customer A

10

10

140

160

Customer

Customer B

12

120

120

160

Customer

Customer C

10

10

110

140

It will repeat because is static row same as static column where repeated showing out the "Price" and "Qty".

How to make the title "customer" only showing as header.

Thanks

Kendy

|||Can you send me the RDL or upload a print screen of youe design mode|||

Hi Idanle,

I have upload the excel file.

Inside the excel file, the tab name "Defect" is the problem i facing, wherelse the tab name "Output" is what i expected.

http://download.yousendit.com/ED0492A72B6C6C66

Thanks

Kendy

|||

I can't found the problem.

Can you send me your table (data source) and the RDL or the matrix definition?

|||

Hi Idanle,

As u can see from the excel file, the tab defect page.

The "Customer" header will always display as a row detail where show in red color.

But when see the output tab, where the result i wanted. The "Customer" header will remain as header, it wouldn't same like defect tab.

My question is can matrix control done as output tab, if yes how the way to do it.

Thanks

Kendy

|||

Hi,

I see the problem and I'ts seams somthing worng with your matrix setting since this problem should not occur.

This is way I ask for thr RDL and table.

If you can just create your data table in excel I will set for you the RDL

|||

Hi Idanle,

I have upload the table, rdlc and expected output i wanted.

http://download.yousendit.com/CA8B3D95276E0341

Thanks for your help.

Regards

Kendy

|||

The table you sent me is not the same table using your Expected output.

What is the problem with the current output ? it's look fine

|||

Hi Idanle,

Actually, when you look at expected output and current output. The only different is header, in expected output there are header where mark in red color wherelse in current output is without the header.

And i have try do it with static row (just try in one column name Customer), the result i get is like this, please download from the link.

http://download.yousendit.com/BC879C5820984C1A

So can i know how to do it become expected output using matrix control provided by sql server reporting services?

Thanks

Kendy

|||For column headers, what you need to do is put a rectangle in the corner cell. In this rectangle, put your Customer label textbox.

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?