Conditionally Formating Subtotal Output

I created a subtotal for my column group by right-clicking on the group and selecting subtotal. I would like to add the condition to the subtotal, that if the value is less than 0 to print zero and not the negative subtotal amount. Is there a way to do this? I haven't been able to find a way to add an expression to the greyed out subtotal field. Thanks.

I would get rid of the autogenerated subtotal, create another matrix that sums the field and conditionally edit the expression of the Sum matrix.

I have also found that the autogenerated subtotal feature has much to be desired.

Conditionally format subtotal for matrix report

I have a matrix that will pull out the current quarters sales figures. I want to change the color of the subtotal font ONLY when we are in the current period. I have a boolean field in the matrix report that is true when it is the current month.

For example, at the end of last month it displays January, February and March figures. I want the sub total to display the totals for January and February in white, whilst the totals for March are Yellow.

Any ideas anyone?

You can use expression for the subtotal style. Just check the value of your bool field in the expression, and return the corresponding color.|||

Unfortunately, that doesn't work.

I am using a condition in the color field of the subtotal properties checking the boolean value - and it ALWAYS thinks that the value is true, whether it is or not. Problem is that I have a mixture in my matrix - the previous 2 months hold false, the current month true and I am displaying all 3 months.

I am using the following in the color field in the properties of the subtotal:

=IIf(Fields!CurrentPeriod.Value, "Yellow", "White")

And it is showing yellow for all months.

Any pointers greatfully received

|||Hi there,

Did you find an answer to this? I am stuck with the same problem.

-Thanks a lot|||

Hi Ragas, Just try like this add your expression in differen place.Click in textbox and go to properties window,go to back ground color in that drop down list add your expression.

|||

Hi folks,

I have tried various methods for conditional formating in subtotal. Everything is working in development environment, but when i deploy and see the report in the http://ReportServer/reports, it is not being implemented. But when i print from the report from report server, i can see the conditional format !!!!

Can anyone give solution for this?

Regards,

karthik

|||

Hi there,

This worked for me:

=iif(inscope("Rating_Group") and inscope("RowRating_Group"),"white","Gainsboro")

Use in any of the attributes (Font, Color, Borderstyl) of the Data cell, in other words the cells that will contain the values of the matrix.

"Rating_Group" and "RowRating_Group" simply represent the Group which the subtotal belongs to. I had two subtotals on my report that's why I used both.

This can be a column group or a row group.

You should be able to format the subtotals to your heart's content with this beaut.

Conditionally format subtotal for matrix report

I have a matrix that will pull out the current quarters sales figures. I want to change the color of the subtotal font ONLY when we are in the current period. I have a boolean field in the matrix report that is true when it is the current month.

For example, at the end of last month it displays January, February and March figures. I want the sub total to display the totals for January and February in white, whilst the totals for March are Yellow.

Any ideas anyone?

You can use expression for the subtotal style. Just check the value of your bool field in the expression, and return the corresponding color.|||

Unfortunately, that doesn't work.

I am using a condition in the color field of the subtotal properties checking the boolean value - and it ALWAYS thinks that the value is true, whether it is or not. Problem is that I have a mixture in my matrix - the previous 2 months hold false, the current month true and I am displaying all 3 months.

I am using the following in the color field in the properties of the subtotal:

=IIf(Fields!CurrentPeriod.Value, "Yellow", "White")

And it is showing yellow for all months.

Any pointers greatfully received

|||Hi there,

Did you find an answer to this? I am stuck with the same problem.

-Thanks a lot|||

Hi Ragas, Just try like this add your expression in differen place.Click in textbox and go to properties window,go to back ground color in that drop down list add your expression.

|||

Hi folks,

I have tried various methods for conditional formating in subtotal. Everything is working in development environment, but when i deploy and see the report in the http://ReportServer/reports, it is not being implemented. But when i print from the report from report server, i can see the conditional format !!!!

Can anyone give solution for this?

Regards,

karthik

|||

Hi there,

This worked for me:

=iif(inscope("Rating_Group") and inscope("RowRating_Group"),"white","Gainsboro")

Use in any of the attributes (Font, Color, Borderstyl) of the Data cell, in other words the cells that will contain the values of the matrix.

"Rating_Group" and "RowRating_Group" simply represent the Group which the subtotal belongs to. I had two subtotals on my report that's why I used both.

This can be a column group or a row group.

You should be able to format the subtotals to your heart's content with this beaut.

Conditionally format subtotal for matrix report

I have a matrix that will pull out the current quarters sales figures. I want to change the color of the subtotal font ONLY when we are in the current period. I have a boolean field in the matrix report that is true when it is the current month.

For example, at the end of last month it displays January, February and March figures. I want the sub total to display the totals for January and February in white, whilst the totals for March are Yellow.

Any ideas anyone?

You can use expression for the subtotal style. Just check the value of your bool field in the expression, and return the corresponding color.|||

Unfortunately, that doesn't work.

I am using a condition in the color field of the subtotal properties checking the boolean value - and it ALWAYS thinks that the value is true, whether it is or not. Problem is that I have a mixture in my matrix - the previous 2 months hold false, the current month true and I am displaying all 3 months.

I am using the following in the color field in the properties of the subtotal:

=IIf(Fields!CurrentPeriod.Value, "Yellow", "White")

And it is showing yellow for all months.

Any pointers greatfully received

|||Hi there,

Did you find an answer to this? I am stuck with the same problem.

-Thanks a lot|||

Hi Ragas, Just try like this add your expression in differen place.Click in textbox and go to properties window,go to back ground color in that drop down list add your expression.

|||

Hi folks,

I have tried various methods for conditional formating in subtotal. Everything is working in development environment, but when i deploy and see the report in the http://ReportServer/reports, it is not being implemented. But when i print from the report from report server, i can see the conditional format !!!!

Can anyone give solution for this?

Regards,

karthik

|||

Hi there,

This worked for me:

=iif(inscope("Rating_Group") and inscope("RowRating_Group"),"white","Gainsboro")

Use in any of the attributes (Font, Color, Borderstyl) of the Data cell, in other words the cells that will contain the values of the matrix.

"Rating_Group" and "RowRating_Group" simply represent the Group which the subtotal belongs to. I had two subtotals on my report that's why I used both.

This can be a column group or a row group.

You should be able to format the subtotals to your heart's content with this beaut.

Conditionally format subtotal for matrix report

I have a matrix that will pull out the current quarters sales figures. I want to change the color of the subtotal font ONLY when we are in the current period. I have a boolean field in the matrix report that is true when it is the current month.

For example, at the end of last month it displays January, February and March figures. I want the sub total to display the totals for January and February in white, whilst the totals for March are Yellow.

Any ideas anyone?

You can use expression for the subtotal style. Just check the value of your bool field in the expression, and return the corresponding color.|||

Unfortunately, that doesn't work.

I am using a condition in the color field of the subtotal properties checking the boolean value - and it ALWAYS thinks that the value is true, whether it is or not. Problem is that I have a mixture in my matrix - the previous 2 months hold false, the current month true and I am displaying all 3 months.

I am using the following in the color field in the properties of the subtotal:

=IIf(Fields!CurrentPeriod.Value, "Yellow", "White")

And it is showing yellow for all months.

Any pointers greatfully received

|||Hi there,

Did you find an answer to this? I am stuck with the same problem.

-Thanks a lot|||

Hi Ragas, Just try like this add your expression in differen place.Click in textbox and go to properties window,go to back ground color in that drop down list add your expression.

|||

Hi folks,

I have tried various methods for conditional formating in subtotal. Everything is working in development environment, but when i deploy and see the report in the http://ReportServer/reports, it is not being implemented. But when i print from the report from report server, i can see the conditional format !!!!

Can anyone give solution for this?

Regards,

karthik

|||

Hi there,

This worked for me:

=iif(inscope("Rating_Group") and inscope("RowRating_Group"),"white","Gainsboro")

Use in any of the attributes (Font, Color, Borderstyl) of the Data cell, in other words the cells that will contain the values of the matrix.

"Rating_Group" and "RowRating_Group" simply represent the Group which the subtotal belongs to. I had two subtotals on my report that's why I used both.

This can be a column group or a row group.

You should be able to format the subtotals to your heart's content with this beaut.

sqlsql

Conditional formating in Subtotals?

I have subtotals in a matrix and I want to format the background color depending on the subtotal value - > 95 = "Green", < 95 > 90 = "Yellow", < 90 = "Red". I go to the Subtotal properties and put the iif expression in the background color, but it's not detecting the Subtotal value. How do I refer to the Subtotal value in a formating expression?

You have to put the background color expression directly on the matrix cell, similar to the approach described in this thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=648771&SiteID=1

-- Robert

|||That didn't work. I copied the example from the post and I set a different color for each of the three results. It set all the cells as "In Subtotal of entire matrix" color. The odd thing is, I also have a background image which shows up correctly in the preview, but the image doesn't show up when I render the report from a browser.|||

if you are doing alot of testing, make sure to refresh the report in the browser from the view to the print view. I have found that SSRS cache's data and unless a refresh is done it can still show previous view data.

Weird.

|||We need more information.

an iif statement will work, how are you computing the subtotals, is it in a group footer? if so something like

=iif( sum(fields!field.value) < Number, "Green", "Yellow")

should work...|||

Yes, I created the subtotals by right-clicking the row cell of the outer-most group and selecting 'subtotals'. The problem with the simple iif... is that it affects all the cells - not just the subtotals.

I did find out why my background image wasn't showing in the subtotals, if you set a background color in the Subtotal properties, it will overlay the background image from the cell properties.

|||I think the InScope solution may be the key to my problem, but for some reason, it isn't detecting the scope. Any ideas on what I've done wrong?|||

Robert was right. The InScope works, but I needed to add the matrix name and underscore to the row and columns group names since I had more than one matrix in the report.

Thanks Robert!

Conditional format within matrix, depending on subtotal?

Hi there,

I'm having trouble inserting a conditional format to a specific column.

e.g.: Matrix within the rows the "weeks" (1, 2, 3, ... , 52), and in the column a "lastyear revenu", "thisyear revenue" and a difference between them, "delta %", in percent grouped by the stores.
I added a subtotal to it so I get in the latest column the "total lastyear revenue", the "total thisyear revenue" and a difference between them in percent for all stores, "total delta %", for a specific week.

Problem: I want to colour the "delta %" column green when it is greater then the "total delta %" value.

I thougt this would be quite easy, but it really is a pain in the *** because, in the background expression dialog box, I can't refer to the subtotal cells ...

I tried to create a simple report from a cube with Month,Store,Turnover, Previous Year turnover and Delta %. Then I placed on the rows the months and the stores and the values in the colums (that should be the way that you did on the report, am I correct?) and I added the subtotal. Then in the background expression I wrote this:

"=iif(sum(Delta.values) > sum(Delta.values,"Dataset1"),"Green","White")"

Doing this I had the monthly delta background in green when it was higher than the total one.

I hope that I was clear enough!

|||So, am I getting this right:

You simply created another dataset in which you calculate the "total delta %". You then refer in the background expression dialog box to the "total delta %" field of the new dataset?
|||

The dataset is the same, I just refer to the whole dataset in the formula.

So, I have only one dataset (dataset1) and in the % delta for the background I use a formula like:

iif((sum(Fields!CYRevenue.value)-sum(Fields!PYRevenue.value))/sum(fields!PYRevenue.value) > (sum(Fields!CYRevenue.value,"Dataset1")-sum(Fields!PYRevenue.value,"Dataset1"))/sum(fields!PYRevenue.value,"Dataset1"),"Green","White")

Hope it helps!

Conditional format within matrix, depending on subtotal?

Hi there,

I'm having trouble inserting a conditional format to a specific column.

e.g.: Matrix within the rows the "weeks" (1, 2, 3, ... , 52), and in the column a "lastyear revenu", "thisyear revenue" and a difference between them, "delta %", in percent grouped by the stores.
I added a subtotal to it so I get in the latest column the "total lastyear revenue", the "total thisyear revenue" and a difference between them in percent for all stores, "total delta %", for a specific week.

Problem: I want to colour the "delta %" column green when it is greater then the "total delta %" value.

I thougt this would be quite easy, but it really is a pain in the *** because, in the background expression dialog box, I can't refer to the subtotal cells ...

I tried to create a simple report from a cube with Month,Store,Turnover, Previous Year turnover and Delta %. Then I placed on the rows the months and the stores and the values in the colums (that should be the way that you did on the report, am I correct?) and I added the subtotal. Then in the background expression I wrote this:

"=iif(sum(Delta.values) > sum(Delta.values,"Dataset1"),"Green","White")"

Doing this I had the monthly delta background in green when it was higher than the total one.

I hope that I was clear enough!

|||So, am I getting this right:

You simply created another dataset in which you calculate the "total delta %". You then refer in the background expression dialog box to the "total delta %" field of the new dataset?
|||

The dataset is the same, I just refer to the whole dataset in the formula.

So, I have only one dataset (dataset1) and in the % delta for the background I use a formula like:

iif((sum(Fields!CYRevenue.value)-sum(Fields!PYRevenue.value))/sum(fields!PYRevenue.value) > (sum(Fields!CYRevenue.value,"Dataset1")-sum(Fields!PYRevenue.value,"Dataset1"))/sum(fields!PYRevenue.value,"Dataset1"),"Green","White")

Hope it helps!

CONDITIONAL FORMAT IN SUBTOTAL

This is a multi-part message in MIME format.
--=_NextPart_000_0014_01C535DC.35B57990
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Hello!
I'm trying aply conditional format in subtotal fields belongings to a = Matrix. But it seems it doesn't work. I'd like to change the background = color each two rows , I'm using something like that:
=3Diif(RowNumber(Nothing) mod 2=3D1,"Black","White")
Anyone Knows why it doesn't work rowNumber in subtotal fields or any new = idea?
--=_NextPart_000_0014_01C535DC.35B57990
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Hello!

I'm trying aply conditional format in = subtotal fields belongings to a Matrix. But it seems it doesn't work. I'd like to = change the background color each two rows , I'm using something like = that:

= =3Diif(RowNumber(Nothing) mod 2=3D1,"Black","White")

Anyone Knows why it doesn't work = rowNumber in subtotal fields or any new idea?
--=_NextPart_000_0014_01C535DC.35B57990--I haven't tried this yet, but from using Report Services so far... the only
functions recognized in expressions for fields and such are global VB
Functions. So I think that's why RowNumber doesn't work, but I could be
wrong.
Maybe you could try putting your subtotals in a different matrix with a
query that actually calculates the subtotals... and also with said query add
the row numbers (using identity or whatnot) and use those for your formatting
expression.
"Estefania" wrote:
> Hello!
> I'm trying aply conditional format in subtotal fields belongings to a Matrix. But it seems it doesn't work. I'd like to change the background color each two rows , I'm using something like that:
> =iif(RowNumber(Nothing) mod 2=1,"Black","White")
> Anyone Knows why it doesn't work rowNumber in subtotal fields or any new idea?

Condition in Subtotal?

Hi
I have the following matrix
CA AZ

No surplus 11 5

---

Surplus 12 10

---

Zotal 100 50

---

Totlal 123 65

---

I want that only (No surplus and Surplus) sum include in total Can I apply This condition on Subtotal.

thanks in advance

Hi Yaseen,

Take a look to this post. This will help you out.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1315844&SiteID=1

Bernard Ong

Condition in Subtotal?

Hi
I have the following matrix
CA AZ

No surplus 11 5

---

Surplus 12 10

---

Zotal 100 50

---

Totlal 123 65

---

I want that only (No surplus and Surplus) sum include in total Can I apply This condition on Subtotal.

thanks in advance

Hi Yaseen,

Take a look to this post. This will help you out.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1315844&SiteID=1

Bernard Ong