How to format the subtotals of a Reporting Services matrix differently, using InScope

SSRS: Removing the Navigation link using an Expression

Rob Farley — Tue, 04 Aug 2009 13:23:00 GMT

There are times when you want to have a Navigation property of a textbox (typically providing a link

re: How to format the subtotals of a Reporting Services matrix differently, using InScope

Andrew — Tue, 16 Jun 2009 22:18:46 GMT

Great! Thanks. InScope was just what I needed.

re: How to format the subtotals of a Reporting Services matrix differently, using InScope

Rob Farley — Wed, 28 Jan 2009 23:44:25 GMT

Oh, and the green corner is useful for when you want to change properties of all the SubTotal boxes - but sometimes you want more control, and InScope can help with that.

re: How to format the subtotals of a Reporting Services matrix differently, using InScope

Rob Farley — Wed, 28 Jan 2009 23:42:48 GMT

Marty - To change the format to bold, look for the FontWeight property.

re: How to format the subtotals of a Reporting Services matrix differently, using InScope

Marty — Wed, 28 Jan 2009 21:05:14 GMT

Two questions, why does the green corner right click trick work for some and not others?

Second, I was able to change the background colour, how would I change the format of the text in the subtotal to bold?

re: How to format the subtotals of a Reporting Services matrix differently, using InScope

leonie — Wed, 21 Jan 2009 14:51:59 GMT

oh my god. like others i have been banging my head against my desk over this for a while... thanks for explaining it in a way that actually makes sense!

re: How to format the subtotals of a Reporting Services matrix differently, using InScope

Rob Farley — Tue, 23 Dec 2008 02:08:43 GMT

Ok. Try using the Scope parameter of aggregate functions Sum and Max to get the max of the Sums per thing you're subtotalling on. You can use it in a similar way to how the OVER clause works in T-SQL.

re: How to format the subtotals of a Reporting Services matrix differently, using InScope

ismail — Mon, 22 Dec 2008 10:17:22 GMT

Hi Rob,

Thank you for your answer but unfortunately it didn't work because :

1) I got an error (the textbox must be placed into the header or the footer of the report),

2) When I placed the textbox into the footer of the report, i got no answer (textbox with empty value).

I'm able to reach the subtotal row thanks to this expression (mentionned in the body of my matrix) :

=iif(InScope("matrix1_Year"),

"Black",

iif(InScope("matrix1_Campaign_Status"),

"Black",

iif(Me.Value = Max(Fields!Number_Opportunity.Value,"DataSource1"), "Red", "Black")

))

But the colored cell is wrong and I don't know why.

Any help ?

Thanks in advance

re: How to format the subtotals of a Reporting Services matrix differently, using InScope

Rob Farley — Fri, 19 Dec 2008 23:52:39 GMT

Try putting a textbox outside the matrix, containing Max(ReportItems!SubTotal.Value) (or something similar).

Then you should be able to test to see if the value in the matrix is the same as the value in the textbox, and color it accordingly.

Hope this helps,

Rob

[SSRS 2005] Maximum value of a matrix subtotal row

Ismail — Fri, 19 Dec 2008 15:44:20 GMT

Hello,

I have a subtotal row (automatically generated) in my matrix and i would like to color its maximum value in red.

I was wondering if the right way to do this was to use the Max() predefined function but unfortunately it didn't work.

How can i do this ?

Thank you in advance for your help

re: How to format the subtotals of a Reporting Services matrix differently, using InScope

Rob Farley — Fri, 12 Dec 2008 01:14:53 GMT

Then I would consider having a separate column, which could maybe be hidden... which tests to see if it's numeric before displaying it. Then you can use that total instead of the original one.

Rob

re: How to format the subtotals of a Reporting Services matrix differently, using InScope

Cesar Ochoa — Thu, 11 Dec 2008 19:37:45 GMT

Just an kind off topic question: Let's say that I have a matrix whit 5 columns and I have one subtotal row, the thing is that I only want the totals of the numeric columns, not the string ones, is it possible to hide these string columns?

re: How to format the subtotals of a Reporting Services matrix differently, using InScope

Brenda Keller — Thu, 30 Oct 2008 15:39:50 GMT

Please ignore my previous comment about the Static Group. My Matrix has two named groups and I found that if I used the InScope function to look for items outside either named group, the formatting worked correctly.

re: How to format the subtotals of a Reporting Services matrix differently, using InScope

Brenda Keller — Thu, 30 Oct 2008 13:56:16 GMT

I have created a matrix and have used the Add Row command to include additional aggregations within the matrix. Using the Add Row command created a Group called [Static Group] within the Rows groupings of the matrix. I am able to format the first row group within my matrix, since this is part of one of the named groups I created. However, the additional rows in my matrix are part of the [Static Group] and do not respond when I use the InScope function. Does anyone know how to format rows in a matrix which are part of a Static Group (or static row)? Thanks in advance.

re: How to format the subtotals of a Reporting Services matrix differently, using InScope

Rob Farley — Thu, 30 Oct 2008 02:35:40 GMT

Try the green corner bit, and then set it there. But otherwise, you can just use InScope like this:

=iif(inscope("matrix1_SalesPersonID"), "White", "Gray")

re: How to format the subtotals of a Reporting Services matrix differently, using InScope

Jim Kiley — Wed, 29 Oct 2008 19:08:56 GMT

what happens if I can't base the expression (like percentages) on a value and I just want the background color gray for the enitre subtotal row?

re: How to format the subtotals of a Reporting Services matrix differently, using InScope

Rob Farley — Sat, 11 Oct 2008 06:30:36 GMT

I think I know what you mean... Try putting a Rectangle into your table cell, and then putting textboxes into the rectangle. You should be able to have a much better degree of flexibility this way.

Rob

display data in report rdlc

santosh upreti — Fri, 10 Oct 2008 10:57:42 GMT

how can we display 3, 4column names from the table in a report column vertically nd the corresponding values of that columns is just to the next of the report column

plz help me its urgent

re: How to format the subtotals of a Reporting Services matrix differently, using InScope

Rob Farley — Sun, 31 Aug 2008 08:39:01 GMT

You can still do the standard visibility stuff, and use expressions in there too. If you're worried, then get it working in a table and transfer the logic to the column.

re: How to format the subtotals of a Reporting Services matrix differently, using InScope

Swapna — Fri, 29 Aug 2008 14:57:48 GMT

How to collapse the data in the matrix based on the column value?