SSRS IIF条件

Question

我有一个报告，其中多个条件将适用于100%、75%和50%

我使用了背景色条件

=iif(count(lookup(Fields!Assessors_Staff_ID.Value,Fields!EMPNO.Value, Fields!SINFO.Value, "FullTime"))>= "8" and lookup(Fields!Assessors_Staff_ID.Value,Fields!EMPNO.Value, Fields!SINFO.Value, "FullTime")
= "100%" ,"Green","Red") and iif(
 count(lookup(Fields!Assessors_Staff_ID.Value,Fields!EMPNO.Value,Fields!SINFO.Value, "FullTime"))>= "6" and lookup(Fields!Assessors_Staff_ID.Value,Fields!EMPNO.Value, Fields!SINFO.Value, "FullTime")
= "75%" ,"Green","Red") and iif(
count(lookup(Fields!Assessors_Staff_ID.Value,Fields!EMPNO.Value, Fields!SINFO.Value, "FullTime"))>= "4" and lookup(Fields!Assessors_Staff_ID.Value,Fields!EMPNO.Value, Fields!SINFO.Value, "FullTime")
= "50%" ,"Green","Red")

我没有任何颜色，请让我知道我在做什么，因为我是SSRS的新手，谢谢

=Switch(Fields!Date.Value >= CDate("01/05/" & Str(Year(Parameters!StartDate.Value))) and Fields!Date.Value <= CDate("01/10/" & Str(Year(Parameters!EndDate.Value))) and
 count(lookup(Fields!Assessors_Staff_ID.Value,Fields!EMPNO.Value, Fields!SINFO.Value, "FullTime"))>= "8"
and lookup(Fields!Assessors_Staff_ID.Value,Fields!EMPNO.Value, Fields!SINFO.Value, "FullTime") = "100%",
"Green",
Fields!Date.Value >= CDate("01/11/" & Str(Year(Parameters!StartDate.Value))) and Fields!Date.Value <= CDate("30/04/" & Str(Year(Parameters!EndDate.Value))) and
count(lookup(Fields!Assessors_Staff_ID.Value,Fields!EMPNO.Value, Fields!SINFO.Value, "FullTime"))>= "4"
and lookup(Fields!Assessors_Staff_ID.Value,Fields!EMPNO.Value, Fields!SINFO.Value, "FullTime") = "100%",
"Green",
Fields!Date.Value >= CDate("01/05/" & Str(Year(Parameters!StartDate.Value))) and Fields!Date.Value <= CDate("01/10/" & Str(Year(Parameters!EndDate.Value))) and
count(lookup(Fields!Assessors_Staff_ID.Value,Fields!EMPNO.Value,Fields!SINFO.Value, "FullTime"))>= "6"
and lookup(Fields!Assessors_Staff_ID.Value,Fields!EMPNO.Value, Fields!SINFO.Value, "FullTime") = "75%",
"Green",
Fields!Date.Value >= CDate("01/11/" & Str(Year(Parameters!StartDate.Value))) and Fields!Date.Value <= CDate("30/04/" & Str(Year(Parameters!EndDate.Value))) and
count(lookup(Fields!Assessors_Staff_ID.Value,Fields!EMPNO.Value, Fields!SINFO.Value, "FullTime"))>= "3"
and lookup(Fields!Assessors_Staff_ID.Value,Fields!EMPNO.Value, Fields!SINFO.Value, "FullTime") = "75%",
"Green",

 Fields!Date.Value >= CDate("01/05/" & Str(Year(Parameters!StartDate.Value))) and Fields!Date.Value <= CDate("01/10/" & Str(Year(Parameters!EndDate.Value))) and
count(lookup(Fields!Assessors_Staff_ID.Value,Fields!EMPNO.Value, Fields!SINFO.Value, "FullTime"))>= "4"
and lookup(Fields!Assessors_Staff_ID.Value,Fields!EMPNO.Value, Fields!SINFO.Value, "FullTime") = "50%" ,
"Green",
Fields!Date.Value >= CDate("01/11/" & Str(Year(Parameters!StartDate.Value))) and Fields!Date.Value <= CDate("30/04/" & Str(Year(Parameters!EndDate.Value))) and
count(lookup(Fields!Assessors_Staff_ID.Value,Fields!EMPNO.Value, Fields!SINFO.Value, "FullTime"))>= "2"
and lookup(Fields!Assessors_Staff_ID.Value,Fields!EMPNO.Value, Fields!SINFO.Value, "FullTime") = "50%",
"Green",
true,"Red"
 )

Answer 1

请尝试使用开关：

=Switch(
  count(lookup(Fields!Assessors_Staff_ID.Value,Fields!EMPNO.Value, Fields!SINFO.Value, "FullTime"))>= "8"
  and lookup(Fields!Assessors_Staff_ID.Value,Fields!EMPNO.Value, Fields!SINFO.Value, "FullTime") = "100%",
  "Green",
  count(lookup(Fields!Assessors_Staff_ID.Value,Fields!EMPNO.Value,Fields!SINFO.Value, "FullTime"))>= "6"
  and lookup(Fields!Assessors_Staff_ID.Value,Fields!EMPNO.Value, Fields!SINFO.Value, "FullTime") = "75%" ,
  "Green",
  count(lookup(Fields!Assessors_Staff_ID.Value,Fields!EMPNO.Value, Fields!SINFO.Value, "FullTime"))>= "4"
  and lookup(Fields!Assessors_Staff_ID.Value,Fields!EMPNO.Value, Fields!SINFO.Value, "FullTime") = "50%" ,
  "Green",
  true,"Red"
)

如果您对在每个条件中执行的计算具有正确的分组设置，则它将起作用。

如果这有帮助，请告诉我。