Function to test & divide two numbers

[DietSquirt]

I'm new to SSRS and trying to cope with the infamous divide by zero problem. By Googling, I see references to placing a function in the code section to do this, but haven't been able to see an example of the actual function.

Does anyone know of a function to do this posted on the net, or can you provide an example? I do need to learn to write a function, but this being the first one I sure could use a working model to study. Any help would be gratefully appreciated - Thanks!

 

[xlbo]

as a basic premis

=IIF(DivisorField = 0,0,NumeratorField/DivisorField)

No need for code

Alternatively

Function TestDivide(Numerator as double, Divisor as double)
dim retVal
if Divisor = 0 then
  retVal = 1
elseif Numerator = 0 then
  retVal = 0
else
  retval = Numerator/Divisor
end if

return retVal
End Function

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[DietSquirt]

Thank you Geoff, I'm going to give it a try, but this week I'm one of the duller crayons. What is the syntax to call this function and send it the values?

PS the first expression you provided has resulted in divide by zero errors for me when I use it, which has forced me to learn something new (functions).

 

[xlbo]

Interesting that you stil lget DIV/0 errors even when testing for 0 - shouldn;t happen as the function should stop after finding that the divisor field is 0.....

Code to call a function is

=code.TestDivide(Fields!NumeratorFieldName.Value,FieldsDivisorFieldName.Value)

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[DietSquirt]

OK I tried it and I'm getting errors. I'm trying to calculate the gross margin for a group total of individual records. Each record is for one customer order line. It is set up like teh example beolw. I put tics in front of the =Fields...so it could be copied out to Excel and make more sense.

When I try to call the fuctions from the Margin% in the subtotal row, I get these errors:
***The Errors***
[rsFieldReference] The Value expression for the textbox ‘textbox18’ refers to the field ‘txtCustGrpMarginDol’. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope.
[rsFieldReference] The Value expression for the textbox ‘textbox18’ refers to the field ‘txtCustgrpSalesDollars’. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope.
***End Errors***

***Report setup***
Column A Column B ColumnC ColumnD
Sales$ Cost$ Margin$ Margin%
Detail row 100 80 20 0.2
The Expressions in the detail row '=Fields!Price.Value '=Fields!Cost.Value Fields!Price.Value-Fields!Cost.Value "=iif((Fields!Price.Value -
Fields!.Value) <=0, 0,
(Fields!Price.Value-Fields!Cost.Value)/iif(Fields!Price.Value =0,1,Fields!Price.Value))"
etc.
Subtotal row '=SUM(Fields!Price.Value) '=sum(Fields!Cost.Value) SUM(Fields!Price.Value)-SUM(Fields!Cost.Value) '=code.TestDivide(Fields!txtCustGrpMarginDol.Value,SUM(Fields!txtCustgrpSalesDollars.Value))
Name of Subtotal row fields txtCustgrpSalesDollars txtGCustGrpMarginDol

 

[SgtJarrow]

As with IIF in many other applications (Access, Excel, etc), both sides of the IIF are evaluated before a result is returned in SSRS. To get around this, if you expect a possible DIV/0 error in an IIF, you can use this:

=IIF(Sum(Fields!PYCE.Value) <> 0, (Sum(Fields!CYCE.Value) - Sum(Fields!PYCE.Value)) / IIF(Sum(Fields!PYCE.Value) <> 0, Sum(Fields!PYCE.Value), 1), 0)

Notice the second embedded IIF in my denomiator. It checks for the field you might expect the 0in and returns a 1 if it is 0. This embedded IIF is processed when the first IIF is kicked off, ensuring the denomator is always a valid number.

I found this on some M$ forum a while back and it has never failed me...

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[DietSquirt]

Thanks Robert, that was the first thing I tried and I run into a lot of messages about scope. This formula is in subtotals in a group footer. How would I adapt that to pass in the correct scope?

You can probably tell I'm going in circles, hitting first one problem and then another.

 

[SgtJarrow]

Looking at your previous posts, I think you may have a logic issue. Please let me know if I am wrong.

Where I work, margins are calculated as Price - Cost / Cost ...
not Price - Cost / Price.

Based on that fact and the field names you supplied, I would expect this:

=IIF(SUM(Fields!Price.Value) - SUM(Fields!Cost.Value) < 0, 0, (SUM(Fields!Price.Value) - SUM(Fields!Cost.Value)) / SUM(Fields!Cost.Value))

I would think that would be all you would need. I would never expect COST to be 0.


But if that is not your case....then this should work:

=IIF(SUM(Fields!Price.Value) - SUM(Fields!Cost.Value) < 0, 0, (SUM(Fields!Price.Value) - SUM(Fields!Cost.Value)) / IIF(SUM(Fields!Price.Value) = 0, 1, SUM(Fields!Price.Value)))


=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[DietSquirt]

Price Cost Margin$ Margin%
104 21 83 79.41 (Price-cost)/price
104 21 83 370.6 (Price-cost)/Cost

I thought WE had good margins!
Maybe our terminology is different. "Cost" to us is our cost of manufacturing the product, "Price" is the $ we invoiced the customer.

Yes, we ship promotional samples at $0 cost and have to factor them in the overall margin.

 

[xlbo]

In terms of your scoping issue (if you still have it), you can use the Fields!Fieldname.Value syntax if the report item exists in the data set for that control. If it does not then you must use an aggregate funtion and reference the data set specifically

=SUM(Fields!FieldName.Value, "DataSetName")





Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question