1

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
SwiftArchitect
  • 47,376
  • 28
  • 140
  • 179
user3430726
  • 55
  • 1
  • 7
  • Can you post the SQL statement and an example of the results of the statement. It might be easier to modify the SQL itself. Failing that, if I can see what the raw data from the dataset looks like then I should be able to hlpe. – Alan Schofield Jul 16 '15 at 00:03
  • Here is the SQL Statement - select DISTINCT Fr.FilingID, fq.AbbreviatedInstructions, fq.Mapping, fq.Number, fa.Grouping, fa.Instructions, fa.[Order], fa.DetailOrder, fa.GroupingOrder, fq.Id, null as Name, fa.FormatString, fa.[Type] as NumType, ISNULL(fa.OverrideAnswer,fa.SourceAnswer) as FinalAnswer from WorkBench.dbo.FilingAnswer fa join WorkBench.dbo.FilingQuestion fq on fa.FilingQuestionId = fq.id join WorkBench.dbo.FilingReview fr on fr.Id = fq.FilingReviewId where fr.FilingId in (@FilingID,@FilingID_Compare) and fr.FundId is null – user3430726 Jul 16 '15 at 15:14
  • Sorry about the delay, I'll look at this tonight or tomorrow. – Alan Schofield Jul 17 '15 at 11:17
  • I think I solved this problem. Thanks for your help. Check out the solution in my reply. - Thanks. – user3430726 Jul 18 '15 at 14:51

1 Answers1

0

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")

user3430726
  • 55
  • 1
  • 7
  • Glad you got it sorted. You might want to clean up your code by adding a function to the reports that converts your various inputs to comparable numeric values. – Alan Schofield Jul 19 '15 at 19:14
  • Then the expression would look something like this = Code.CleanValues(Fields!col1.Value) - Code.CleanValues(Fields!col2.Value) The function would be something like this... public function CleanValues (myValue) as decimal dim result as decimal Decimal.TryParse(myValue,result) return result End Function – Alan Schofield Jul 19 '15 at 19:21
  • Agreed. It's a good idea to write a function that will reply the calculated value. Much cleaner approach. That would be my next level of optimization of code. I really appreciate your help. Thanks again. – user3430726 Jul 20 '15 at 14:39