SQLTeam.com | Weblogs | Forums

Expression for both font color and weight in SSRS

Hi

I would like to know if it is possible to change both the font color AND font weight based on conditions for a textbox with an expression.

If have the following to change the font color :

=SWITCH(Fields!MaksPenalisasie.Value = "JA", "Red", Fields!Gepenaliseer.Value = "JA", "Red", TRUE, "Black")

If would like to also make the font bold where Fields!MaksPenalisasie.Value = "JA".

Regards

I would do all of this in the stored procedure that feeds the report.

That way your ssrs report stays clean and easy to maintain when you become cto and next person has to pick up where you left off

Interesting. How do you do that?

Is the dataset code stored in the report as text? I also find it cleaner once you start getting beyond really basic SSRS formatting to have the expression logic in a stored procedure (or even just saved in the table the report is querying). It's easier to maintain, faster to make changes etc.

For example we would make an alert column. If the value was over a certain threshold it would be green, between another threshold yellow, below dark red. We would code out that logic in SQL, so the report just had to do whatever the column told it to. A lot easier than writing SSRS expressions that are more "hidden".

It seems what you're doing is really basic though. You can easily do what you're trying to do. The expression I'd write for the font weight would be =iif(Fields!MaksPenalisasie.Value = "JA","ExtraBold","Normal"). That should work.

And yes it's possible to go crazy. If a field value is "JA" make the font bold, make the background color red, the font color white, etc. I have a lot of experience doing that.

1 Like

No one has yet explained how to pull this off in the stored procedure that they recommend using. For example, the IIF formula in the post is MDX, not T-SQL: and, although I'm not an SSRS user, I'm pretty sure that MDX doesn't work in stored procedures.

Sorry if that was confusing or unhelpful. I would just try the expression I wrote right inside the report builder in the font weight property. The same way you did the one for the font color, it should handle the font weight just as easy.

In SQL we write case statements to build out formatting columns in the table or stored procedure. For example case when MaksPenalisasie = 'JA' then 'ExtraBold' else 'Normal' end as MaksPenalisasieFontWeight. If you're not familiar with having a stored procedure drive the data to the report I'm sure we can help with that.

For your case it's really basic formatting so doing it directly in the report should be just as fine.

1 Like

With SSRS you can change the font color and the font weight. If you select the textbox properties and choose for font and you can change any option with the fx. You cannot do it in once, you have to do it in 2 expressions even if the expression is the same. You can use an expression to do so:

=SWITCH(Fields!MaksPenalisasie.Value = "JA", "bold", Fields!Gepenaliseer.Value = "JA", "bold", TRUE, "default")

I don't agree with the others, I always keep the intelligence in the database and the format/style on the report. You can re-use the stored procedures for more cases and you shouldn't bother others with the format of your report.

Nice source:
Conditional Formatting with SSRS – SQLServerCentral

3 Likes

Totally agreed! and awesome link, as well.

There actually is a way to do all of this in stored procedures and I have done so for all of my DBA related "Morining Reports" so that I don't have to depend on a Client having SSRS in place but that's an exception rather than a rule.

That's also why I'm not going to post how to do it ALL (create the all the HTML from T-SQL to gen the report) from a stored procedure on this thread. And, I also don't see how to control the colors rendered in SSRS from a stored procedure.

My point was that a couple of folks said you could do it in a stored procedure and then never demonstrated how to do it. It's kind of like folks saying that "It's easy to get to the moon... all you have to do is build a rocket ship". :yum:

One can't answer a question at anyone's behest cause we have other things to do in life but here it is.


		--white hex
	      when p.par is null then '#FFFFFF'
		  --red hex
		  when purchased_quantity = 0 then '#FF0000'
		  when p.par > purchased_quantity then '#FF0000'
		  --yellow hex 20%
		  else '#FFFFFF'	        
	   end as color

then on the report

Awesome.. thank you for your time!

Shifting gears a bit...

I've learned that, when that is true, one should not suggest a method unless they're willing to take the time to demonstrate the method. :smiley:

not really but thanks for your opinion. things come up.