使用IF语句with和语句计算列数>目标

Question

我有4列计算员工佣金的基础上，如果他们击中了那个类别。我有一个iif的声明工作，只计算是否所有是命中，如果一些是命中。我需要做的是，如果1,2,3,4被击中，那么第5列将显示"4“，如果第2,4列被击中，则显示"2”。这可以是任何顺序。因此，有些可能仅为4,1为真，或列为2, 3 ,4为真，即等于3。

我尝试了以下代码：

=IIf(ReportItems!gp.Value > ReportItems!gpgoal.Value AND 
ReportItems!gp.Value > ReportItems!gp70goal.Value AND ReportItems!accessorygpperbox.Value > 70 AND 
ReportItems!tmppercent.Value > .7499,"4", IIf(ReportItems!gp.Value > ReportItems!gpgoal.Value AND 
ReportItems!gp.Value > ReportItems!gp70goal.Value AND
ReportItems!accessorygpperbox.Value > 70,"3",IIf(ReportItems!gp.Value > ReportItems!gpgoal.Value AND 
ReportItems!gp.Value > ReportItems!gp70goal.Value,"2",IIf(ReportItems!gp.Value > ReportItems!gpgoal.Value,"1","0")

我的SQL代码

WITH MEMBER [Measures].[DateYMDUniqueName] AS IIF (
  IsEmpty([Measures].[Quantity])
  ,Nothing
  ,[Date YMD].CURRENTMEMBER.UNIQUENAME
)
 MEMBER [Measures].[DateUniqueName] AS IIF (
  IsEmpty([Measures].[Quantity])
  ,Nothing
  ,[Date].CURRENTMEMBER.UNIQUENAME
) MEMBER [Measures].[ParentUniqueName] AS   IIF (IsEmpty([Measures].[Quantity])
     ,Null
     ,[Categories and Products].CURRENTMEMBER.PARENT_UNIQUE_NAME
  )  MEMBER [Measures].[UniqueName] AS  IIF (IsEmpty([Measures].[Quantity])
     ,Null
     ,[Categories and Products].CURRENTMEMBER.UNIQUENAME
) MEMBER [Measures].[Level] AS   IIF (IsEmpty([Measures].[Quantity])
     ,Null
     ,[Categories and Products].CurrentMember.Level.Ordinal
  ) MEMBER [Measures].[Accessory Value] AS ([Measures].[Profit], [Performance Groups Name].&[155]) MEMBER [Measures].[Insurance Value] AS ([Measures].[Profit], [Performance Groups Name].&[5]) MEMBER [Measures].[Total GP per Device Goal] AS ([Measures].[Employee Profit Target], [Performance Groups Name].&[36]) MEMBER [Measures].[Total Accessory GP Goal] AS  ([Measures].[Employee Profit Target], [Performance Groups Name].&[3]) MEMBER [Measures].[Total Insurance GP Goal] AS ([Measures].[Employee Profit Target], [Performance Groups Name].&[5]) MEMBER [Measures].[Avg Device GP Goal] AS ([Measures].[Employee Profit Target], [Performance Groups Name].&[37]) MEMBER [Measures].[Accessory Dollar per Device Goal] AS ([Measures].[Employee Profit Target], [Performance Groups Name].&[33]) MEMBER [Measures].[Accessory Qty Goal] AS  ([Measures].[Employee Target], [Performance Groups Name].&[3]) MEMBER [Measures].[Insurance Dollar per device Goal] AS ([Measures].[Employee Profit Target], [Performance Groups Name].&[38]) MEMBER [Measures].[Insurance Percent Goal] AS .60 MEMBER [Measures].[GP_Value] AS ([Measures].[Profit], [Performance Groups Name].&[157]) MEMBER [Measures].[GP_Goal] AS [Measures].[Employee Profit Target] MEMBER [Measures].[Activations_Qty] AS ([Measures].[Quantity], [Performance Groups Name].&[197]) MEMBER [Measures].[Activations_Goal] AS ([Performance Groups Name].&[197],[Measures].[Employee Target]) MEMBER [Measures].[Upgrades_Qty] AS ([Measures].[Quantity], [Performance Groups Name].&[147]) MEMBER [Measures].[Upgrades_Goal] AS ([Performance Groups Name].&[147],[Measures].[Employee Target])

 MEMBER [Measures].[Total Devices Qty] AS ([Measures].[Quantity], [Performance Groups Name].&[149]) MEMBER [Measures].[New_Strategic_Devices_Qty] AS ([Measures].[Quantity], [Performance Groups Name].&[151]) MEMBER [Measures].[Accessory Qty] AS ([Measures].[Quantity], [Performance Groups Name].&[155]) MEMBER [Measures].[Insurance Qty] AS ([Measures].[Quantity], [Performance Groups Name].&[174]) MEMBER [Measures].[Total Devices Goal] AS ([Measures].[Employee Target], [Performance Groups Name].&[25]) MEMBER [Measures].[New_Strategic_Devices_Goal] AS ([Performance Groups Name].&[151],[Measures].[Employee Target]) MEMBER [Measures].[Insurance Qty Goal] AS ([Measures].[Employee Target], [Performance Groups Name].&[5]) MEMBER [Measures].[Total Devices Value] AS ([Measures].[Profit], [Performance Groups Name].&[25]) MEMBER [Measures].[Hit Rate Percent Goal] AS .33 MEMBER [Measures].[New Strategic Percent Goal] AS .1556 MEMBER [Measures].[Accessory Qty per Device Goal] AS 2.5 MEMBER [Measures].[SmartPhone Only] AS ([Measures].[Quantity], [Performance Groups Name].&[43]) MEMBER [Measures].[Accessory Bundles] AS ([Measures].[Quantity], [Performance Groups Name].&[158]) MEMBER [Measures].[New and Upg All but CPE and Prepaid] AS ([Measures].[Quantity], [Performance Groups Name].&[64]) MEMBER [Measures].[New And Smartphone Samsung Postpaid] AS ([Measures].[Quantity], [Performance Groups Name].&[55]) MEMBER [Measures].[New and Upgrade Smartphone Postpaid ] AS ([Measures].[Quantity], [Performance Groups Name].&[43]) MEMBER [Measures].[Samsung Flagship] AS ([Measures].[Quantity], [Performance Groups Name].&[165]) MEMBER [Measures].[Hum Count] AS ([Measures].[Quantity], [Performance Groups Name].&[45]) MEMBER [Measures].[Smartphone Postpaid] AS ([Measures].[Quantity], [Categories and Products].&[101011101619]) MEMBER [Measures].[Accessory GP Per Box] AS ([Performance Groups Name].&[3], [Measures].[Quantity]) MEMBER [Measures].[New Jetpack] AS ([Performance Groups Name].&[162],[Measures].[Quantity]) MEMBER [Measures].[New Tablet] AS ([Performance Groups Name].&[161],[Measures].[Quantity]) MEMBER [Measures].[New Prepaid] AS ([Performance Groups Name].&[164],[Measures].[Quantity]) MEMBER [Measures].[CPE M2M Prepay] AS ([Performance Groups Name].&[170],[Measures].[Quantity]) MEMBER [Measures].[Phobio Trade-In] AS ([Measures].[Quantity], [Performance Groups Name].&[91]) MEMBER [Measures].[HYLA Trade-In] AS ([Measures].[Quantity], [Performance Groups Name].&[92]) MEMBER [Measures].[Trade-In Percentage] AS ([Measures].[Quantity], [Performance Groups Name].&[94]) MEMBER [Measures].[New and Upgrade Total] AS ([Measures].[Quantity], [Performance Groups Name].&[149]) MEMBER [Measures].[Moto Z2 Force] AS ([Performance Groups Name].&[82],[Measures].[Quantity]) MEMBER [Measures].[LG G7] AS ([Performance Groups Name].&[103],[Measures].[Quantity]) MEMBER [Measures].[Sam S9 and S9 Plus] AS ([Performance Groups Name].&[104],[Measures].[Quantity]) MEMBER [Measures].[Gizmo] AS ([Performance Groups Name].&[105],[Measures].[Quantity]) MEMBER [Measures].[Samsung S8 S8Plus and Note8] AS ([Performance Groups Name].&[108],[Measures].[Quantity]) MEMBER [Measures].[LG V30] AS ([Performance Groups Name].&[107],[Measures].[Quantity]) MEMBER [Measures].[Basic Phone] AS ([Performance Groups Name].&[89],[Measures].[Quantity]) MEMBER [Measures].[Smartphones] AS ([Performance Groups Name].&[114],[Measures].[Quantity]) MEMBER [Measures].[Gizmo watch] AS ([Performance Groups Name].&[110],[Measures].[Quantity]) MEMBER [Measures].[Apple Watch] AS ([Performance Groups Name].&[167],[Measures].[Quantity]) MEMBER [Measures].[Samsung Watch] AS ([Performance Groups Name].&[118],[Measures].[Quantity]) MEMBER [Measures].[New HUM] AS ([Measures].[Quantity], [Performance Groups Name].&[181])

 MEMBER [Measures].[New HUM X] AS ([Measures].[Quantity], [Performance Groups Name].&[182])

 MEMBER [Measures].[HUM Devices] AS ([Measures].[Quantity], [Performance Groups Name].&[153]) MEMBER [Measures].[Postpaid Phones and Tablets] AS ([Measures].[Quantity], [Performance Groups Name].&[140]) MEMBER [Measures].[Setup Fee] AS ([Measures].[Quantity], [Performance Groups Name].&[171]) MEMBER [Measures].[Smartphone Post and Prepaid Sales From Stock] AS ([Measures].[Quantity], [Performance Groups Name].&[90]) MEMBER [Measures].[New and Upgrade Postpaid phones] AS ([Performance Groups Name].&[173],[Measures].[Quantity]) MEMBER [Measures].[Postpaid Phones] AS ([Performance Groups Name].&[173],[Measures].[Quantity]) SELECT NON EMPTY { [Measures].[HYLA Trade-In], [Measures].[Phobio Trade-In], [Measures].[CPE M2M Prepay], [Measures].[New and Upgrade Total], [Measures].[LG G7], [Measures].[Moto Z2 Force], [Measures].[Trade-In Percentage], [Measures].[New Jetpack], [Measures].[New Prepaid], [Measures].[New and Upgrade Smartphone Postpaid ], [Measures].[Samsung Flagship], [Measures].[Postpaid Phones], [Measures].[New And Smartphone Samsung Postpaid], [Measures].[Accessory GP Per Box], [Measures].[New Tablet], [Measures].[Hum Count], [Measures].[Smartphone Postpaid], [Measures].[HUM Devices], [Measures].[Postpaid Phones and Tablets], [Measures].[New HUM], [Measures].[New HUM X], [Measures].[New and Upg All but CPE and Prepaid], [Measures].[New and Upgrade Postpaid phones], [Measures].[Setup Fee], [Measures].[Smartphone Post and Prepaid Sales From Stock], [Measures].[Samsung Watch], [Measures].[Samsung S8 S8Plus and Note8], [Measures].[LG V30], [Measures].[Sam S9 and S9 Plus], [Measures].[Gizmo], [Measures].[Gizmo watch], [Measures].[Apple Watch], [Measures].[Basic Phone], [Measures].[Smartphones], [Measures].[New_Strategic_Devices_Qty], [Measures].[New_Strategic_Devices_Goal], [Measures].[Total Devices Qty], [Measures].[Accessory Qty Goal], [Measures].[Insurance Qty], [Measures].[Insurance Qty Goal], [Measures].[Accessory Qty], [Measures].[Total Devices Goal], [Measures].[GP_Value], [Measures].[GP_Goal], [Measures].[Accessory Bundles], [Measures].[Activations_Goal], [Measures].[Upgrades_Qty], [Measures].[Upgrades_Goal], [Measures].[Activations_Qty], [Measures].[Total Accessory GP Goal], [Measures].[Hit Rate Percent Goal], [Measures].[Total Devices Value], [Measures].[Hours Punched], [Measures].[New Strategic Percent Goal], [Measures].[SmartPhone Only], [Measures].[Accessory Qty per Device Goal], [Measures].[Sales], [Measures].[Insurance Percent Goal], [Measures].[Insurance Value], [Measures].[Total Insurance GP Goal], [Measures].[Accessory Value], [Measures].[Total GP per Device Goal], [Measures].[Insurance Dollar per device Goal], [Measures].[Accessory Dollar per Device Goal], [Measures].[Avg Device GP Goal] } ON COLUMNS, NON EMPTY { ([Locations and Employees].[Employee Name].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOSET(@LocationsandEmployees, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOMEMBER(@FromDateYMD, CONSTRAINED) : STRTOMEMBER(@ToDateYMD, CONSTRAINED) ) ON COLUMNS FROM [Super])) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

我知道我在正确的轨道上，但也许有人可以更容易或更干净地做这件事。

Answer 1

我认为使用SWITCH语句处理这么多条件语句会好得多。SWITCH中的每个条件只有一个可以计算的值，所以只有在条件为真时才能设置该值。

=SWITCH(ReportItems!gp.Value > ReportItems!gpgoal.Value AND ReportItems!gp.Value > ReportItems!gp70goal.Value AND ReportItems!accessorygpperbox.Value > 70 AND ReportItems!tmppercent.Value > .7499,"4", 
        ReportItems!gp.Value > ReportItems!gpgoal.Value AND ReportItems!gp.Value > ReportItems!gp70goal.Value AND ReportItems!accessorygpperbox.Value > 70,"3",
        ReportItems!gp.Value > ReportItems!gpgoal.Value AND ReportItems!gp.Value > ReportItems!gp70goal.Value,"2",
        ReportItems!gp.Value > ReportItems!gpgoal.Value,"1",
        true, "0")

这个表达式基本上复制了表达式，但是它不需要多个IIF，最后一个条件将与前四个条件语句不匹配的记录的值设置为零。

Answer 2

根据您的描述，很难获得您想要存档的内容，但这将对您有所帮助。简单地创建一个又一个步骤。我通常对表达式使用计算字段(以便更好地重用)。(右键单击数据集字段并选择字段属性>字段>添加)

'Field name: Category1
=IIf(Fields!gp.Value > Fields!gpgoal.Value, "1", Nothing)

'Field name: Category2
=IIf(Fields!gp.Value > Fields!gpgoal.Value AND Fields!gp.Value > Fields!gp70goal.Value,"2", Nothing)

Ans so on for Category3 and Category4...

在您的tablix中，您现在可以使用四个类别的组合作为最终结果：

=IIF(Fields!Category1.Value = 1 AND Fields!Category2.Value = 2 AND Fields!Category3.Value = 3 AND Fields!Category4.Value = 4, "5", Nothing)

其他组合也是如此。

注意:如果引用Report!Items，也可以将表达式放在计算字段中。