Matrix won't collapse

I have a Matrix displaying my OLAP based dataset. The dataset looks correct
and the MAtrix displays properly with all rows and columns expanded.
I don't have the ability to collapse or expand any of the rows or columns.
No + - icon displays either.
I have tried changing settings for the initial display of columns as
collapsed and expanded.
Any ideas on the problem?
JimOK... I got it.
The visibility needs to be set on the groups.
Jim
"Jim L" <jim@.noaddress.com> wrote in message
news:uwYeUKXrEHA.1204@.TK2MSFTNGP12.phx.gbl...
>I have a Matrix displaying my OLAP based dataset. The dataset looks correct
>and the MAtrix displays properly with all rows and columns expanded.
> I don't have the ability to collapse or expand any of the rows or columns.
> No + - icon displays either.
> I have tried changing settings for the initial display of columns as
> collapsed and expanded.
> Any ideas on the problem?
> Jim
>

Matrix report not setting the pagewidth properly

Hi,
I have a matrix report that displays offices down the page and all the
days in a month across the page, with a subreport in each cell
displaying other data.
The problem i am having is when i load the page the first time it will
only display half the days, when i press refresh or change the zoom
level it correctly displays all the data.
I have tried setting the zoom level on the url and setting the actual
and interactive page size to A2 (594mm x 420mm) and it has made no
difference.
Any suggestions on how i can get this to display correctly?
Thanks
DarrynOn Mar 11, 9:49 pm, darryn.brya...@.gmail.com wrote:
> Hi,
> I have a matrix report that displays offices down the page and all the
> days in a month across the page, with a subreport in each cell
> displaying other data.
> The problem i am having is when i load the page the first time it will
> only display half the days, when i press refresh or change the zoom
> level it correctly displays all the data.
> I have tried setting the zoom level on the url and setting the actual
> and interactive page size to A2 (594mm x 420mm) and it has made no
> difference.
> Any suggestions on how i can get this to display correctly?
> Thanks
> Darryn
Usually, when including subreports in a table, etc of another report,
the format tends to be a little hard to control (at least, at the
granular level). If there is a way to avoid using the subreport(s) in
this way, I would suggest it. I would check the complexity of the
queries/stored procedures used for the subreports and maybe use the
Database Engine Tuning Advisor to improve the performance of the
queries/stored procedures. It seems like there might be a bottleneck
with the queries. Hope this helps.
Regards,
Enrique Martinez
Sr. SQL Server Developer

Matrix Duplicates

I have a matrix created that is displaying duplicate rows, the underlying
data is fine, not sure what's going on . It looks something like this.
Label Job#1 Job#2
Shoes 10 15
Shirts 50 45
Pants 25
Pants 40
I can't understand why "pants" is displaying twice. It seems to be
happening randomly in the matrix.Most likely, in one case the label field has a contents like "Pants", and in
the other case there is some whitespace at the end, such as "Pants ".
Try changing the grouping expression to e.g. =Trim(Fields!Label.Value)
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"FL Jim" <FLJim@.discussions.microsoft.com> wrote in message
news:0BA709D4-88AF-4126-943D-829CDD76B869@.microsoft.com...
>I have a matrix created that is displaying duplicate rows, the underlying
> data is fine, not sure what's going on . It looks something like this.
> Label Job#1 Job#2
> Shoes 10 15
> Shirts 50 45
> Pants 25
> Pants 40
> I can't understand why "pants" is displaying twice. It seems to be
> happening randomly in the matrix.|||I tried this and now I'm getting a blank label for the duplicate row, but
it's still there. I did a Len(Label) for these rows in SQL and they are the
exact same length. Quite strange.
Label Job#1 Job#2
Shoes 10 15
Shirts 50 45
Pants 25
40
"Robert Bruckner [MSFT]" wrote:
> Most likely, in one case the label field has a contents like "Pants", and in
> the other case there is some whitespace at the end, such as "Pants ".
> Try changing the grouping expression to e.g. =Trim(Fields!Label.Value)
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "FL Jim" <FLJim@.discussions.microsoft.com> wrote in message
> news:0BA709D4-88AF-4126-943D-829CDD76B869@.microsoft.com...
> >I have a matrix created that is displaying duplicate rows, the underlying
> > data is fine, not sure what's going on . It looks something like this.
> >
> > Label Job#1 Job#2
> > Shoes 10 15
> > Shirts 50 45
> > Pants 25
> > Pants 40
> >
> > I can't understand why "pants" is displaying twice. It seems to be
> > happening randomly in the matrix.
>
>|||I realized it's now blank becuase the 'hide duplicates' toggle was on for the
group in the matrix; however, it doesn't explain why it recognizes the row as
a duplicate, but doesn't just display the data together in one row.
"Robert Bruckner [MSFT]" wrote:
> Most likely, in one case the label field has a contents like "Pants", and in
> the other case there is some whitespace at the end, such as "Pants ".
> Try changing the grouping expression to e.g. =Trim(Fields!Label.Value)
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "FL Jim" <FLJim@.discussions.microsoft.com> wrote in message
> news:0BA709D4-88AF-4126-943D-829CDD76B869@.microsoft.com...
> >I have a matrix created that is displaying duplicate rows, the underlying
> > data is fine, not sure what's going on . It looks something like this.
> >
> > Label Job#1 Job#2
> > Shoes 10 15
> > Shirts 50 45
> > Pants 25
> > Pants 40
> >
> > I can't understand why "pants" is displaying twice. It seems to be
> > happening randomly in the matrix.
>
>|||There may be something else in the query or the report design that results
in that behavior. Can you post a small report (e.g. based on Northwind data)
that reproduces the issue you are experiencing?
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"FL Jim" <FLJim@.discussions.microsoft.com> wrote in message
news:46268052-7531-4F0C-A2BE-FE27AAA8E7A3@.microsoft.com...
>I realized it's now blank becuase the 'hide duplicates' toggle was on for
>the
> group in the matrix; however, it doesn't explain why it recognizes the row
> as
> a duplicate, but doesn't just display the data together in one row.
> "Robert Bruckner [MSFT]" wrote:
>> Most likely, in one case the label field has a contents like "Pants", and
>> in
>> the other case there is some whitespace at the end, such as "Pants ".
>> Try changing the grouping expression to e.g. =Trim(Fields!Label.Value)
>> -- Robert
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "FL Jim" <FLJim@.discussions.microsoft.com> wrote in message
>> news:0BA709D4-88AF-4126-943D-829CDD76B869@.microsoft.com...
>> >I have a matrix created that is displaying duplicate rows, the
>> >underlying
>> > data is fine, not sure what's going on . It looks something like
>> > this.
>> >
>> > Label Job#1 Job#2
>> > Shoes 10 15
>> > Shirts 50 45
>> > Pants 25
>> > Pants 40
>> >
>> > I can't understand why "pants" is displaying twice. It seems to be
>> > happening randomly in the matrix.
>>

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 assistance required

Hi,
I'm trying to build a report displaying a matrix that looks like the
following but have some trouble composing it:
Required output :
Code, Name, Target, Batch 1, Batch 2, Batch 3
1, One, 12, 10, 11, 12
2, Two, 9, 10, 9, 11
Totals: 21, 20, 20, 23
From the following data
BatchId, Code, Name, Target, Actual
1, 1, One, 12, 10
1, 2, Two, 9, 10
2, 1, One, 12, 11
2, 2, Two, 9, 9
3, 1, One, 12, 12
3, 2, Two, 9, 11
(Assumption: all targets for a specified code are the same, batch=1 code=1
target=12 -> all targets for code=1 equal 12)
The main issue I have is adding the target totals.
Could someone please help me with this?
ErikErik,
Unless I am missing something, in your case you can simply add a matrix
subtotal element by right-clicking on a matrix group in the report layout
and choose Subtotal.
--
Hope this helps.
---
Teo Lachev, MCSD, MCT
Author: "Microsoft Reporting Services in Action"
http://www.prologika.com
"Erik Tamminga" <REVERSE_THIS_agnimmate@.REVERSE_THIS_nerrats.ln> wrote in
message news:e2vLhFrfEHA.3612@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I'm trying to build a report displaying a matrix that looks like the
> following but have some trouble composing it:
> Required output :
> Code, Name, Target, Batch 1, Batch 2, Batch 3
> 1, One, 12, 10, 11, 12
> 2, Two, 9, 10, 9, 11
> Totals: 21, 20, 20, 23
> From the following data
> BatchId, Code, Name, Target, Actual
> 1, 1, One, 12, 10
> 1, 2, Two, 9, 10
> 2, 1, One, 12, 11
> 2, 2, Two, 9, 9
> 3, 1, One, 12, 12
> 3, 2, Two, 9, 11
> (Assumption: all targets for a specified code are the same, batch=1 code=1
> target=12 -> all targets for code=1 equal 12)
> The main issue I have is adding the target totals.
> Could someone please help me with this?
> Erik
>|||Hi Teo,
Thank you for the reply, but that doesn't fix the problem.
The target column exists only once and doesn't repeat like the "Batch ..."
column does. I don't know exactly how to explain this issue further more
because my "matrix-knowledge" doesn't reach that far.
What I did to create the matrix is the following:
- Add a matrix control
- Drop the "BatchId" field into the "columns" cell.
- Drop the "Code", "Name" and "Target" fields into the "rows" cells (the
seconds and third fields added as row-groups.
I managed to get subtotals for the "Batch..." columns by adding a new
row-group (expression =1) and Selecting "Subtotals" for this cell. But I'd
rather not see this column in my report and it doesn't totalize the target
column.
Maybe you could post me an example based on the data described below? (if
it's not too much trouble) I like solving my own problems, but are pulling
my hairs out on this one.
Erik Tamminga, MCSD
"Teo Lachev" <teo@.nospam.prologika.com> wrote in message
news:%23IHSQLtfEHA.904@.TK2MSFTNGP09.phx.gbl...
> Erik,
> Unless I am missing something, in your case you can simply add a matrix
> subtotal element by right-clicking on a matrix group in the report layout
> and choose Subtotal.
> --
> Hope this helps.
> ---
> Teo Lachev, MCSD, MCT
> Author: "Microsoft Reporting Services in Action"
> http://www.prologika.com
>
> "Erik Tamminga" <REVERSE_THIS_agnimmate@.REVERSE_THIS_nerrats.ln> wrote in
> message news:e2vLhFrfEHA.3612@.TK2MSFTNGP12.phx.gbl...
> > Hi,
> >
> > I'm trying to build a report displaying a matrix that looks like the
> > following but have some trouble composing it:
> >
> > Required output :
> >
> > Code, Name, Target, Batch 1, Batch 2, Batch 3
> > 1, One, 12, 10, 11, 12
> > 2, Two, 9, 10, 9, 11
> > Totals: 21, 20, 20, 23
> >
> > From the following data
> >
> > BatchId, Code, Name, Target, Actual
> > 1, 1, One, 12, 10
> > 1, 2, Two, 9, 10
> > 2, 1, One, 12, 11
> > 2, 2, Two, 9, 9
> > 3, 1, One, 12, 12
> > 3, 2, Two, 9, 11
> >
> > (Assumption: all targets for a specified code are the same, batch=1
code=1
> > target=12 -> all targets for code=1 equal 12)
> >
> > The main issue I have is adding the target totals.
> >
> > Could someone please help me with this?
> >
> > Erik
> >
> >
>|||Eric,
I see now what the issue is. You need to create a subtotal on a row column
(Target). Unfortunately, this is not supported. As a workaround (if
acceptable), can you transform the data at the data source and make the
Target a Batch? In other words, instead of
BatchId, Code, Name, Target, Actual
1, 1, One, 12, 10
1, 2, Two, 9, 10
2, 1, One, 12, 11
2, 2, Two, 9, 9
3, 1, One, 12, 12
3, 2, Two, 9, 11
Have
BatchId, Code, Name, Actual
1, 1, One, 10
1, 2, Two, 10
2, 1, One, 11
2, 2, Two, 9
3, 1, One, 12
3, 2, Two, 11
Target, 1, One, 12
Target, 2, Two, 9
and so on...
Hope this helps.
---
Teo Lachev, MCSD, MCT
Author: "Microsoft Reporting Services in Action"
http://www.prologika.com
"Erik Tamminga" <REVERSE_THIS_agnimmate@.REVERSE_THIS_nerrats.ln> wrote in
message news:u5l62PufEHA.636@.TK2MSFTNGP12.phx.gbl...
> Hi Teo,
> Thank you for the reply, but that doesn't fix the problem.
> The target column exists only once and doesn't repeat like the "Batch ..."
> column does. I don't know exactly how to explain this issue further more
> because my "matrix-knowledge" doesn't reach that far.
> What I did to create the matrix is the following:
> - Add a matrix control
> - Drop the "BatchId" field into the "columns" cell.
> - Drop the "Code", "Name" and "Target" fields into the "rows" cells
(the
> seconds and third fields added as row-groups.
> I managed to get subtotals for the "Batch..." columns by adding a new
> row-group (expression =1) and Selecting "Subtotals" for this cell. But I'd
> rather not see this column in my report and it doesn't totalize the target
> column.
> Maybe you could post me an example based on the data described below? (if
> it's not too much trouble) I like solving my own problems, but are pulling
> my hairs out on this one.
> Erik Tamminga, MCSD
> "Teo Lachev" <teo@.nospam.prologika.com> wrote in message
> news:%23IHSQLtfEHA.904@.TK2MSFTNGP09.phx.gbl...
> > Erik,
> >
> > Unless I am missing something, in your case you can simply add a matrix
> > subtotal element by right-clicking on a matrix group in the report
layout
> > and choose Subtotal.
> >
> > --
> > Hope this helps.
> >
> > ---
> > Teo Lachev, MCSD, MCT
> > Author: "Microsoft Reporting Services in Action"
> > http://www.prologika.com
> >
> >
> > "Erik Tamminga" <REVERSE_THIS_agnimmate@.REVERSE_THIS_nerrats.ln> wrote
in
> > message news:e2vLhFrfEHA.3612@.TK2MSFTNGP12.phx.gbl...
> > > Hi,
> > >
> > > I'm trying to build a report displaying a matrix that looks like the
> > > following but have some trouble composing it:
> > >
> > > Required output :
> > >
> > > Code, Name, Target, Batch 1, Batch 2, Batch 3
> > > 1, One, 12, 10, 11, 12
> > > 2, Two, 9, 10, 9, 11
> > > Totals: 21, 20, 20, 23
> > >
> > > From the following data
> > >
> > > BatchId, Code, Name, Target, Actual
> > > 1, 1, One, 12, 10
> > > 1, 2, Two, 9, 10
> > > 2, 1, One, 12, 11
> > > 2, 2, Two, 9, 9
> > > 3, 1, One, 12, 12
> > > 3, 2, Two, 9, 11
> > >
> > > (Assumption: all targets for a specified code are the same, batch=1
> code=1
> > > target=12 -> all targets for code=1 equal 12)
> > >
> > > The main issue I have is adding the target totals.
> > >
> > > Could someone please help me with this?
> > >
> > > Erik
> > >
> > >
> >
> >
>

Matrix Aggregation to a Column only

Hello,
I have a matrix report displaying data from a data cube, via analysis
services. It contains column groupings for Months, Quarters and Year as well
as row groups for Team, User and Region. This allows the user to drill down
from a top level "All Teams" total for the whole year, down to a full view of
a region for a particular month.
To achieve this, the "Sum" function is used to aggregate the figures. But,
we wish to also use a running total of Sales by region using Brought Forward
and Carried Forward balances. This needs to be aggregated by column (region
to user to team), but not by row. (Otherwise the Jan, Feb and March totals
are added together for example for Q1, which is incorrect).
Is there a way to specify the axis for the Sum function? Or another way to
create the report that still allows the drill-down/up functionality?
Many Thanks.
Ben Mann.Although I don't understand the business part of your question... Matrix
allows aggregations to be done on rows OR columns... It looks like you
wanted some aggregation on both axes... Matrixes can do this...
Also you can use any of the aggregate functions that RS supports, take a
look at runningvalue... that might be what you are lookin for...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Ben Mann" <Ben Mann@.discussions.microsoft.com> wrote in message
news:37B6B96A-FF6F-4D63-A820-B6E588C13233@.microsoft.com...
> Hello,
> I have a matrix report displaying data from a data cube, via analysis
> services. It contains column groupings for Months, Quarters and Year as
well
> as row groups for Team, User and Region. This allows the user to drill
down
> from a top level "All Teams" total for the whole year, down to a full view
of
> a region for a particular month.
> To achieve this, the "Sum" function is used to aggregate the figures. But,
> we wish to also use a running total of Sales by region using Brought
Forward
> and Carried Forward balances. This needs to be aggregated by column
(region
> to user to team), but not by row. (Otherwise the Jan, Feb and March totals
> are added together for example for Q1, which is incorrect).
> Is there a way to specify the axis for the Sum function? Or another way to
> create the report that still allows the drill-down/up functionality?
> Many Thanks.
> Ben Mann.