Stanislav Jirák Stanislav Jirák -4 years ago 134
SQL Question

How to set font color in SSRS matrix based on values range?

I have a matrix of numbers and I want to change a font color if a value is grater then 75% of all value's range.

=Switch(Fields!Total.Value>(Sum(Fields!Total.Value))*175/100, "LightGreen", Fields!Total.Value<Avg(Sum(Fields!Total.Value))*25/100, "Red")


...obviously does not work, because I make a sum on each cells and I don't know how to aggregate values outside a SQL Server.
Thanks.

Answer Source

By default Fields!Total.Value will be the same as Sum(Fields!Total.Value) in the detail rows of your table. You can specify one aggregate scope override like this:

=Sum(Fields!Total.Value, "<Your DataSet or Group Name>")

If you need anything more intricate than this, I would suggest using a subquery to get the aggregates at the scope you need. There are many other answers out there on how to do this.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download