SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Conditional formatting with Reporting Services


Conditional formatting with Reporting Services

Author
Message
Adam Aspin
Adam Aspin
SSC-Addicted
SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)

Group: General Forum Members
Points: 443 Visits: 1032
Comments posted to this topic are about the item Conditional formatting with Reporting Services
Bob Griffin
Bob Griffin
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1371 Visits: 702
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
markmeiller
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 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”)
Adam Aspin
Adam Aspin
SSC-Addicted
SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)

Group: General Forum Members
Points: 443 Visits: 1032
Thanks Mark - this sounds like a real timesaver.

Adam
kevlray
kevlray
SSC Eights!
SSC Eights! (866 reputation)SSC Eights! (866 reputation)SSC Eights! (866 reputation)SSC Eights! (866 reputation)SSC Eights! (866 reputation)SSC Eights! (866 reputation)SSC Eights! (866 reputation)SSC Eights! (866 reputation)

Group: General Forum Members
Points: 866 Visits: 170
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
Adam Aspin
SSC-Addicted
SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)

Group: General Forum Members
Points: 443 Visits: 1032
Oops! Sorry about the typo - the ";" in the first snippet before CASE should, of course, be a comma!
Chris Howarth-536003
Chris Howarth-536003
Old Hand
Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)

Group: General Forum Members
Points: 397 Visits: 1163
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
Matteo-317581
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 72
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
Fred Stemp
SSC Rookie
SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)

Group: General Forum Members
Points: 45 Visits: 219
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
pitso.maceke
SSC Journeyman
SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)

Group: General Forum Members
Points: 87 Visits: 213
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search