Change display of subtotal row in Matrix report

I'd like to display the subtotal and Grand total rows of my Matrix report in
grey, but I can't find a proprties box to change. I've changed the area
that displays the text ie. "Grand Total", but there are not separate boxes
for the subtotal values like you'd see with a table. I also seem to be
stuck with my subtotal text in the second column when I'd like to print it in
the first column. I'd like to see
SubTotal xxxxx
Grand Total
I can't define in the same box.
I'm really "winging it" with this Matrix report. All my other report
experiences have been with tables.
Thank you in advance for any help you can provide.... PBOn Mar 13, 1:44 pm, ppbedz <ppb...@.discussions.microsoft.com> wrote:
> I'd like to display the subtotal and Grand total rows of my Matrix report in
> grey, but I can't find a proprties box to change. I've changed the area
> that displays the text ie. "Grand Total", but there are not separate boxes
> for the subtotal values like you'd see with a table. I also seem to be
> stuck with my subtotal text in the second column when I'd like to print it in
> the first column. I'd like to see
> SubTotal xxxxx
> Grand Total
> I can't define in the same box.
> I'm really "winging it" with this Matrix report. All my other report
> experiences have been with tables.
> Thank you in advance for any help you can provide.... PB
In the Layout view/tab, click on the matrix cell that has the
subtotals/grandtotal and select F4 (to get the Properties window).
Below Appearance, to the right of BackgroundColor select
'<Expressions...>' from the drop-down list and enter something like
the following:
=iif(Fields!FieldName.Value = "Subtotal" or Fields!FieldName.Value ="Grand Total", "LightGrey", "White")
Where LightGrey is the value if true and White is the color otherwise.
If you create the subtotal in your stored procedure or query that
sources the report, you can set the column using an expression
(similar to above); however, I believe. if you use the one created in
the report, it will force you to a specific column. Hope this helps.
Regards,
Enrique Martinez
Sr. SQL Server Developer|||Thank you. You have been a great help! I have 60 Finance reports to be
created with Visual Studio and no formal training. I am so grateful for
your answers to my posts! Can you recommend any other resources/discussion
groups that would be helpful? I did purchase the book "Hitchhikers Guide to
SQL Server 2000 Reporting Services". It has been helpful with the basics,
but not with the more kind of detailed "coding" that you have been helping me
with. I'd love to have a reference for creating "code" (I've created some
.code to check for division by zero, but it's been copied from other
references) and for the ".expressions" that can be used with a field names
(ie. .substring, .value, etc.)
Regards,
PB
"EMartinez" wrote:
> On Mar 13, 1:44 pm, ppbedz <ppb...@.discussions.microsoft.com> wrote:
> > I'd like to display the subtotal and Grand total rows of my Matrix report in
> > grey, but I can't find a proprties box to change. I've changed the area
> > that displays the text ie. "Grand Total", but there are not separate boxes
> > for the subtotal values like you'd see with a table. I also seem to be
> > stuck with my subtotal text in the second column when I'd like to print it in
> > the first column. I'd like to see
> >
> > SubTotal xxxxx
> >
> > Grand Total
> >
> > I can't define in the same box.
> >
> > I'm really "winging it" with this Matrix report. All my other report
> > experiences have been with tables.
> >
> > Thank you in advance for any help you can provide.... PB
> In the Layout view/tab, click on the matrix cell that has the
> subtotals/grandtotal and select F4 (to get the Properties window).
> Below Appearance, to the right of BackgroundColor select
> '<Expressions...>' from the drop-down list and enter something like
> the following:
> =iif(Fields!FieldName.Value = "Subtotal" or Fields!FieldName.Value => "Grand Total", "LightGrey", "White")
> Where LightGrey is the value if true and White is the color otherwise.
> If you create the subtotal in your stored procedure or query that
> sources the report, you can set the column using an expression
> (similar to above); however, I believe. if you use the one created in
> the report, it will force you to a specific column. Hope this helps.
> Regards,
> Enrique Martinez
> Sr. SQL Server Developer
>|||Mr Martinez will probably give you some more tips, but I just want to add a
link to William Pearson's articles on Reporting Serivces
http://www.databasejournal.com/article.php/1459531
You need to scroll down almost to the bottom, but there you find 38
tutorials. A lot of them are focused on OLAP based reports, but most of the
things you can do with OLAP based reports you can do with database based
reports as well.
Direct Reports (Brian Welcker's Weblog) at
http://blogs.msdn.com/bwelcker/default.aspx is good too. I also read Bob's
SQL Reporting Services Blog at http://blogs.msdn.com/bobmeyers/default.aspx
As for discussion groups, I usually end up with this news group, either
following it through a news reader, or searching with Google groups.
Can't give you a lot of tips on the code bit, though, haven't done too much
of that myself.
Kaisa M. Lindahl Lervik
http://blogs.spipp.net/blogs/kaisa
"ppbedz" <ppbedz@.discussions.microsoft.com> wrote in message
news:7F2266E8-5FC4-4000-B2C3-3C18641C47C3@.microsoft.com...
> Thank you. You have been a great help! I have 60 Finance reports to be
> created with Visual Studio and no formal training. I am so grateful for
> your answers to my posts! Can you recommend any other
> resources/discussion
> groups that would be helpful? I did purchase the book "Hitchhikers Guide
> to
> SQL Server 2000 Reporting Services". It has been helpful with the basics,
> but not with the more kind of detailed "coding" that you have been helping
> me
> with. I'd love to have a reference for creating "code" (I've created
> some
> .code to check for division by zero, but it's been copied from other
> references) and for the ".expressions" that can be used with a field names
> (ie. .substring, .value, etc.)
> Regards,
> PB
>
> "EMartinez" wrote:
>> On Mar 13, 1:44 pm, ppbedz <ppb...@.discussions.microsoft.com> wrote:
>> > I'd like to display the subtotal and Grand total rows of my Matrix
>> > report in
>> > grey, but I can't find a proprties box to change. I've changed the
>> > area
>> > that displays the text ie. "Grand Total", but there are not separate
>> > boxes
>> > for the subtotal values like you'd see with a table. I also seem to
>> > be
>> > stuck with my subtotal text in the second column when I'd like to print
>> > it in
>> > the first column. I'd like to see
>> >
>> > SubTotal xxxxx
>> >
>> > Grand Total
>> >
>> > I can't define in the same box.
>> >
>> > I'm really "winging it" with this Matrix report. All my other report
>> > experiences have been with tables.
>> >
>> > Thank you in advance for any help you can provide.... PB
>> In the Layout view/tab, click on the matrix cell that has the
>> subtotals/grandtotal and select F4 (to get the Properties window).
>> Below Appearance, to the right of BackgroundColor select
>> '<Expressions...>' from the drop-down list and enter something like
>> the following:
>> =iif(Fields!FieldName.Value = "Subtotal" or Fields!FieldName.Value =>> "Grand Total", "LightGrey", "White")
>> Where LightGrey is the value if true and White is the color otherwise.
>> If you create the subtotal in your stored procedure or query that
>> sources the report, you can set the column using an expression
>> (similar to above); however, I believe. if you use the one created in
>> the report, it will force you to a specific column. Hope this helps.
>> Regards,
>> Enrique Martinez
>> Sr. SQL Server Developer
>>|||On Mar 14, 8:04 am, "Kaisa M. Lindahl Lervik" <kais...@.hotmail.com>
wrote:
> Mr Martinez will probably give you some more tips, but I just want to add a
> link to William Pearson's articles on Reporting Serivces
> http://www.databasejournal.com/article.php/1459531
> You need to scroll down almost to the bottom, but there you find 38
> tutorials. A lot of them are focused on OLAP based reports, but most of the
> things you can do with OLAP based reports you can do with database based
> reports as well.
> Direct Reports (Brian Welcker's Weblog) athttp://blogs.msdn.com/bwelcker/default.aspxis good too. I also read Bob's
> SQL Reporting Services Blog athttp://blogs.msdn.com/bobmeyers/default.aspx
> As for discussion groups, I usually end up with this news group, either
> following it through a news reader, or searching with Google groups.
> Can't give you a lot of tips on the code bit, though, haven't done too much
> of that myself.
> Kaisa M. Lindahl Lervikhttp://blogs.spipp.net/blogs/kaisa
> "ppbedz" <ppb...@.discussions.microsoft.com> wrote in message
> news:7F2266E8-5FC4-4000-B2C3-3C18641C47C3@.microsoft.com...
> > Thank you. You have been a great help! I have 60 Finance reports to be
> > created with Visual Studio and no formal training. I am so grateful for
> > your answers to my posts! Can you recommend any other
> > resources/discussion
> > groups that would be helpful? I did purchase the book "Hitchhikers Guide
> > to
> > SQL Server 2000 Reporting Services". It has been helpful with the basics,
> > but not with the more kind of detailed "coding" that you have been helping
> > me
> > with. I'd love to have a reference for creating "code" (I've created
> > some
> > .code to check for division by zero, but it's been copied from other
> > references) and for the ".expressions" that can be used with a field names
> > (ie. .substring, .value, etc.)
> > Regards,
> > PB
> > "EMartinez" wrote:
> >> On Mar 13, 1:44 pm, ppbedz <ppb...@.discussions.microsoft.com> wrote:
> >> > I'd like to display the subtotal and Grand total rows of my Matrix
> >> > report in
> >> > grey, but I can't find a proprties box to change. I've changed the
> >> > area
> >> > that displays the text ie. "Grand Total", but there are not separate
> >> > boxes
> >> > for the subtotal values like you'd see with a table. I also seem to
> >> > be
> >> > stuck with my subtotal text in the second column when I'd like to print
> >> > it in
> >> > the first column. I'd like to see
> >> > SubTotal xxxxx
> >> > Grand Total
> >> > I can't define in the same box.
> >> > I'm really "winging it" with this Matrix report. All my other report
> >> > experiences have been with tables.
> >> > Thank you in advance for any help you can provide.... PB
> >> In the Layout view/tab, click on the matrix cell that has the
> >> subtotals/grandtotal and select F4 (to get the Properties window).
> >> Below Appearance, to the right of BackgroundColor select
> >> '<Expressions...>' from the drop-down list and enter something like
> >> the following:
> >> =iif(Fields!FieldName.Value = "Subtotal" or Fields!FieldName.Value => >> "Grand Total", "LightGrey", "White")
> >> Where LightGrey is the value if true and White is the color otherwise.
> >> If you create the subtotal in your stored procedure or query that
> >> sources the report, you can set the column using an expression
> >> (similar to above); however, I believe. if you use the one created in
> >> the report, it will force you to a specific column. Hope this helps.
> >> Regards,
> >> Enrique Martinez
> >> Sr. SQL Server Developer
You are very welcome. Glad to be of assistance. I can email you a kind
of 'how tos' list (that I have created along the way), if you would
like. This news group is the most active one (for Reporting Services),
so it usually gets the most responses and assistance. A couple of
lower activity ones are: http://groups.google.com/group/ReportingServices
and http://groups.google.com/group/RS2005. I tend to help out on those
as well. These Microsoft links are pretty handy (but not too easy to
navigate): http://msdn2.microsoft.com/en-us/library/ms159106.aspx and
http://msdn2.microsoft.com/en-us/library/ms170246.aspx. Also, if you
know of anyone that would be interested, I will be offering a SQL
Server 2005 Reporting Services class in the Nashville, TN area
starting the end of April.
Regards,
Enrique Martinez
Sr. SQL Server Developer|||Hi all!
I'm using the function SubToal built in RS.
How can I change the row's background for the row's handling the subtotals
infos?
Is there a way to find the value of a textboxX instead of the value of a
field?
Thanks, Julien
"ppbedz" <ppbedz@.discussions.microsoft.com> wrote in message
news:66E08C5E-4E81-49D4-9FA8-81616926508F@.microsoft.com...
> I'd like to display the subtotal and Grand total rows of my Matrix report
> in
> grey, but I can't find a proprties box to change. I've changed the area
> that displays the text ie. "Grand Total", but there are not separate
> boxes
> for the subtotal values like you'd see with a table. I also seem to be
> stuck with my subtotal text in the second column when I'd like to print it
> in
> the first column. I'd like to see
> SubTotal xxxxx
> Grand Total
> I can't define in the same box.
> I'm really "winging it" with this Matrix report. All my other report
> experiences have been with tables.
> Thank you in advance for any help you can provide.... PB|||Hi Julien,
I had the same problem and I solved it using the InScope() function.
You will find an example on this blog :
http://devauthority.com/blogs/chrisslatt/archive/2005/07/06/Reporting_Service_Using_InScope_to_do_custom_subtotals.aspx
Best regards,
Jean-Baptiste
"Julien Bonnier" wrote:
> Hi all!
> I'm using the function SubToal built in RS.
> How can I change the row's background for the row's handling the subtotals
> infos?
> Is there a way to find the value of a textboxX instead of the value of a
> field?
> Thanks, Julien
>
> "ppbedz" <ppbedz@.discussions.microsoft.com> wrote in message
> news:66E08C5E-4E81-49D4-9FA8-81616926508F@.microsoft.com...
> > I'd like to display the subtotal and Grand total rows of my Matrix report
> > in
> > grey, but I can't find a proprties box to change. I've changed the area
> > that displays the text ie. "Grand Total", but there are not separate
> > boxes
> > for the subtotal values like you'd see with a table. I also seem to be
> > stuck with my subtotal text in the second column when I'd like to print it
> > in
> > the first column. I'd like to see
> >
> > SubTotal xxxxx
> >
> > Grand Total
> >
> > I can't define in the same box.
> >
> > I'm really "winging it" with this Matrix report. All my other report
> > experiences have been with tables.
> >
> > Thank you in advance for any help you can provide.... PB
>
>|||FABOULOUS!!!
Thanks alot! I'm gonna add this to all my reports!
Thanks again!
Julien
"jbb92" <jbb92@.discussions.microsoft.com> wrote in message
news:905BD939-9C3E-4EBE-B002-7FFBC064752B@.microsoft.com...
> Hi Julien,
> I had the same problem and I solved it using the InScope() function.
> You will find an example on this blog :
> http://devauthority.com/blogs/chrisslatt/archive/2005/07/06/Reporting_Service_Using_InScope_to_do_custom_subtotals.aspx
> Best regards,
> Jean-Baptiste
> "Julien Bonnier" wrote:
>> Hi all!
>> I'm using the function SubToal built in RS.
>> How can I change the row's background for the row's handling the
>> subtotals
>> infos?
>> Is there a way to find the value of a textboxX instead of the value of a
>> field?
>> Thanks, Julien
>>
>> "ppbedz" <ppbedz@.discussions.microsoft.com> wrote in message
>> news:66E08C5E-4E81-49D4-9FA8-81616926508F@.microsoft.com...
>> > I'd like to display the subtotal and Grand total rows of my Matrix
>> > report
>> > in
>> > grey, but I can't find a proprties box to change. I've changed the
>> > area
>> > that displays the text ie. "Grand Total", but there are not separate
>> > boxes
>> > for the subtotal values like you'd see with a table. I also seem to
>> > be
>> > stuck with my subtotal text in the second column when I'd like to print
>> > it
>> > in
>> > the first column. I'd like to see
>> >
>> > SubTotal xxxxx
>> >
>> > Grand Total
>> >
>> > I can't define in the same box.
>> >
>> > I'm really "winging it" with this Matrix report. All my other report
>> > experiences have been with tables.
>> >
>> > Thank you in advance for any help you can provide.... PB
>>