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 Formatting With DateDiff

I am attemting to write an expression that changes the background color if the difference between two dates is less than 60 days. I have this expression but it does not work. The color never changes.

=IIF (DateDiff("Day", Fields!pract_start_date.Value, Fields!project_start_date.Value) < 60,"Yellow","White")

Maybe you should wrap the DateDiff() with an ABS() in case the dates are in the wrong order in your function, resulting in a DateDiff of -60 rather than 60?

|||

That's exactly what was wrong. I had them in the wrong order. I thought I had checked for that but, evidently, did not. Thanks.

Conditional Formatting Problem when Rendering to Excel vs. PDF/HTML

Hello,

I need some quick help. I have conditional formating for color in some of the cells in my reports. When I render the reports to HTML or PDF, colors work great. When I render the report to Excel, all the cells come through in one color. How do I fix this in RS 2000? Or how do make the rendered Excel file just one specific color, like black?

Hi,

There was a similar problem with the hyperlinks used in the reports.

When HTML or I guess .pdf works fine Excel was not working as desired.

Targeting the output format that will be generally rendered by users was my solution then.

Eralper

Conditional Formatting on Datetime Field

I am building a report with a query that includes a field of type datetime.
I would like to change the color of the text in this column to RED if the
value contained in the field is earlier than now() - 10 minutes. I would
appreciate direction as to the appropriate function to use in this scenario.
I have tried to work with datediff, but without positive results.
Regards,
Pete Zerger, MCSE(Messaging)
Co-founder and Webmaster, MOMReourcs.org
URL:http://www.momresources.org
mailto:pete.zerger@.gmail.comWill something like this work for you?
=iif(Fields!EventDate.Value < dateadd("n", -10, Now()), "Red", "Black")
Regards,
Dan

Conditional Formatting - not so conditional?

I have the following code in the color property of a textbox. However, when I run my report all of the values in this column display in green regardless of their value.

=SWITCH(Fields!Wrap.Value >= 3, "Red", Fields!Wrap.Value < 3, "Green")

I already tried =iif(Fields!Wrap.Value >= 3 , "Red", "Green") and got the same results.

Is it because this is a matrix report? What am I doing wrong?

Thanks in advance . . .What part of the matrix do you have this in, and what values show in the textbox?|||OK, well you pointed me right to my problem. The values are percentages. I was doing my conditional formatting based on 3 instead of .03 . . .

That is fixed but I still have something else wrong. A very small number of values that are not .03 are coloring red. I am also getting a warning that says "[rsNonAggregateInMatrixCell] The Color expression for the textbox ‘textbox22’ references a field outside an aggregate function. Value expressions in matrix cells should be aggregates, to allow for subtotaling."

I assume this message is related to my problem.

Here is a sample of what the output is looking like. All of the values formatted correctly except for the 2.60 %. The 2.60% was colored red as if it was >= .03. I know you can't see the colors here:

WRAP

3.50 %

1.22 %

1.15 %

3.13 %

2.43 %

2.60 %

14.21 %

0.00 %

8.41 %

6.14 %

5.23 %

5.23 %

2.42 %

2.42 %

1.87 %

1.87 %

2.39 %

2.39 %

4.17 %

4.17 %

3.15 %

|||

A matrix cell is always in the intersection of a row and a column group. Usually, several detail rows will go into the same matrix cell instance based on the row/column grouping. That's the reason why you get the warning. By just referencing a field without aggregate function (=iif(Fields!Wrap.Value >= 0.03 , "Red", "Green") ), you reference just the field value of the first row in that cell - while the textbox in the cell may actually show the aggregate of the values.

You should use the following color expression instead: =iif(Sum(Fields!Wrap.Value) >= 0.03 , "Red", "Green")

-- Robert

|||I made the change that you suggested. Which eliminates the warning that I was receiving. However, I am still getting inconsistent results with the formatting. For instance, of the following values only 5.09, and the two 0.00's are colored green.

Could it be because I have enabled drill-down in this report? Even if I drill down to the lowest level the colors still are not consistently populating based on the conditions in this formula.

WRAP

5.09 %

2.73 %

0.00 %

0.00 %

1.77 %

2.55 %

3.61 %

6.68 %

3.02 %

3.50 %

2.86 %

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 Formating in Reporting Services 2005

Hello,
I have a problem formating a line in a diagram in SSRS 2005. I want to change the color of the line depending on a value on the x-axis.

I always get an exception that the bordercolor expression for the diagram Object 'Bestandsentwicklung.DataPoint' contains an error. The entry string has the wrong format.

The function is the following:
=IIF(Parameters!CurrentWeek.Value.ToString > cdbl(Fields!DimZeit_WocheTag_Woche_MEMBER_KEY.Value.ToString),"Firebrick","Blue")
whereby the parameter currentweek should return a number and the field DimZeit_WocheTag_Woche_MEMBER_KEY, too.

Can anyone help me? How do I have to convert the values to solve the problem?

Stefoon

The error is because you are comparing a string with a double data type. Do not use ToString for CurrentWeek parameter, instead use CDbl on both sides of your expression in IIf.

Shyam

|||

Hi Shyam,

you are right. But it does not work either.

Stefoon

Conditional Format

I have a report that I would like to highlight some rows in it. I would have
to change the backgound color and set font to bold. The backgound color and
the flag to bold or not would be return by the stored procedure with the
data. For example:
'Gross Sales', 100, 'LightGrey', 'Bold'
'Net Sales', 80, '','' or 'Net Sales', 80, 'Transparent','Normal'
Is there a way to set formatting conditionally like that based on data
returned? Any help is appreciated.
Thanks,
Carmen.Hi Carmen
I assume that you are using a table to provide these rows. If you select
the row and click on the Background Color property, the top option is
'<expression>'. Selecting this takes you to a window that allows you to
access fields from your dataset.
something like '=Fields!BackgroundColor' entered here should do what you are
looking for, provided that your stored procedure returns a value that
definitely matches a color selection.
A similar thing can be done under the Font>FontWeight property for the row.
All the best,
Ed Allison
"Carmen" <Carmen@.discussions.microsoft.com> wrote in message
news:9BFFBF24-99D9-4D05-9C64-C4E0466337BC@.microsoft.com...
>I have a report that I would like to highlight some rows in it. I would
>have
> to change the backgound color and set font to bold. The backgound color
> and
> the flag to bold or not would be return by the stored procedure with the
> data. For example:
> 'Gross Sales', 100, 'LightGrey', 'Bold'
> 'Net Sales', 80, '','' or 'Net Sales', 80, 'Transparent','Normal'
> Is there a way to set formatting conditionally like that based on data
> returned? Any help is appreciated.
> Thanks,
> Carmen.|||They will not bind up automatically based on name... So in the Gross Sales,
background color expression you might place. (ie the query colum names
Select col, whatever, backcolor, fontweight from yourtable..)
=Fields!backcolor.Value
If the backcolor value might be empty you can provide a default value ie
=IIF(Fields!backcolor.Value is nothing,
'SomeDefaultColor',Fields!backcolor.Value)
(it would probably be better to provide a default back color in the query,
so it can be changed more easily than going into each expression tho.)
Hope this helps...
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Carmen" wrote:
> I have a report that I would like to highlight some rows in it. I would have
> to change the backgound color and set font to bold. The backgound color and
> the flag to bold or not would be return by the stored procedure with the
> data. For example:
> 'Gross Sales', 100, 'LightGrey', 'Bold'
> 'Net Sales', 80, '','' or 'Net Sales', 80, 'Transparent','Normal'
> Is there a way to set formatting conditionally like that based on data
> returned? Any help is appreciated.
> Thanks,
> Carmen.|||Thanks Ed and Wayne for your responses. I decided to manually (in the report)
set backgound and bold since joining two queries on the report seemed a
little too complicated. However, I might use you guys suggestion in the
future.
Thanks again,
Carmen.
"Wayne Snyder" wrote:
> They will not bind up automatically based on name... So in the Gross Sales,
> background color expression you might place. (ie the query colum names
> Select col, whatever, backcolor, fontweight from yourtable..)
> =Fields!backcolor.Value
> If the backcolor value might be empty you can provide a default value ie
> =IIF(Fields!backcolor.Value is nothing,
> 'SomeDefaultColor',Fields!backcolor.Value)
> (it would probably be better to provide a default back color in the query,
> so it can be changed more easily than going into each expression tho.)
> Hope this helps...
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> I support the Professional Association for SQL Server ( PASS) and it''s
> community of SQL Professionals.
>
> "Carmen" wrote:
> > I have a report that I would like to highlight some rows in it. I would have
> > to change the backgound color and set font to bold. The backgound color and
> > the flag to bold or not would be return by the stored procedure with the
> > data. For example:
> >
> > 'Gross Sales', 100, 'LightGrey', 'Bold'
> > 'Net Sales', 80, '','' or 'Net Sales', 80, 'Transparent','Normal'
> >
> > Is there a way to set formatting conditionally like that based on data
> > returned? Any help is appreciated.
> >
> > Thanks,
> >
> > Carmen.

Conditional expressions for background color

I am trying to use conditional expressions within a report field to change
the background color depending on the value of one of the fields in the
report.
Within the properties for one of the report items, I click the background
color item and then choose the expressions and that brings up an edit
expression window. I key the following code into the expression pane.
=iff(fields!libseq.value > 2, "Red", "Blue")
when I rebuild the solution or try to run the report, I get the following
message.
C:\VS Test Applications\Report Testing\Report4.rdl The background color
expression for the textbox 'textbox10' contains an error: [BC30451] Name
'iff' is not declared.
Does anyone know what needs to be declared and where?
thanks,
hughIt shoud be IIf and not iff
--
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"Hugh O" <HughLD@.newsgroup.nospam> wrote in message
news:uv82bHbgGHA.4144@.TK2MSFTNGP02.phx.gbl...
>I am trying to use conditional expressions within a report field to change
>the background color depending on the value of one of the fields in the
>report.
> Within the properties for one of the report items, I click the background
> color item and then choose the expressions and that brings up an edit
> expression window. I key the following code into the expression pane.
> =iff(fields!libseq.value > 2, "Red", "Blue")
> when I rebuild the solution or try to run the report, I get the following
> message.
> C:\VS Test Applications\Report Testing\Report4.rdl The background color
> expression for the textbox 'textbox10' contains an error: [BC30451] Name
> 'iff' is not declared.
> Does anyone know what needs to be declared and where?
> thanks,
> hugh
>|||Hi Hugh,
Thank you for your post.
As Jasper mentioned, the function you use should be Iif not Iff.
Iif is a Visual Basic Fuction. Here is an article for your reference.
Expression Examples in Reporting Services
http://msdn2.microsoft.com/en-us/library/ms157328.aspx
If you have questions or concerns, please feel free to let me know.
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.

conditional color property based on date

I want to use an IIF statement to set the color property for a text box.
Here is my code that is not working:
=IIf(DateAdd('m', -6, Fields!Orig_expireDate.Value) <
Now(), "red", "black")Use " instead of '
( ' is a comment for vb, even in reporting services)
Mike G.
"ladydi_1226" <ladydi1226@.discussions.microsoft.com> wrote in message
news:92B486DB-EFFA-49BC-AEE3-76CBBF4A24AE@.microsoft.com...
>I want to use an IIF statement to set the color property for a text box.
> Here is my code that is not working:
> =IIf(DateAdd('m', -6, Fields!Orig_expireDate.Value) <
> Now(), "red", "black")

Conditional color in reports?

I have reports in Grids and Matrix formats. They look ugly!
I'd like to be able to have type color change if a value is negative.
I'd like to be able to set the entire row's color based off of group code.
Can this be done, and how?
TIAYou can write an expression for the background color of the row.
Read this for more info:
http://msdn2.microsoft.com/en-us/library/ms159238.aspx
> I have reports in Grids and Matrix formats. They look ugly!
> I'd like to be able to have type color change if a value is negative.
> I'd like to be able to set the entire row's color based off of group
> code. Can this be done, and how?
> TIA
>

conditional color formatting

i'm using SQL Server Reporting Services 2005 and have a simple report that
essentially pulls back a bunch of database records and displays the results
in the excel-like grid. I want to shade an entire row, based on the value
of a field contained in that row, but i can't figure out where (and how) to
do this.
any help is much appreciated..
tia
jtOn Apr 3, 6:26 pm, "JTL" <j...@.clickstreamtech.com> wrote:
> i'm using SQL Server Reporting Services 2005 and have a simple report that
> essentially pulls back a bunch of database records and displays the results
> in the excel-like grid. I want to shade an entire row, based on the value
> of a field contained in that row, but i can't figure out where (and how) to
> do this.
> any help is much appreciated..
> tia
> jt
In the Layout view, select the left-most cell in the table and click
F4 (for the Properties window). Below the Appearance property, in the
Properties window, select '<Expression...>' from the drop-down list to
the right of 'BackgroundColor.' Enter something like the following
below 'Edit Expression:'
=iif(Fields!FieldName.Value <> SomeValue, "BackgroundColorIfTrue",
"BackgroundColorIfFalse")
Then select the cell directly to the right of the left-most one and
repeat the same steps. Continue in this manner until all cells all the
way across have the same expression used.
Regards,
Enrique Martinez
Sr. Software Consultant

conditional background color

Hello,

Is it possible to do conditional background color formatting on Matrix?

Thanks,

You can do that by setting the background color to the following expression:
=IIF(RunningValue(Fields!YourField.Value, CountDistinct, Nothing) Mod 2 = 1, "Color1", "Color2")
RunningValue returns an aggregate. In this case, it will keep counting "YourField" values:
YourField | Result of "RunningValue(Fields!YourField.Value, CountDistinct, Nothing)"
1 | 1
5 | 2
23 | 3
etc
If you would replace "CountDistinct" with for example "Sum", it would work like:
1 | 1
5 | 6
23 | 29
etc
The rest of the expression should be self explanatory.