SSRS Expression #Error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Analysis Server and Reporting Services (2008)

SSRS Expression #Error

Author

Topic

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2014-12-03 : 23:16:30

Hi, I have an SSRS with calculation but the result displayed an #Error. Need your help guys on what to do with this formula in expression. any idea. thanks.

below is my formula codes in SSRS expression that create #Error.

=IIf(Fields!requiredQty.Value = 0, FormatPercent(0),IIf(Fields!requiredQty.Value <= Fields!TOTAL.Value, FormatPercent(0), FormatPercent((Fields!requiredQty.Value-Fields!TOTAL.Value)/IIf(Fields!requiredQty.Value=0,1,Fields!requiredQty.Value))))

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-04 : 09:08:48

First, when you post questions, try to format the code for readability, like this:


=IIf(Fields!requiredQty.Value = 0, 
	FormatPercent(0),
	IIf(Fields!requiredQty.Value <= Fields!TOTAL.Value, 
		FormatPercent(0), 
		FormatPercent(
			(Fields!requiredQty.Value-Fields!TOTAL.Value)/
			IIf(Fields!requiredQty.Value=0,
				1,
				Fields!requiredQty.Value
			)
		)
	)
)

I built a test report and used your expression on a variety of data to test all the paths. It worked. That makes me wonder if your field references are incorrect. that is, do the fields requireQty and TOTAL really exist in your dataset?

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2014-12-08 : 22:13:19

Hi gbritton,

Sorry the first question i posted is working. I posted a new SSRS expression formula.
Kindly please take a look this new ssrs expression. thank you.

note: category format is default. i did not choose any format.

=IIf(Fields!RECLAIMQTY.Value = 0,
  FormatPercent(0), 
  IIF(Fields!RECLAIMQTY.Value<>0 and Fields!requiredQty.Value > Fields!TOTAL.Value,
	FormatPercent(
        Fields!RECLAIMQTY.Value / IIF(Fields!requiredQty.Value=0,
        1,Fields!requiredQty.value)
	    ),
		FormatPercent(Fields!RECLAIMQTY.Value / Fields!TOTAL.Value
	)
   )
)