master sysmessages: ssrs

Showing posts with label ssrs. Show all posts

Friday, March 23, 2012

Matrix Totals Problem - SSRS 2005

I wasn't able to get my totals working accurately in a table footer using SUM, so I though hmm, maybe try matrix...well, still same type of problems...a bit different but still, my totals are not totalling the columns!

Screen Shot:

http://www.photopizzaz.biz/matrix_totals_problem.jpg

I don't know what else to do, I give up at this point and need help from someone here who has already gone through these headaches!

I don't understand why BOL doesn't address more complicated issues such as this...they barely tell you anything there.

I believe you need to preface your column expressions with sum: ie: the expression under customer number would be: sum(fields!customernumber.Value)sql

Matrix Subtotals

I've just started using SSRS 2005 and am pretty impressed, however, I have
hit a stumbling block with Matrix sub totals, I have data displayed like so :
Client 1, 2006-01, Â£10000
Client 1, 2006-02, Â£15000
client2, 2006-01, Â£25000
client2, 2006-02, Â£10000
client2, 2006-03, Â£5000
(I have left out the pivoted data for simplicity - the above are all rows)
If I add a subtotal it totals all of the pivoted totals at the base of the
matrix but I actually want to subtotal on each client, for example, client1
would have a total of Â£25000 and Client2 a total of Â£40000. The subtotal is
grouped by creditorID ie Client1, Client2 so I'm not sure what I am missing.
Is it even possible to subtotal by each group? Any help appreciated
MarkHi Mark,
Thank you for your posting!
Based on my experience, you could do this. I assume you use the date field
as the row group. The easied way to do this is right-click the text of the
date field and click Subtotal. Then you could get the subtotal of date
field and grouped by the creditID field.
Hope this will be helpful!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Thanks a lot for that Wei, I'm getting very strange results - if I add a
subtotal to the CreditorID field it adds a grand total at the end of the
matrix, if I add it below the period / date field it gives a wildly
inaccurate figure. Could it be something to do with Scope - been searching
the net for Scope info but not much luck - I think I'll need to go back to
the drawing board on this one,
Can you recommend any good books or links which go into Matrix reports in
detail. Any further help greatfully received.
Mark
"Wei Lu [MSFT]" wrote:
> Hi Mark,
> Thank you for your posting!
> Based on my experience, you could do this. I assume you use the date field
> as the row group. The easied way to do this is right-click the text of the
> date field and click Subtotal. Then you could get the subtotal of date
> field and grouped by the creditID field.
> Hope this will be helpful!
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hi Mark,
Thank you for the update.
As for the subtotal issue, please send an email to me and I will send the
sample rdl file to you.
As for the books for Matrix report, you could refer the SQL Books online
help:
Working with Matrix Data Regions
http://msdn2.microsoft.com/en-us/library/ms157334(d=ide).aspx
My direct email address is weilu@.ONLINE.microsoft.com (please remove the
ONLINE when you send the email), you may send an email to me directly and I
will reply with the sample.
Please let me know the result. Thank you!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.

Matrix subtotal label issue (SSRS 2005)

Hello,

We have a matrix that includes two row groups with subtotals for each group, like the following:

<tr><td> </td><td>Rm 34A Total</td><td>10</td></tr>

<tr><td> </td><td>Rm 34A Total</td><td>7</td></tr>

<tr><td>Unit 1 Total</td><td> </td><td>17</td></tr>

</table>

The issue is that the second group's subtotal label doesn't display the correct name, as in the example. It's always "Rm 34A" or whichever room is returned first in the dataset, even if the subtotal is actually for Rm 35. The labels for the first group display correctly, and the issue is only a label problem - the subtotal data seems to be fine. For the subtotal label, we have =Fields!Subgroup.Value + " Total" as the expression. Anybody have any suggestions? Thanks,

RLG

RLGow wrote:

Hello,
We have a matrix that includes two row groups with subtotals for each group, like the following:
<table width="80%">
<tr><td align="center">Unit</td><td align="center">Room</td><td align="center">Data</td></tr>
<tr><td>Unit 1</td><td>Rm 34A</td><td>6</td></tr>
<tr><td> </td><td> </td><td>4</td></tr>
<tr><td> </td><td>Rm 34A Total</td><td>10</td></tr>
<tr><td> </td><td>Rm 50</td><td>7</td></tr>
<tr><td> </td><td>Rm 34A Total</td><td>7</td></tr>
<tr><td>Unit 1 Total</td><td> </td><td>17</td></tr>
</table>
The issue is that the second group's subtotal label doesn't display the correct name, as in the example. It's always "Rm 34A" or whichever room is returned first in the dataset, even if the subtotal is actually for Rm 35. The labels for the first group display correctly, and the issue is only a label problem - the subtotal data seems to be fine. For the subtotal label, we have =Fields!Subgroup.Value + " Total" as the expression. Anybody have any suggestions? Thanks,
RLG

Try

=Fields!Subgroup.Value & " Total"

|||

Hi,

I also have the same problem.

However, I have discovered that this behaviour appears then you have column-subtotals and row-subtotal.

If I remove the subtotal for rows this problem seems to disappear.

Instead I get another problems with empty labels in my subtotal text-fields. There are no NULL or empty strings in used columns in my recordset!

If you have any kind of solution or work around please let me know.

Regards, Jonas

Matrix subtotal label issue (SSRS 2005)

Hello,

We have a matrix that includes two row groups with subtotals for each group, like the following:

<tr><td> </td><td>Rm 34A Total</td><td>10</td></tr>

<tr><td> </td><td>Rm 34A Total</td><td>7</td></tr>

<tr><td>Unit 1 Total</td><td> </td><td>17</td></tr>

</table>

RLG

RLGow wrote:

Hello,

We have a matrix that includes two row groups with subtotals for each group, like the following:

<table width="80%">

<tr><td align="center">Unit</td><td align="center">Room</td><td align="center">Data</td></tr>

<tr><td>Unit 1</td><td>Rm 34A</td><td>6</td></tr>

<tr><td> </td><td> </td><td>4</td></tr>

<tr><td> </td><td>Rm 34A Total</td><td>10</td></tr>

<tr><td> </td><td>Rm 50</td><td>7</td></tr>

<tr><td> </td><td>Rm 34A Total</td><td>7</td></tr>

<tr><td>Unit 1 Total</td><td> </td><td>17</td></tr>

</table>

The issue is that the second group's subtotal label doesn't display the correct name, as in the example. It's always "Rm 34A" or whichever room is returned first in the dataset, even if the subtotal is actually for Rm 35. The labels for the first group display correctly, and the issue is only a label problem - the subtotal data seems to be fine. For the subtotal label, we have =Fields!Subgroup.Value + " Total" as the expression. Anybody have any suggestions? Thanks,

RLG

Try

=Fields!Subgroup.Value & " Total"

|||

Hi,

I also have the same problem.

However, I have discovered that this behaviour appears then you have column-subtotals and row-subtotal.

If I remove the subtotal for rows this problem seems to disappear.

Instead I get another problems with empty labels in my subtotal text-fields. There are no NULL or empty strings in used columns in my recordset!

If you have any kind of solution or work around please let me know.

Regards, Jonas

Monday, March 19, 2012

Matrix report 4th row group subtotal row color

I'm dealing w/ SSRS 2005.

I have my main matrix report which has five row groups.

What I'd like to do is have the subtotal at the 4th level have a coloring for the whole row at run-time...so the user can follow from left to right what the 4th level subtotal actually is (the report can get fairly wide).

At design time, you don't even see the rows to the right of the subtotal, you just see the subtotal box.

Thanks!

got it...click on the little green triangle in the upper right corner, and set the background property.

Matrix Question

Hello,
I have been working away with the SSRS and am getting pretty comfortable
with some of it's features, benefits and limitations. I have created a
series of Matrix reports (pivot tables) that show sales from 2004, 2005,
2006, 2007 and even future sales of 2008. My goal is to have a comparison of
2004 versus 2005; 2005 versus 2006 and so on. In Excel I understand how to
acomplish this goal but I don't see how I acomplish this calculation in a
matrix. My field for the year is 'year(eve_date)'.
Thanks in advance - I'm sure it's simply and I'm just being brain dead.
ChrisOn May 9, 2:32 pm, "Chris Marsh" <cma...@.synergy-intl.com> wrote:
> Hello,
> I have been working away with the SSRS and am getting pretty comfortable
> with some of it's features, benefits and limitations. I have created a
> series of Matrix reports (pivot tables) that show sales from 2004, 2005,
> 2006, 2007 and even future sales of 2008. My goal is to have a comparison of
> 2004 versus 2005; 2005 versus 2006 and so on. In Excel I understand how to
> acomplish this goal but I don't see how I acomplish this calculation in a
> matrix. My field for the year is 'year(eve_date)'.
> Thanks in advance - I'm sure it's simply and I'm just being brain dead.
> Chris
I would suggest handling this functionality in the query/stored
procedure that is sourcing the report. I normally use while loops or
cursors to accomplish this. Hope this is helpful.
Regards,
Enrique Martinez
Sr. Software Consultant|||Thanks - I hoped that this could be done on the report but we can try the
query idea.
"EMartinez" <emartinez.pr1@.gmail.com> wrote in message
news:1178755079.558957.64180@.y80g2000hsf.googlegroups.com...
> On May 9, 2:32 pm, "Chris Marsh" <cma...@.synergy-intl.com> wrote:
>> Hello,
>> I have been working away with the SSRS and am getting pretty comfortable
>> with some of it's features, benefits and limitations. I have created a
>> series of Matrix reports (pivot tables) that show sales from 2004, 2005,
>> 2006, 2007 and even future sales of 2008. My goal is to have a comparison
>> of
>> 2004 versus 2005; 2005 versus 2006 and so on. In Excel I understand how
>> to
>> acomplish this goal but I don't see how I acomplish this calculation in a
>> matrix. My field for the year is 'year(eve_date)'.
>> Thanks in advance - I'm sure it's simply and I'm just being brain dead.
>> Chris
>
> I would suggest handling this functionality in the query/stored
> procedure that is sourcing the report. I normally use while loops or
> cursors to accomplish this. Hope this is helpful.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>|||Best possible is to bring from query, if there is any limitations on writing
the query or access etc... try calculated fields
Amarnath
"Chris Marsh" wrote:
> Thanks - I hoped that this could be done on the report but we can try the
> query idea.
> "EMartinez" <emartinez.pr1@.gmail.com> wrote in message
> news:1178755079.558957.64180@.y80g2000hsf.googlegroups.com...
> > On May 9, 2:32 pm, "Chris Marsh" <cma...@.synergy-intl.com> wrote:
> >> Hello,
> >>
> >> I have been working away with the SSRS and am getting pretty comfortable
> >> with some of it's features, benefits and limitations. I have created a
> >> series of Matrix reports (pivot tables) that show sales from 2004, 2005,
> >> 2006, 2007 and even future sales of 2008. My goal is to have a comparison
> >> of
> >> 2004 versus 2005; 2005 versus 2006 and so on. In Excel I understand how
> >> to
> >> acomplish this goal but I don't see how I acomplish this calculation in a
> >> matrix. My field for the year is 'year(eve_date)'.
> >>
> >> Thanks in advance - I'm sure it's simply and I'm just being brain dead.
> >>
> >> Chris
> >
> >
> > I would suggest handling this functionality in the query/stored
> > procedure that is sourcing the report. I normally use while loops or
> > cursors to accomplish this. Hope this is helpful.
> >
> > Regards,
> >
> > Enrique Martinez
> > Sr. Software Consultant
> >
>
>

Monday, March 12, 2012

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.

Friday, March 9, 2012

matrix column grouping collapse

Hi Folks
Going a little crazy with this one...
Have a SSRS 2K SP1 matrix report which is highly similar to the company
sales report example. As a matter of act, the column headings are identical.
Have no issues with the row groupings expanding and showing initially as
collapsed, but for the life of me, I can not get the columns (YEAR/QUARTER)
to rendering as collapsed or have any of this functionality.
Having groupings working fine for both row/column, but the collapsed for
columns just doesn't seem to be functional?
thanks
robgot it...
solution was found here:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rshowto/htm/hrs_designer_v1_0zvx.asp
rob
"Rob" wrote:
> Hi Folks
> Going a little crazy with this one...
> Have a SSRS 2K SP1 matrix report which is highly similar to the company
> sales report example. As a matter of act, the column headings are identical.
> Have no issues with the row groupings expanding and showing initially as
> collapsed, but for the life of me, I can not get the columns (YEAR/QUARTER)
> to rendering as collapsed or have any of this functionality.
> Having groupings working fine for both row/column, but the collapsed for
> columns just doesn't seem to be functional?
> thanks
> rob

Friday, March 23, 2012

Matrix Totals Problem - SSRS 2005

Matrix Subtotals

Matrix subtotal label issue (SSRS 2005)

Matrix subtotal label issue (SSRS 2005)

Monday, March 19, 2012

Matrix report 4th row group subtotal row color

Matrix Question

Monday, March 12, 2012

Matrix Control in SSRS

Friday, March 9, 2012

matrix column grouping collapse

master sysmessages

Blog Archive

About Me