Conditional formatting with Reporting Services

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Adam Aspin » Conditional formatting with Reporting...

17 posts, Page 1 of 2

1 2 »»

Conditional formatting with Reporting Services

Rate Topic

Display Mode

Topic Options

Author

Message

Adam Aspin

Posted Tuesday, May 05, 2009 12:26 AM

SSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 10:26 AM
Points: 81, Visits: 857

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

Post #709928

Bob Griffin

Posted Tuesday, May 05, 2009 8:19 AM

Ten Centuries

Group: General Forum Members
Last Login: Tuesday, June 11, 2013 7:57 AM
Points: 1,133, Visits: 667

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... Smile

Post #710256

markmeiller

Posted Tuesday, May 05, 2009 8:36 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, December 19, 2011 11:57 AM
Points: 8, Visits: 83

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”)

Post #710283

Adam Aspin

Posted Tuesday, May 05, 2009 8:49 AM

SSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 10:26 AM
Points: 81, Visits: 857

Thanks Mark - this sounds like a real timesaver.

Adam

Post #710309

kevlray

Posted Tuesday, May 05, 2009 10:11 AM

Right there with Babe

Group: General Forum Members
Last Login: Tuesday, March 27, 2012 9:05 AM
Points: 786, Visits: 161

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.

Post #710429

Adam Aspin

Posted Tuesday, May 05, 2009 10:57 AM

SSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 10:26 AM
Points: 81, Visits: 857

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

Post #710477

Chris Howarth-536003

Posted Wednesday, May 06, 2009 4:07 AM

SSC Veteran

Group: General Forum Members
Last Login: Thursday, May 30, 2013 8:03 AM
Points: 266, Visits: 1,025

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

Post #710911

Matteo-317581

Posted Friday, May 15, 2009 9:06 AM

Grasshopper

Group: General Forum Members
Last Login: Monday, March 28, 2011 11:41 AM
Points: 14, Visits: 65

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

Post #718001

Fred Stemp

Posted Tuesday, May 19, 2009 8:49 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, April 05, 2012 9:47 AM
Points: 6, Visits: 28

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.

Fred Stemp
Technical Project Manager
Definition 6, Marketing Integration. Technology Innovation. Building Brands That Matter.
Website: http://www.definition6.com
Email: fred.stemp@definition6.com

Post #719925

pitso.maceke

Posted Thursday, August 20, 2009 4:58 AM

Valued Member

Group: General Forum Members
Last Login: Monday, April 30, 2012 12:46 AM
Points: 51, Visits: 211

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

Post #774109

« Prev Topic | Next Topic »

17 posts, Page 1 of 2

1 2 »»

Permissions