SSRS表达式替换为NA的NA(SSRS Expression to Substitute 0 for NA)

SSRS表达式替换为NA的NA(SSRS Expression to Substitute 0 for NA)

我有下面提供的SSRS表达式。 该表达式的问题是最后一列有时会显示#VALUE! 当第一列值为NA 。 它基本上没有计算。 如何修复此表达式以使其显示正确的数值？ 一种可能的解决方案是用0代替NA ，但我怎样才能做到这一点？

表达：

= iif（Fields！NumType.Value =“1”，iif（reportItems！txt_Current_Filing_Firm.value =“”，“0”，reportItems！txt_Current_Filing_Firm.value），“1”） - IIF（Fields！NumType.Value =“1 “，iif（reportitems！txt_Compare_Filing_Firm.value =”“，”0“，reportitems！txt_Compare_Filing_Firm.value），”1“）

SSRS的当前产出：

Column 1 | Column 2 | Column 3 NA 3456.00 #VALUE! NA 347856.00 #VALUE! NA 98763456.00 #VALUE! NA 33456.35 #VALUE!

SSRS的预期产出：

Column 1 | Column 2 | Column 3 0 3456.00 -3456.00 0 347856.00 -347856.00 0 98763456.00 -98763456.00 0 33456.35 -33456.35

I've the SSRS Expression that is provided below. The issue with that expression is that the last column sometimes shows #VALUE! when the 1st column value is NA. It is basically failing to calculate. How can I fix this expression so that it shows the correct numeric value? One possible solution is to substitute 0 for NA, but how can I achieve this ?

Expression:

=iif(Fields!NumType.Value = "1",iif(reportItems!txt_Current_Filing_Firm.value = "","0",reportItems!txt_Current_Filing_Firm.value),"1") - IIF(Fields!NumType.Value = "1",iif(reportitems!txt_Compare_Filing_Firm.value = "","0",reportitems!txt_Compare_Filing_Firm.value),"1")

Current Output in SSRS:

Column 1 | Column 2 | Column 3 NA 3456.00 #VALUE! NA 347856.00 #VALUE! NA 98763456.00 #VALUE! NA 33456.35 #VALUE!

Expected Output in SSRS:

Column 1 | Column 2 | Column 3 0 3456.00 -3456.00 0 347856.00 -347856.00 0 98763456.00 -98763456.00 0 33456.35 -33456.35

最满意答案

这是解决方案。 想分享。 我们需要用0代替“NA”。它有效！

= iif（Fields！NumType.Value =“1”，iif（reportItems！txt_Current_Filing_Firm.value =“”OR reportItems！txt_Current_Filing_Firm.value =“NA”，“0”，reportItems！txt_Current_Filing_Firm.value），“1”） - IIF（Fields！NumType.Value =“1”，iif（reportItems！txt_Current_Filing_Firm.value =“”或reportitems！txt_Compare_Filing_Firm.value =“NA”，“0”，reportitems！txt_Compare_Filing_Firm.value），“1”）

Here is the solution. Thought to share. We need to replace "NA" with 0. It works!

=iif(Fields!NumType.Value = "1",iif(reportItems!txt_Current_Filing_Firm.value = "" OR reportItems!txt_Current_Filing_Firm.value = "NA","0",reportItems!txt_Current_Filing_Firm.value),"1") - IIF(Fields!NumType.Value = "1",iif(reportItems!txt_Current_Filing_Firm.value = "" OR reportitems!txt_Compare_Filing_Firm.value = "NA","0",reportitems!txt_Compare_Filing_Firm.value),"1")