Visual Studio Report Builder Issue

jversusj

Hi all, I tried searching the forum, but I guess my query strings were off base because I wasn't finding anything.

I am trying to do a simple calculation in VS report builder as part of a total row in a NAV report layout.

I need to calculate an "Awarded Margin%" based on two fields, "Awarded Value" and "Awarded Cost".

In classic NAV, the formula was easy,
((Awarded Value - Awarded Cost)/Awarded Value) *100

The problem is that I have some records where Awarded Value = 0. In classic NAV, this was addressed by adding the conditon
IF Awarded Value <> 0 THEN
<formula>
ELSE
0;
I am now trying to approximate this in VS and having a rough go of it. I know it must be easy and something I am overlooking. Both Awarded Value and Awarded Cost are decimal fields in NAV, so I would think they are decimals in the VS dataset, but I have tried CDec function to make them decimal and still had errors in my output. I then thought to create a decimal variable in NAV and set AwardedValueLessCost = Awarded Value - Awarded Cost, and AwardValue = AwardedValue to eliminate the need to SUM in VS. I still get an error.

Currently, I create an expression in a field in my total row:
IIf(Last(AwardedValue.Value) > 0,Last(AwardedValueLessCost.Value)/Last(AwardedValue.Value),0)

When I run the report, I get calculated percentages for all records where the Awarded Value <> 0, but get #ERROR for all others.

I then tried experimenting with my formula.
IIf(Last(AwardedValue.Value) > 0,1,0)

The above expression returns the expected 100% for those with Awarded Value and 0% for all others.

I then tried some arithmetic to see if that was the problem
IIf(Last(AwardedValue.Value) > 0,90/100,0)

The above expression returns the expected 90% and 0%.

I then tried IIf(Last(AwardedValue.Value) > 0,Last(AwardedValueLessCost),0)

the above expression returned the expected values (0 and otherwise).

I'm at a loss as to why I cannot get this formula to work against the values from the data set with arithmetic operations. I have confirmed that Last (AwardedValue.Value) and Last(AwardedValueLessCost) are returning the expected values.

Thoughts?

jversusj

I could use the Hidden property to hide those that throw the Error, but i want to understand what I am doing wrong since this is such a very basic calculation and should be possible.

Kishorm

Looking at your various tests, I would try...

IIf(Last(AwardedValue.Value) > 0,Last(AwardedValueLessCost)/Last(AwardedValue.Value),0)

...I.e. do not have the ".Value" on the AwardedValueLessCost variable

chi

Hi, try reference to http://www.bidn.com/blogs/dustinryan/ss ... zero-error

jversusj

chi wrote:

Hi, try reference to http://www.bidn.com/blogs/dustinryan/ss ... zero-error

Yes, this appears to have solved it. Thank you! \:D/