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....

Distribution Calculation

Hi

Can someone please tell me how I can take a set of sales orders and generate a table or set of data that show the percentage of sales per product which are in different sales ranges

eg Socks - Beige

50% Orders are above $100

20% Orders are above $90 and less than 100

30% Orders are above $60 and less than 90

Many thanks for your time

TV

This will work:

create table sale
(
saleId int identity,
value decimal(10,5)
)
go
insert into sale (value)
select 100
union all
select 110
union all
select 90
union all
select 20
union all
select 30
union all
select 100
union all
select 95
union all
select 35
union all
select 39
union all
select 20
go

select count(*),
sum(case when value >= 100 then 1.0 else 0 end) / count(*) * 100 as [100Up],
sum(case when value < 100 and value >= 90 then 1.0 else 0 end) /
count(*) * 100 as [between90and99.9],
sum(case when value < 100 then 1.0 else 0 end) / count(*) * 100 as [under90]
from sale

|||

Thankyou very much Louis

If I want this distribution per store

do i just add the following to it

select StoreID, count(*),
sum(case when value >= 100 then 1.0 else 0 end) / count(*) * 100 as [100Up],
sum(case when value < 100 and value >= 90 then 1.0 else 0 end) /
count(*) * 100 as [between90and99.9],
sum(case when value < 100 then 1.0 else 0 end) / count(*) * 100 as [under90]
from sale

Group By StoreID

|||That looks right to me.