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?
Go to Top of Page

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
)
)
)
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-09 : 09:05:09
Same thing, your expression worked without error on the sample data I created.
Go to Top of Page

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2014-12-16 : 03:06:17
I notice this calculation has a zero value for both fields, that's why i got the #Error.


FormatPercent(Fields!RECLAIMQTY.Value / Fields!TOTAL.Value)
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-16 : 09:16:46
Ok -- probably you should post sample data so we can see what you used to cause the error. Otherwise we spend time spinning our wheels here.
Go to Top of Page
   

- Advertisement -