Conditional formatting with Reporting Services

Question

Conditional formatting with Reporting Services

Adam Aspin

SSCarpal Tunnel

Points: 4900
More actions
May 5, 2009 at 12:26 am

#209089

Comments posted to this topic are about the item Conditional formatting with Reporting Services

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

Bob Griffin SSCertifiable Points: 5982 More actions · Answer 1

Glad to see the Reporting Services articles. This is a good one. Never would've thought of creating the formatting info back in the sproc...

Also nice use of the word "turgid." I had to look it up... 🙂

markmeiller SSC-Addicted Points: 446 More actions · Answer 2

An extra note, instead of typing in feild!col1.value you can use me.value and then apply the formatting to a lot of columns at once.

For example if you have several currency columns that need to be red if negative just select them all and in the color expression type iif(me.value<0,”red”,”black”)

Adam Aspin SSCarpal Tunnel Points: 4900 More actions · Answer 3

Thanks Mark - this sounds like a real timesaver.

Adam

kevlray SSCommitted Points: 1908 More actions · Answer 4

I was thrown off by the ";" with the first 'CASE' for the Commission Flag (I had not used CASE before). I have worked with Crystal Reports for years, even though the Report Builder is not as 'friendly' in a lot of ways as Crystal. It appears it is just as flexable so far.

Adam Aspin SSCarpal Tunnel Points: 4900 More actions · Answer 5

Oops! Sorry about the typo - the ";" in the first snippet before CASE should, of course, be a comma!

Chris Howarth-536003 SSCommitted Points: 1775 More actions · Answer 6

If you despise the report designer as much as I do you could even take it a step further by returning the colour codes/names by the stored proc. This way you avoid those nasty IIF statements, and the logic is kept in one place:

CASE

WHEN SP.Bonus = @Topseller THEN '#0000FF' --Blue

WHEN SP.Bonus = @Bottomseller THEN '#FF0000' --Red

ELSE '#000000' --Black

END AS TopBottomSellerColour

...or:

CASE

WHEN SP.Bonus = @Topseller THEN 'Blue'

WHEN SP.Bonus = @Bottomseller THEN 'Red'

ELSE 'Black'

END AS TopBottomSellerColour

The 'Colour' property of the textbox could then simply be set to:

=Fields!TopBottomSellerColour.Value

Chris

Matteo-317581 Old Hand Points: 338 More actions · Answer 7

Another Option would be to use the "Custom Code" area in the report properties to create a routine that would return a colour based on dollar value.

This way you do not have to burden the SP with this display-ish logic.

regards

Matteo

Fred Stemp SSChasing Mays Points: 645 More actions · Answer 8

Nice article. I do a lot of BI and KPI type reports and prefer to use a SWITCH statement on a flag instead of IIF statement:

=switch(Fields!mydata.Value = 1,"blue",

Fields!mydata.Value = 2,"green",

Fields!mydata.Value = 3,"orange",

Fields!mydata.Value = 4,"red")

Just an option I thought I'd share.

Frederick (Fred) J. Stemp, Jr.
Database Administrator / Database Developer
Dealer Funding, LLC

'...if they take my stapler then I'll set the building on fire...'

pitso.maceke Default port Points: 1467 More actions · Answer 9

Can I have a conditional format of each "Person Name".For instant each person have got a set target, Then I want to format the "Sales YTD" to quickly see if the guys are achieving their target or not?

Please assist guys

stacey doran Valued Member Points: 50 More actions · Answer 10

any ideas on how to format a cell when the cell in the previous row has a different value.

Fred Stemp SSChasing Mays Points: 645 More actions · Answer 11

What are you looking to do specifically?

If it's from a cell and you want just that value to change format on another cell in the Expression of the cell you want changed you can reference the other cell by using ReportItems!<cell name>.Value

But to really help, if you could provide an example of what you are trying to do, I maybe able to get you where you want to go 🙂

Frederick (Fred) J. Stemp, Jr.
Database Administrator / Database Developer
Dealer Funding, LLC

'...if they take my stapler then I'll set the building on fire...'

stacey doran Valued Member Points: 50 More actions · Answer 12

I have a Width column. I need to change the format of the cells in the Width column.

I would like the font to be Dark Blue if the Width value in the cell is different than the Width value in the cell in the previous row.

Fred Stemp SSChasing Mays Points: 645 More actions · Answer 13

Does the column's property allow it to auto grow? If not how are you setting the width property?

Frederick (Fred) J. Stemp, Jr.
Database Administrator / Database Developer
Dealer Funding, LLC

'...if they take my stapler then I'll set the building on fire...'

stacey doran Valued Member Points: 50 More actions · Answer 14

sorry, its confusing. The column value is for Width for a product. Like 12ft or 10ft or 8 ft. I want to change the font color propery of this.