Divde by Zero error

I to am getting the above error when trying to action the following
calculation.
=iif(CostValue=0,0,Profit/CostValue)
I have tried a number of the other solutions posted and these do not seem to
work in my instance.
Have tried to use Nz function but this is not included in Reporting
Services, also tried ISERROR this too failed.
Help me Obi Wan - you're my only hope....Try this:
=iif(Fields!CostValue.Value = 0, 0, Fields!Profit.Value /
iif(Fields!CostValue.Value = 0, 1, Fields!CostValue.Value))
Keep in mind that iif is a function call and therefore all arguments get
evaluated.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jules_Anime" <JulesAnime@.discussions.microsoft.com> wrote in message
news:0E7C5AA8-C96C-4CAE-97B5-67BF4BDF3C71@.microsoft.com...
> I to am getting the above error when trying to action the following
> calculation.
> =iif(CostValue=0,0,Profit/CostValue)
> I have tried a number of the other solutions posted and these do not seem
to
> work in my instance.
> Have tried to use Nz function but this is not included in Reporting
> Services, also tried ISERROR this too failed.
> Help me Obi Wan - you're my only hope....|||What is your data source? I trap divide by zero errors using a function in
SQL Server before the data is delivered to the report.
/*
Created by Vince Plaza
Last revised by Vince Plaza
Last revised on 6/29/2003
The divide by zero trap looks for a denominator of zero and skips the
division operation.
It also serves to round results to a desired number of decimal places.
*/
CREATE FUNCTION dbo.fnRPTS_DivideByZeroTrap (@.NUMERATOR AS FLOAT,
@.DENOMINATOR AS FLOAT, @.ROUND AS INT)
RETURNS FLOAT AS
BEGIN
DECLARE @.OUTPUT AS FLOAT
IF @.DENOMINATOR = 0
SELECT @.OUTPUT = 0
ELSE IF @.ROUND = -1 --Dont Round
SELECT @.OUTPUT = @.NUMERATOR/@.DENOMINATOR
ELSE
SELECT @.OUTPUT = ROUND((@.NUMERATOR*1.0)/ (@.DENOMINATOR*1.0),@.ROUND)
RETURN @.OUTPUT
END
"Jules_Anime" wrote:
> I to am getting the above error when trying to action the following
> calculation.
> =iif(CostValue=0,0,Profit/CostValue)
> I have tried a number of the other solutions posted and these do not seem to
> work in my instance.
> Have tried to use Nz function but this is not included in Reporting
> Services, also tried ISERROR this too failed.
> Help me Obi Wan - you're my only hope....|||Thanks Robert. This seemed to work fine.
Although I thought I had already followed this path, maybe I was "Lost in
Translation"
"Robert Bruckner [MSFT]" wrote:
> Try this:
> =iif(Fields!CostValue.Value = 0, 0, Fields!Profit.Value /
> iif(Fields!CostValue.Value = 0, 1, Fields!CostValue.Value))
> Keep in mind that iif is a function call and therefore all arguments get
> evaluated.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Jules_Anime" <JulesAnime@.discussions.microsoft.com> wrote in message
> news:0E7C5AA8-C96C-4CAE-97B5-67BF4BDF3C71@.microsoft.com...
> > I to am getting the above error when trying to action the following
> > calculation.
> >
> > =iif(CostValue=0,0,Profit/CostValue)
> >
> > I have tried a number of the other solutions posted and these do not seem
> to
> > work in my instance.
> >
> > Have tried to use Nz function but this is not included in Reporting
> > Services, also tried ISERROR this too failed.
> >
> > Help me Obi Wan - you're my only hope....
>
>|||Thks..I like the neatness of this solution, I think I went with a case
statment in the original SQL.
It ment however that I had to summarise the view firstly, and then report
from the summarised data.
Cheers.
"vmp_pdx" wrote:
> What is your data source? I trap divide by zero errors using a function in
> SQL Server before the data is delivered to the report.
> /*
> Created by Vince Plaza
> Last revised by Vince Plaza
> Last revised on 6/29/2003
> The divide by zero trap looks for a denominator of zero and skips the
> division operation.
> It also serves to round results to a desired number of decimal places.
> */
> CREATE FUNCTION dbo.fnRPTS_DivideByZeroTrap (@.NUMERATOR AS FLOAT,
> @.DENOMINATOR AS FLOAT, @.ROUND AS INT)
> RETURNS FLOAT AS
> BEGIN
> DECLARE @.OUTPUT AS FLOAT
> IF @.DENOMINATOR = 0
> SELECT @.OUTPUT = 0
> ELSE IF @.ROUND = -1 --Dont Round
> SELECT @.OUTPUT = @.NUMERATOR/@.DENOMINATOR
> ELSE
> SELECT @.OUTPUT = ROUND((@.NUMERATOR*1.0)/ (@.DENOMINATOR*1.0),@.ROUND)
> RETURN @.OUTPUT
> END
>
> "Jules_Anime" wrote:
> > I to am getting the above error when trying to action the following
> > calculation.
> >
> > =iif(CostValue=0,0,Profit/CostValue)
> >
> > I have tried a number of the other solutions posted and these do not seem to
> > work in my instance.
> >
> > Have tried to use Nz function but this is not included in Reporting
> > Services, also tried ISERROR this too failed.
> >
> > Help me Obi Wan - you're my only hope....