SRSS 带值范围的条件格式颜色? [英] SRSS Conditional formatting colour with value ranges?

问题描述

我有一个 sql 报告服务报告，它显示了我们所有商店等的销售额，它包括一个显示预算值百分比的列.我想要做的是，如果值是 >= .35 然后使它成为单元格 Green 的背景，如果值是 <.35 和 >.30 然后 Yellow 如果值为 <.30 然后是红色.

I have a sql reporting services report that displays sales from all of our stores etc it includes a column which displays a % to budget value. What I want to do is if the value is >= .35 then make it the background of the cell Green, if the value is < .35 and > .30 then Yellow if the value is < .30 then Red.

这真的让我很头疼，我似乎无法让它在所有 3 个范围内都能工作.

It's really doing my head in I just can't seem to get it to work with all 3 ranges.

我可以在单元格的背景颜色属性中放入表达式:

I can put in the background color properties of the cell the expression:

=IIF(Fields!ID__to_Budget.Value >= 0.35, "Green", "Red")

这行得通，但当然我没有任何黄色范围，任何低于 0.35 的都会变成红色.

And that works, but of course I don't have any Yellow range, anything under .35 becomes red.

我一直在尝试对背景颜色进行嵌套表达式，如下所示:

I have been trying to do a nested expression on the background color like this:

=iif( (Fields!ID__to_Budget.Value >= 0.35), "Green",
      ( iif(Fields!ID__to_Budget.Value <0.35 and > 0.30, "Yellow",
         iif(Fields!ID__to_Budget.Value < 0.30 "Red", "White") ) ) )

但它抱怨和>0.30"部分的语法错误.

but it complains about a syntax error on the "and >0.30" part.

非常感谢兰斯

推荐答案

您缺少一个逗号.但是，嵌套的 Iif 很麻烦，所以要执行简单的函数路由，我会为此使用 Switch 函数，因此您不必嵌套任何东西——这样会更容易正确使用语法.

You're missing a comma. But, nested Iifs are a pain, so to do the simple function route, I would use the Switch function for this, instead, so you don't have to nest anything--it will be easier to get the syntax right.

=Switch(
   Fields!ID__to_Budget.Value < 0.30, "Red",
   Fields!ID__to_Budget.Value < 0.35, "Yellow",
   Fields!ID__to_Budget.Value >= 0.35, "Green"
)

请参阅报告服务表达式示例 有关此功能的更多信息.

See Reporting Services Expression Examples for more on this function.

但在我看来，对值的严重性"进行编码在数据库中做得更好:

But it seems to me that encoding the "severity" of the values is something better done in the database:

CREATE TABLE dbo.BudgetSeverity (
   FromValue decimal(20, 10) NOT NULL PRIMARY KEY CLUSTERED,
   ToValue decimal(20,10) NOT NULL,
   SeverityLevel tinyint NOT NULL
);
INSERT dbo.BudgetSeverity
VALUES
   (-9999999999, 0.3, 1),
   (0, 0.35, 2),
   (0.35, 9999999999, 3)
;

然后在您的数据集中，加入该表.

Then in your data set, join to this table.

SELECT
   ...,
   BS.SeverityLevel
FROM
   ...
   INNER JOIN dbo.BudgetSeverity BS
      ON B.ID__to_Budget >= BS.FromValue
      AND B.ID__to_Budget < BS.ToValue

最后，在您的报告中:

=Choose(Fields!SeverityLevel.Value, "Red", "Yellow", "Green")

通过这种方式，您无需部署新报告即可更改严重性级别.如果您不喜欢将其放入数据库的想法，您还有其他选择.在报告的代码部分，您可以编写一个简单的函数来根据传入的值返回颜色.然后您可以使用更容易管理的 Case 语句，并且您可以在不同的字段中重用此函数，如果发生变化，可以更轻松地一次更新所有这些.

This way you can change the severity levels without having to deploy a new report. If you don't like the idea of putting this in the database, you still have other options. In the Code section of the report you could write a simple function to return a color based on the value passed in. Then you can use a Case statement which will be much, much easier to manage, and you can reuse this function in different fields, making it easier to update all of them at once if something changes.

这篇关于SRSS 带值范围的条件格式颜色?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！