HI
I have a an issues with a calculated field, and I just cannot get my head around although i have tried numerous different options
Divide By Zero from a calculated field
= iif(Fields!ReturnedQty.Value=0,1 / IIF(100*IIF (Fields!ItemQty.Value=0,1))*100),Fields!ItemQty.Value))
Any assistance greatly appreciated
You have too many closing parentheses - plus your calculation will result in a NULL value. If I am reading this correctly, then you should have:
=IIf(Fields!ReturnedQty.Value = 0, 1 / IIf(Fields!ItemQty.Value = 0, 1, Fields!ItemQty.Value) * 100, Fields!ItemQty.Value)
I am not sure what you are trying to calculate here - how does the Returned Quantity factor into the calculation?
Many thanks I will give that a go , it's for a calculated field for returns percentage against units sold
Hi, I just reduced your IIF() sentence to the following simplier form so you can analyze better the outcome:
if Fields!ReturnedQty.Value = 0
if Fields!ItemQty.Value = 0
(1 / 1 ) * 100
else (1 / Fields!ItemQty.Value) * 100
else Fields!ItemQty.Value
As there is a divide by zero you should check the value of Fields!ItemQty.Value , it must be not null or greater than zero.
Jaime
that still throws errors and doesnt cover the sum i'm trying to achive , the below works but when i try to sort it throws the error so i ssume i need to cover the NULLS in Feilds!Itemqty , but i cant work out the expression .
= IIF (fields!ReturnedQty.Value=0, 0, fields!ReturnedQty.Value* (100 / Fields!ItemQty.Value))
You changed the calculation...
It would help a great deal if you stated what the calculation is supposed to do...your new statement will fail with a divide by zero error when Fields!ItemQty.Value = 0. With that said - to get the percentage of returned items the calculation would be: Returned / Quantity.
You don't need to multiply by 100 - you can set the field to a percentage for display and just return the value of the calculation and SSRS will display the value correctly.
So - your calculation needs to be: IIf(Fields!ReturnedQty.Value = 0, 0.0, Fields!ReturnedQty.Value / IIf(Fields!ItemQty.Value = 0, 1, Fields!ItemQty.Value))
If you have 1 item returned - with 100 items in the quantity - 1 / 100 = .01 and display as a percentage will be 1%. If you have 10 items returned - with a quantity of 10 = 10 / 10 = 1 and that will display as a percentage of 100%
If you have 10 returned - with a quantity of 0 (not sure how that would ever happen though - unless you are counting the quantity before the items have been returned) then you would have 10 / 1 = 10 - which will be 1000%.
For a better calculation - I think you would want to count the returned and quantity as the total and then determine the percentage returned. That would be:
IIf(Fields!ReturnedQty.Value = 0, 0.0, Fields!ReturnedQty.Value / (Fields!ItemQty.Value + Fields!ReturnedQty.Value)
10 items returned - with 10 items in quantity (before being returned) results in: 10 / (10 + 10) = .5 (50%) of total items in quantity (stock) were returned.
With this calculation - you do not have to worry about the value in Fields!ItemQty.Value being 0 or empty. If there are zero items this results in: 10 / (0 + 10) = 1 (100%) of items in stock were returned.
If the value in Fields!ItemQty.Value can be empty or nothing - then you would need something like:
IIf(Fields!ReturnedQty.Value = 0, 0.0, Fields!ReturnedQty.Value / (IsNothing(Fields!ItemQty.Value, 0) + Fields!ReturnedQty.Value))
However - I would insure that I never got an empty or null value back from SQL and that way I could avoid unnecessary complications.
Many thanks for your input , yes the real issue is that we may have items returned but no sales . This is due to gaurentee periods along with Our agents agreeing to take old Stock back to get new stock into customers . Makes this sort of thing a real pain
So what do you want to calculate? Is it the percentage of returned items (ReturnedQty) over items sold (ItemQty) regardless of how many items were sold?
If you have 10 items returned - with only 1 item sold, what is the percentage you want returned?
10 / 1 = 10 (1000%)
10 / (1 + 10) = .91 (91%)
Or something else?
why not do this on the server side SSRS is for mainly for presentation only. I would recommend you avoid this on SSRS but instead do it in a stored procedure
Why would doing this in SQL Server be any better or easier? The calculation is the same regardless of where it is performed.
This doesn't actually need the IIf - since 0 / n = 0 so the calculation becomes: ReturnedQty / (ItemQty + ReturnedQty).
In SQL Server this becomes:
CAST(ReturnedQty * 1.0 / (ItemQty + ReturnedQty) * 100 AS numeric(5, 2)) AS ReturnedPercentage
Add the fact that ReturnedQty and ItemQty are probably computed/calculated columns themselves, you now have a more complex SQL statement just to get the percentage which is easily calculated in a cell in SSRS and formatted as a percentage for display.
not better or easier, just a personal preference. I hate working in SSRS with conditional formatting and calculations. Then if you need to fix something easier, for me, to change it in a stored procedure than an SSRS report.
Firstly I would like to thank all for there input and ideas . I'm on a sharp learning curve with this , I appricate it would be easier to correct the issue in SQL rather than SSRS as SSRS is not the most user friendly
So this is where i'am I need to caluculate the Percentage of the retuned items to those sold , On some occasions there have been returns and no items sold so hence the Divide By 0 error .
The calculation I believe should be 90 x (100/125) = 72 % Or in SSRS = fields!ReturnedQty.Value* (100/fields!ItemQty.Value )
This calculation works fine but as soon as I ask it to sort ASC or DESC it throws a Divide By error
Below is the original SQL , i have tried to Cast the calculation but obviously missing something
(SELECT
ISNULL((
SELECT SUM(SalesInvoiceItems.Quantity) AS INVOICES
FROM SalesInvoiceItems
INNER JOIN SalesInvoices ON SalesInvoiceItems.SalesInvoice = SalesInvoices.SalesInvoice
INNER JOIN Customers ON SalesInvoices.Customer = Customers.Customer
INNER JOIN CustomerCommissionAgents ON Customers.Customer = CustomerCommissionAgents.Customer
WHERE YEAR(SalesInvoices.effectivedate) = YEAR(getdate())
--AND dbo.wfn_GetUDFNvarchar ('Category','Products', Products.Product) IN ('CLOCK')
AND
SalesInvoiceItems.Product = Products.Product ), 0) as ItemQty,
ISNULL((
SELECT SUM(ReturnedItems.QuantityReturned) AS returned
FROM ReturnedItems
INNER JOIN ReturnNotes ON ReturnedItems.ReturnNote = ReturnNotes.ReturnNote
WHERE (ReturnedItems.ReportedProduct = Products.Product or ReturnedItems.ActualProduct = Products.Product or ReturnedItems.ReplacementProduct = Products.Product)
AND YEAR(ReturnNotes.OpenedDate) = YEAR(getdate())
-- AND dbo.wfn_GetUDFNvarchar ('Category','Products', Products.Product) IN ('CLOCK')
AND ReturnedItems.QuantityReturned >= '1'
), 0) as ReturnedQty,
ISNULL((
SELECT STUFF ((SELECT ',' + FaultCodeId FROM FaultCodes
INNER JOIN ReturnedItems ON ReturnedItems.ActualFaultCode = FaultCodes.FaultCode
INNER JOIN ReturnNotes ON ReturnedItems.ReturnNote = ReturnNotes.ReturnNote
WHERE ReturnedItems.ReportedProduct = Products.Product
AND YEAR(ReturnNotes.OpenedDate) = YEAR(getdate())
AND ReturnedItems.QuantityReturned >= '1'
-- AND FaultCodes.FaultCodeId IN ('CLOCK 10')
FOR XML PATH ('')), 1, 1, '' )
), 0) as faultCodes,
dbo.wfn_GetUDFNvarchar ('Category','Products', Products.Product) AS Category
,ProductId
FROM Products)If the ItemQty is 0 - what should the calculation be? I am not sure why you need to know the ratio between the items sold and 100 to determine the percentage of returned items. But - if that is the calculation you want:
=Fields!ReturnedQty.Value * (100 / IIf(Fields!ItemQty.Value = 0, 100, Fields!ItemQty.Value))
If sold items = 0, returned = 90: 90 * (100 / 100) = 90
If sold items = 1, returned = 90: 90 * (100 / 1) = 9000
If sold items = 125, returned = 90: 90 * (100 / 125) = 72
If you change your formula to:
=Fields!ReturnedQty.Value / IIf(Fields!ItemQty.Value = 0, 100, Fields!ItemQty.Value)
If sold items = 0, returned = 90: 90 / 100 = .9
If sold items = 1, returned = 90: 90 / 1 = 90
If sold items = 125, returned = 90: 90 / 125 = .72
Format the field in SSRS as percentage and the .72 will be display as 72%.
We have a winner , Thank you so much . seems simple when I look at it now
