DNN DAL SqlDataProvider Passing NULL to SQL Stored Procedure

Hello,

I'm trying to pass a null object to a stored procedure to update a SQL Table boolean field with a null value. My SQL Table boolean column allows nulls.

I'm using an InfoObject which has several properties all corresponding to fields in the SQL Table. One of those fields is a boolean. I create an instance of the InfoObject in my code and assigns values to the various properties. The boolean property in question (call it InfoOjbect.BooleanProperty) is not assigned anything. I then call my StoredProcedure passing the InfoObject to it (using the DotNetNuke DAL architecture) and the final result is the Table's boolean column is populated with a 0 and not a NULL. If I explicitly define the InfoObject.BooleanProperty = null.nullboolean before passing it to the Stored Procedure, the same thing happens. How do I pass a null to the SQL database for a boolean field? I've tried making InfoObject.BooleanProperty = dbnull.value but it won't let me do this saying "dbnull cannot be converted to a boolean." Do I have to explicitly create my InfoObject properties to allow for a null to be assigned to it?

Any help would be greatly appreciated. I'm using the DotNetNuke DAL architecture passing my InfoObject through a dataprovider to the sqldataprovider which calls the SQL Stored Procedure to add the new record to the Table.

Thanks in advance for any help.Please help?!|||The issue was with my InfoObject construction. DNN Core Team provided the solution. You can see it athttp://www.dotnetnuke.com/Community/ForumsDotNetNuke/tabid/795/forumid/118/threadid/41618/threadpage/3/scope/posts/Default.aspx

DMX Queries with MS Time Series

Hi!

I have a table Month_Sales(Month, product_1, .., product_n). The value of column product_i is the sale in this month.

so when i build MS Time Series for this domain, i want to query to find top m product is seld most in next month?

How do i buid that query?

DMX doesn't provide a way to order by the forecast value. However, you should be able to easily do this in client side code. Assuming your model looks like this:

CREATE MINING MODEL SalesForecast(
Product TEXT KEY,
Month LONG KEY TIME,
Sales LONG CONTINUOUS)
USING Microsoft_Time_Series

The following query will return the prediction for the next month for all products:

SELECT Product, Sales from SalesForecast

You can sort the query result by Sales in client code and pick the top N..

DLL Error & Legend Question

I'm using version 8.5.
Is there a way to limit the legend to only show elements that are shown on the chart (i.e have a value)?

Also I'm recieving the following error when I attempt to generate a report into PDF:

"ERROR IN DATABASE DLL, CHECK REPORT'S SQL"

The report sql works fine in Query Analzyer, the database is up to and it is verified.

Any advice is greatly appreciated,

Thanks,
GalahadHi,
Change your database driver and try once again.

Regards

Deepak|||Yes i had tried that. But whenever i try to change to any other driver than pdsodbc.dll (ODBC), i get errors that are the same as before.

I'm accessing SQL Server data so I have changed to every driver that i know that would work w/ sql server. But no luck.

Other reports that I run have no issues using the same driver.

FYI, The reports are run locally on my machine for testing and migrated over to a dev server. A java client converts the Crystal into PDF files.

Any suggestions?

Thanks,
Galahad|||Open the report and Do verify Database
Set the Database Location path also|||Dear all in this discussion
i am also stuck with this problem , for an immediate solution , the DNS connection , along with a set location works , but the report speed is slow

The senario, the rpt files are in the local machine , the server has the database , when the report is requested it gives the error "Physical database not found"
please let me know the solution

Division returns 10 instead of value with decimals

I have a textbox with a value like this:
="The average score is: " & 100/9.5 & " points."
I've tried all different conversion functions I can think of but with no
result.
/PeterPeter Larsson wrote:
> I have a textbox with a value like this:
> ="The average score is: " & 100/9.5 & " points."
> I've tried all different conversion functions I can think of but with no
> result.
> /Peter
Try this:
="The average score is: " & FormatNumber(100/9.5,2) & " points."
The second argument (2) returns the number of digits past the decimal.|||Michael, thanks for replying!
Your suggestion works fine (="The average score is: " & 100/9.5 & "
points." I simplified the calculation in my original posting.
Now I have the number of decimals correct, but still only an integer
value like 1.00
The thing is that I use this in a footer row of a table, it looks
exactly like this:
="The average score is: " &
FormatNumber(SUM(Fields!Score.Value*Fields!Exp.Value)/First(Fields!Count,"NumberOfPlayers"),2)
& " points."
The values left of the \ sign is from the table dataset itself, the
NumberOfPlayers dataset is not.
I guess I have to some conversion in the expression, but I can't find
the correct function. :(
ANy ideas?
Best regards,
Peter Larsson!
Michael wrote:
> Peter Larsson wrote:
>> I have a textbox with a value like this:
>> ="The average score is: " & 100/9.5 & " points."
>> I've tried all different conversion functions I can think of but with
>> no result.
>> /Peter
> Try this:
> ="The average score is: " & FormatNumber(100/9.5,2) & " points."
> The second argument (2) returns the number of digits past the decimal.|||On Feb 11, 9:47=A0am, Peter Larsson <scape...@.hotmail.com> wrote:
> Michael, thanks for replying!
> Your suggestion works fine (=3D"The average score is: " & 100/9.5 & "
> points." I simplified the calculation in my original posting.
> Now I have the number of decimals correct, but still only an integer
> value like 1.00
> The thing is that I use this in a footer row of a table, it looks
> exactly like this:
> =3D"The average score is: " &
> FormatNumber(SUM(Fields!Score.Value*Fields!Exp.Value)/First(Fields!Count,"=N=ADumberOfPlayers"),2)
> & " points."
> The values left of the \ sign is from the table dataset itself, the
> NumberOfPlayers dataset is not.
> I guess I have to some conversion in the expression, but I can't find
> the correct function. :(
> ANy ideas?
> Best regards,
> Peter Larsson!
>
> Michael wrote:
> > Peter Larsson wrote:
> >> I have a textbox with a value like this:
> >> =3D"The average score is: " & 100/9.5 & " points."
> >> I've tried all different conversion functions I can think of but with
> >> no =A0 result.
> >> /Peter
> > Try this:
> > =3D"The average score is: " & FormatNumber(100/9.5,2) & " points."
> > The second argument (2) returns the number of digits past the decimal.- =Hide quoted text -
> - Show quoted text -
I would make sure that the Data returned is a Floating point number,
and not Integer somewhere along the line. For example, your
denominator First(Fields!Count.Value) may actually be returning an
Integer (since Counts are typically whole numbers) and thus dividing
by an Integer is an Integer, which have no decimal places, hense the
"1.000"
=3D"The average score is: " &
Format( Sum(Fields!Score.Value * Fields!Exp.Value )
/ CDbl(First(Fields!Count.Value,"N=ADumberOfPlayers")), "0.00" ) & "
points."
-- Scott|||Orne wrote:
> On Feb 11, 9:47 am, Peter Larsson <scape...@.hotmail.com> wrote:
>> Michael, thanks for replying!
>> Your suggestion works fine (="The average score is: " & 100/9.5 & "
>> points." I simplified the calculation in my original posting.
>> Now I have the number of decimals correct, but still only an integer
>> value like 1.00
>> The thing is that I use this in a footer row of a table, it looks
>> exactly like this:
>> ="The average score is: " &
>> FormatNumber(SUM(Fields!Score.Value*Fields!Exp.Value)/First(Fields!Count,"N­umberOfPlayers"),2)
>> & " points."
>> The values left of the \ sign is from the table dataset itself, the
>> NumberOfPlayers dataset is not.
>> I guess I have to some conversion in the expression, but I can't find
>> the correct function. :(
>> ANy ideas?
>> Best regards,
>> Peter Larsson!
>>
>> Michael wrote:
>> Peter Larsson wrote:
>> I have a textbox with a value like this:
>> ="The average score is: " & 100/9.5 & " points."
>> I've tried all different conversion functions I can think of but with
>> no result.
>> /Peter
>> Try this:
>> ="The average score is: " & FormatNumber(100/9.5,2) & " points."
>> The second argument (2) returns the number of digits past the decimal.- Hide quoted text -
>> - Show quoted text -
> I would make sure that the Data returned is a Floating point number,
> and not Integer somewhere along the line. For example, your
> denominator First(Fields!Count.Value) may actually be returning an
> Integer (since Counts are typically whole numbers) and thus dividing
> by an Integer is an Integer, which have no decimal places, hense the
> "1.000"
> ="The average score is: " &
> Format( Sum(Fields!Score.Value * Fields!Exp.Value )
> / CDbl(First(Fields!Count.Value,"N­umberOfPlayers")), "0.00" ) & "
> points."
> -- Scott
Hi Scott,
Well, now it suddenly works, in the following format
formatnumber((val1*val2)/val3,2) without any conversion to double, all
numbers are integer. This is really strange because I tried it many times.
I just hate when stuff just suddenly start working again, and you have
no clue what was wrong.
Thanks for helping though. :)
Regards,
Peter Larsson!

Division returning 1 or 0

Why is this giving me a 1 or 0 in SSRS 2005 ? I should be getting .25

=Fields!FeeSchedule.Value / 100
it should be doing 25 / 100 = .25

I had N0 as the format on my expression field which is why...solved.

Division Problem

Hi there,

I need some help on a query I'm trying to build. I have two columns, one has the current months value and the other has the previous months value. I only want to select the rows where the previous and current values differ by more than 10%. The problem I have is that some of the rows have values of 0.

Any help is appreciated

I am not sure what error you are getting or what is the expected behavior. From what I can understand, I will try to answer your queries.

If some rows have 0 value then you will get divide by zero errors. You can avoid this by doing one of the following:

1. use an expression like x/nullif(y, 0). This will prevent divide by zero errors and return NULL. You can further modify the NULL value like coalesce(x/nullif(y, 0), -1) or any other appropriate value
2. You can use SET ARITHABORT OFF setting to return NULL for such division errors. This is not a recommended way since this setting can affect use of indexes on computed columns or indexed views for example. Best is to handle the calculations correctly in your expressions as described in step #1

The other problem might be that you are performing integer arithmetic. SQL Server by default uses the data types of the involved variables/columns/values in the expression to determine how it performs the arithmetic operation. You can find more details in Books Online. So if both your columns are integer data types then dividing them will be based on integer arithmetic which may not produce the expected results. You can do one of the following:

1. coerce data type of one value to desired numeric data type so that the resulting expression is not integer value. Ex: x/cast(y as float) or more explicit cast(x as numeric(10, 0))/cast(y as numeric(10,0))
2. Or modify existing value using floating point operations for example to change the behavior. Ex: x/(y * 1.) will result in floating point value for the expression

The first method is preferable since you can explicitly control the resulting data type without letting SQL Server to interpret 1. and assign default precision/scale for the value. For more details, look in the using decimal/float data in Books Online.

|||Thanks for the reply.

I have, however, simplified the problem somewhat. I can strip out the chance of it dividing by zero by changing the way I compute the difference. For example,

Instead of using

<current value> / <Previous value> * 100 >= 10

to get the percentage.

I can use

(<current value> - <Previous value>) >= <current value> * 0.1

However the previous value may be bigger than the current so i also have to check

(<current value> - <Previous value>) <= (<current value> * 0.1) * -1

Therefor there is no division.

Only question I can think of now is, is there a way to force the result of a calculation to be positive?
|||Yes, try the ABS(numeric_expression) function.|||

first. . . why two columns. . . this is not normalized. . .

you should have

Table: MonthlyTaskData:
TaskID|MonthID | MonthValue

any other schema is plain wrong!!!

[rant over]

given @.CurrMonthID and @.PrevMonthID

select cur.TaskID, cur.MonthID, cur.MonthValue
from MonthData cur left join MonthData prev on
cur.TaskID = prev.TaskID
where
(
prev.MonthValue = 0 and
cur.MonthID = @.CurMonthID and
Prev.MonthID = @.PrevMonthID ) or
prev.TaskID is null -- has no previuous month
union
select cur.TaskID, cur.MonthID, cur.MonthValue
from MonthData cur inner join MonthData prev on
cur.TaskID = prev.TaskID
where
(
prev.MonthValue <> 0 and
cur.MonthID = @.CurMonthID and
Prev.MonthID = @.PrevMonthID
) and
(
(abs(prev.MonthValue - cur.MonthValue)/ prev.MonthValue) > .1
)

|||Sorry, my bad.

This is actually a stored procedure where the current month value is returned from the table and the previous months value is returned by something like you have above.

I was merely giving a hypothetical example that was simplified to show the problem I was trying to solve so I could take that solution and apply it to my stored procedure.

Thank you for the repsonse, it's good to know someone is paying attention :)|||oh. . . and my query has a mistake -

select cur.TaskID, cur.MonthID, cur.MonthValue
from MonthData cur left join MonthData prev on
cur.TaskID = prev.TaskID
where
(
prev.MonthValue = 0 and
cur.MonthID = @.CurMonthID and
Prev.MonthID = @.PrevMonthID ) or
-- HERES THE CHANGE
(prev.TaskID is null and cur.MonthID = @.CurMonthID) -- has no previuous month
union
select cur.TaskID, cur.MonthID, cur.MonthValue
from MonthData cur inner join MonthData prev on
cur.TaskID = prev.TaskID
where
(
prev.MonthValue <> 0 and
cur.MonthID = @.CurMonthID and
Prev.MonthID = @.PrevMonthID
) and
(
(abs(prev.MonthValue - cur.MonthValue)/ prev.MonthValue) > .1
)

Division Problem

Hi there,

I need some help on a query I'm trying to build. I have two columns, one has the current months value and the other has the previous months value. I only want to select the rows where the previous and current values differ by more than 10%. The problem I have is that some of the rows have values of 0.

Any help is appreciated

I am not sure what error you are getting or what is the expected behavior. From what I can understand, I will try to answer your queries.

If some rows have 0 value then you will get divide by zero errors. You can avoid this by doing one of the following:

1. use an expression like x/nullif(y, 0). This will prevent divide by zero errors and return NULL. You can further modify the NULL value like coalesce(x/nullif(y, 0), -1) or any other appropriate value
2. You can use SET ARITHABORT OFF setting to return NULL for such division errors. This is not a recommended way since this setting can affect use of indexes on computed columns or indexed views for example. Best is to handle the calculations correctly in your expressions as described in step #1

The other problem might be that you are performing integer arithmetic. SQL Server by default uses the data types of the involved variables/columns/values in the expression to determine how it performs the arithmetic operation. You can find more details in Books Online. So if both your columns are integer data types then dividing them will be based on integer arithmetic which may not produce the expected results. You can do one of the following:

1. coerce data type of one value to desired numeric data type so that the resulting expression is not integer value. Ex: x/cast(y as float) or more explicit cast(x as numeric(10, 0))/cast(y as numeric(10,0))
2. Or modify existing value using floating point operations for example to change the behavior. Ex: x/(y * 1.) will result in floating point value for the expression

The first method is preferable since you can explicitly control the resulting data type without letting SQL Server to interpret 1. and assign default precision/scale for the value. For more details, look in the using decimal/float data in Books Online.

|||Thanks for the reply.

I have, however, simplified the problem somewhat. I can strip out the chance of it dividing by zero by changing the way I compute the difference. For example,

Instead of using

<current value> / <Previous value> * 100 >= 10

to get the percentage.

I can use

(<current value> - <Previous value>) >= <current value> * 0.1

However the previous value may be bigger than the current so i also have to check

(<current value> - <Previous value>) <= (<current value> * 0.1) * -1

Therefor there is no division.

Only question I can think of now is, is there a way to force the result of a calculation to be positive?
|||Yes, try the ABS(numeric_expression) function.|||

first. . . why two columns. . . this is not normalized. . .

you should have

Table: MonthlyTaskData:
TaskID|MonthID | MonthValue

any other schema is plain wrong!!!

[rant over]

given @.CurrMonthID and @.PrevMonthID

select cur.TaskID, cur.MonthID, cur.MonthValue
from MonthData cur left join MonthData prev on
cur.TaskID = prev.TaskID
where
(
prev.MonthValue = 0 and
cur.MonthID = @.CurMonthID and
Prev.MonthID = @.PrevMonthID ) or
prev.TaskID is null -- has no previuous month
union
select cur.TaskID, cur.MonthID, cur.MonthValue
from MonthData cur inner join MonthData prev on
cur.TaskID = prev.TaskID
where
(
prev.MonthValue <> 0 and
cur.MonthID = @.CurMonthID and
Prev.MonthID = @.PrevMonthID
) and
(
(abs(prev.MonthValue - cur.MonthValue)/ prev.MonthValue) > .1
)

|||Sorry, my bad.

This is actually a stored procedure where the current month value is returned from the table and the previous months value is returned by something like you have above.

I was merely giving a hypothetical example that was simplified to show the problem I was trying to solve so I could take that solution and apply it to my stored procedure.

Thank you for the repsonse, it's good to know someone is paying attention :)|||oh. . . and my query has a mistake -

select cur.TaskID, cur.MonthID, cur.MonthValue
from MonthData cur left join MonthData prev on
cur.TaskID = prev.TaskID
where
(
prev.MonthValue = 0 and
cur.MonthID = @.CurMonthID and
Prev.MonthID = @.PrevMonthID ) or
-- HERES THE CHANGE
(prev.TaskID is null and cur.MonthID = @.CurMonthID) -- has no previuous month
union
select cur.TaskID, cur.MonthID, cur.MonthValue
from MonthData cur inner join MonthData prev on
cur.TaskID = prev.TaskID
where
(
prev.MonthValue <> 0 and
cur.MonthID = @.CurMonthID and
Prev.MonthID = @.PrevMonthID
) and
(
(abs(prev.MonthValue - cur.MonthValue)/ prev.MonthValue) > .1
)

Division by zero

Hi!

I have a field where i have to do a division. To be sure that the value will never be zero i have done this:

=IIf(Sum(Fields!Total_Amount_1.Value) <>0,
((Sum(Fields!Total_Amount_2.Value)-Sum(Fields!Total_Amount_1.Value)) / Abs(Sum(Fields!Total_Amount_1.Value))),"N/A")

When i run the report i get the "#Error" in the field.

I have done some test and notice that it seems that the problem is that the IIF try both condition (true condition and false condition). When i replace the ABS function by a number like 100 everything is working fine. So the error seems to be the division by zero but this is why i'm using a IIF.

How can i do this division only if the value is not 0 and when it's 0 return "N/A" ?

Thank !
I found a post with the same question as mine:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=188595&SiteID=1

This work fine!

Thanks

Division by 0 in query

In my query, there's a mathematical expression that takes a value from one table and divides it by another value (X).

The problem is that X can be 0 sometimes and then I get an error.

How can I prevent errors like this for the case of X=0?

In access I would use IIF function, but it doesn't appear in SQL SERVER views.

Thanks.

SELECT CASE X WHEN 0 THEN 0 ELSE 10/X -- your division here with the actual column ENDFROM Table

Thanks

-Mark post(s) as "Answer" that helped you

|||

Before you divide, you can check the value of X. Example,

If X = 0 then

response.write "Invalid division."

response.end

End If

|||

e_screw:

SELECT
CASE X
WHEN 0 THEN 0
ELSE 10/X -- your division here with the actual column
END
FROM Table

No Disrespect sir, There is no doubt that this query will work But my question is Dose it works even when filed (X) is null and at the same time checks for zero divisor ?

So my query will be like this :

SELECT *, CASE COALESCE (X, 0) WHEN 0 THEN 0 ELSE COALESCE (10 / X, 0) END AS Expr1
FROM Tbl_something

OR

SELECT *, ISNULL(10/ NULLIF (X, 0), 0) AS Expr1
FROM Tbl_something

Here 10 will be replaced with ur actual column i.e., diviedent and x will be divisor column

Regards,
Shri

|||

shrinidhi:

There is no doubt that this query will work But my question is Dose it works even when filed (X) is null and at the same time checks for zero divisor ?

Well, I was giving an example of how to do a conditional select using SELECT CASE in SQL. The datatype and its null validity should be checked by the user. I am not even checking if the datatype of column X is integer or varchar.

Thanks

|||

in your Selection Query

select like this

Select Isnull(X,1) from

if is only for division and multiplication

Dividing by 0

From sample posted, I tried the following but results in #error. Any
suggestion?
Here is the actual expression:
= Iif(Fields!cprice.Value - Fields!ccommission.Value = 0,0, (100 *
Fields!ccommission.Value/( Fields!cprice.Value- Fields!Average_Cost.Value)))I've had the same issue. To get around it, I've written a custom function
and then return the value to the report.
"Knolls" wrote:
> From sample posted, I tried the following but results in #error. Any
> suggestion?
> Here is the actual expression:
> = Iif(Fields!cprice.Value - Fields!ccommission.Value = 0,0, (100 *
> Fields!ccommission.Value/( Fields!cprice.Value- Fields!Average_Cost.Value)))|||this should work
= Iif(Fields!cprice.Value - Fields!ccommission.Value = 0,0, (100 *
Fields!ccommission.Value/iif(( Fields!cprice.Value-
Fields!Average_Cost.Value)=0,1,( Fields!cprice.Value-
Fields!Average_Cost.Value))))
"Knolls" wrote:
> From sample posted, I tried the following but results in #error. Any
> suggestion?
> Here is the actual expression:
> = Iif(Fields!cprice.Value - Fields!ccommission.Value = 0,0, (100 *
> Fields!ccommission.Value/( Fields!cprice.Value- Fields!Average_Cost.Value)))

Divided by zero

=IIF( Fields!DAILYRUNRT.Value > 0, Fields!DAILYRUNRT.Value /
Fields!GOL_DAILYRUNRT.Value * 100, 0)
Why this is throwing Divided by zero error
please help meBecause GOL_DAILYRUNRT.Value is zero? Perhaps you should put
IIF( Fields!GOL_DAILYRUNRT.Value > 0, Fields!DAILYRUNRT.Value /
Fields!GOL_DAILYRUNRT.Value * 100, 0)?
MC
"PrasantH" <PrasantH@.discussions.microsoft.com> wrote in message
news:AD3D844C-C52A-4205-84FE-3315AC41484E@.microsoft.com...
> =IIF( Fields!DAILYRUNRT.Value > 0, Fields!DAILYRUNRT.Value /
> Fields!GOL_DAILYRUNRT.Value * 100, 0)
> Why this is throwing Divided by zero error
> please help me|||As you are dividing by Fields!GOL_DAILYRUNRT.Value; I think you should
check if that is greater than zero.
Try this:
=IIF( Fields!GOL_DAILYRUNRT.Value = 0 ,0,Fields!DAILYRUNRT.Value/ IIF(
Fields!GOL_DAILYRUNRT.Value = 0,1, Fields!GOL_DAILYRUNRT.Value)
In any case, IIF is a function call and therefore all arguments are
evaluated before the function is invoked( which causes the division by zero
in expression).
"PrasantH" <PrasantH@.discussions.microsoft.com> wrote in message
news:AD3D844C-C52A-4205-84FE-3315AC41484E@.microsoft.com...
> =IIF( Fields!DAILYRUNRT.Value > 0, Fields!DAILYRUNRT.Value /
> Fields!GOL_DAILYRUNRT.Value * 100, 0)
> Why this is throwing Divided by zero error
> please help me|||You will need another ) at the end:
=IIF( Fields!GOL_DAILYRUNRT.Value = 0 ,0,Fields!DAILYRUNRT.Value/ IIF(
Fields!GOL_DAILYRUNRT.Value = 0,1, Fields!GOL_DAILYRUNRT.Value))
I have tried your expression as it is a more elegant solution to this
nagging problem than I have been using. Of course in my tests this morning I
could not get the simple expression IIF(FieldB.Value = 0, 0, FieldA.Value /
FieldB.Value) to fail. It returned zero instead of #error, NaN, infinity....
It seems as though RS evealuate both sides of the IIF statement sometimes
and only one side on other occassions. There seems to be no consistency at
all that I can figure out, however, I will definitely try your expression the
next time I need to code around this issue. Thanks for the posting.
"RA" wrote:
> As you are dividing by Fields!GOL_DAILYRUNRT.Value; I think you should
> check if that is greater than zero.
> Try this:
> =IIF( Fields!GOL_DAILYRUNRT.Value = 0 ,0,Fields!DAILYRUNRT.Value/ IIF(
> Fields!GOL_DAILYRUNRT.Value = 0,1, Fields!GOL_DAILYRUNRT.Value)
> In any case, IIF is a function call and therefore all arguments are
> evaluated before the function is invoked( which causes the division by zero
> in expression).
> "PrasantH" <PrasantH@.discussions.microsoft.com> wrote in message
> news:AD3D844C-C52A-4205-84FE-3315AC41484E@.microsoft.com...
> > =IIF( Fields!DAILYRUNRT.Value > 0, Fields!DAILYRUNRT.Value /
> > Fields!GOL_DAILYRUNRT.Value * 100, 0)
> > Why this is throwing Divided by zero error
> > please help me
>
>|||Note: IIF() is a function call. Therefore, all arguments get evaluated by
the CLR before the function is called. See also:
http://msdn.microsoft.com/library/en-us/vblr7/html/vafctiif.asp
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"B. Mark McKinney" <BMarkMcKinney@.discussions.microsoft.com> wrote in
message news:31975EF8-B448-4748-85DD-FBFFF54DB634@.microsoft.com...
> You will need another ) at the end:
> =IIF( Fields!GOL_DAILYRUNRT.Value = 0 ,0,Fields!DAILYRUNRT.Value/ IIF(
> Fields!GOL_DAILYRUNRT.Value = 0,1, Fields!GOL_DAILYRUNRT.Value))
> I have tried your expression as it is a more elegant solution to this
> nagging problem than I have been using. Of course in my tests this morning
I
> could not get the simple expression IIF(FieldB.Value = 0, 0, FieldA.Value
/
> FieldB.Value) to fail. It returned zero instead of #error, NaN,
infinity....
> It seems as though RS evealuate both sides of the IIF statement sometimes
> and only one side on other occassions. There seems to be no consistency at
> all that I can figure out, however, I will definitely try your expression
the
> next time I need to code around this issue. Thanks for the posting.
>
> "RA" wrote:
> > As you are dividing by Fields!GOL_DAILYRUNRT.Value; I think you should
> > check if that is greater than zero.
> >
> > Try this:
> > =IIF( Fields!GOL_DAILYRUNRT.Value = 0 ,0,Fields!DAILYRUNRT.Value/ IIF(
> > Fields!GOL_DAILYRUNRT.Value = 0,1, Fields!GOL_DAILYRUNRT.Value)
> >
> > In any case, IIF is a function call and therefore all arguments are
> > evaluated before the function is invoked( which causes the division by
zero
> > in expression).
> >
> > "PrasantH" <PrasantH@.discussions.microsoft.com> wrote in message
> > news:AD3D844C-C52A-4205-84FE-3315AC41484E@.microsoft.com...
> > > =IIF( Fields!DAILYRUNRT.Value > 0, Fields!DAILYRUNRT.Value /
> > > Fields!GOL_DAILYRUNRT.Value * 100, 0)
> > > Why this is throwing Divided by zero error
> > > please help me
> >
> >
> >|||Consider using CUSTOM CODE (look it up in the help docs) that can
globally solve this for your whole report with a function like this:
Function SafeDivide(byval onumer as Object, byval odenom as Object) as
Decimal
' author: jerry nixon
' purpose: divide and avoid div by zero errs
' version: 1.4
If onumer Is Nothing Then Return 0
If odenom Is Nothing Then Return 0
If isDbNull(onumer) Then Return 0
If isDbNull(odenom) Then Return 0
Dim inumer As Decimal
Dim idenom As Decimal
Try
inumer = Ctype(onumer, Decimal)
idenom = Ctype(odenom, Decimal)
Catch ex as Exception
Return 0
End Try
If Not isNumeric(inumer) Then Return 0
If Not isNumeric(idenom) Then Return 0
If idenom = 0 Then Return 0
Try
Return Decimal.Divide(inumer, idenom)
Catch ex As Exception
Return 0
End Try
End Function

Divide by zero with Sub Query

Hello,
I need to dived a number by a summed value from a subquery. Any
suggestions on how to trap for zeros would be appreciated.
Thanks in advance,
sck10
SELECT
tsp01.StoreRevenue /
SELECT SUM(tft02.TotalFunded)
FROM
MyTable Tsp02
WHERE (Tsp02.Track_ID = Tsp01.Track_ID)
GROUP BY Tsp02.Track_ID)
What do you want to do if you have found a 0?
Try
select 20 / nullif(2, 0) -- returns 10
select 20 / nullif(0, 0) -- returns NULL
Ben Nevarez, MCDBA, OCP
Database Administrator
"sck10" wrote:

> Hello,
> I need to dived a number by a summed value from a subquery. Any
> suggestions on how to trap for zeros would be appreciated.
> --
> Thanks in advance,
> sck10
> SELECT
> tsp01.StoreRevenue /
> SELECT SUM(tft02.TotalFunded)
> FROM
> MyTable Tsp02
> WHERE (Tsp02.Track_ID = Tsp01.Track_ID)
> GROUP BY Tsp02.Track_ID)
>
>
|||SELECT
CASE
WHEN SUM(tft02.TotalFunded)=0 THEN 0 ELSE
SUM(tsp01.StoreRevenue)/SUM(tft02.TotalFunded)
END
FROM tsp01, tsp02
where (tsp02.Track_ID = tsp01.Track_ID)
GROUP BY Tsp02.Track_ID
let me know if this works for you.
|||Thanks Zomer,
This worked...
"zomer" <noneee@.gmail.com> wrote in message
news:1143485957.082088.145680@.i39g2000cwa.googlegr oups.com...
> SELECT
> CASE
> WHEN SUM(tft02.TotalFunded)=0 THEN 0 ELSE
> SUM(tsp01.StoreRevenue)/SUM(tft02.TotalFunded)
> END
> FROM tsp01, tsp02
> where (tsp02.Track_ID = tsp01.Track_ID)
> GROUP BY Tsp02.Track_ID
> let me know if this works for you.
>

Divide by zero with Sub Query

Hello,
I need to dived a number by a summed value from a subquery. Any
suggestions on how to trap for zeros would be appreciated.
--
Thanks in advance,
sck10
SELECT
tsp01.StoreRevenue /
SELECT SUM(tft02.TotalFunded)
FROM
MyTable Tsp02
WHERE (Tsp02.Track_ID = Tsp01.Track_ID)
GROUP BY Tsp02.Track_ID)What do you want to do if you have found a 0?
Try
select 20 / nullif(2, 0) -- returns 10
select 20 / nullif(0, 0) -- returns NULL
Ben Nevarez, MCDBA, OCP
Database Administrator
"sck10" wrote:
> Hello,
> I need to dived a number by a summed value from a subquery. Any
> suggestions on how to trap for zeros would be appreciated.
> --
> Thanks in advance,
> sck10
> SELECT
> tsp01.StoreRevenue /
> SELECT SUM(tft02.TotalFunded)
> FROM
> MyTable Tsp02
> WHERE (Tsp02.Track_ID = Tsp01.Track_ID)
> GROUP BY Tsp02.Track_ID)
>
>|||SELECT
CASE
WHEN SUM(tft02.TotalFunded)=0 THEN 0 ELSE
SUM(tsp01.StoreRevenue)/SUM(tft02.TotalFunded)
END
FROM tsp01, tsp02
where (tsp02.Track_ID = tsp01.Track_ID)
GROUP BY Tsp02.Track_ID
let me know if this works for you.|||Thanks Zomer,
This worked...
"zomer" <noneee@.gmail.com> wrote in message
news:1143485957.082088.145680@.i39g2000cwa.googlegroups.com...
> SELECT
> CASE
> WHEN SUM(tft02.TotalFunded)=0 THEN 0 ELSE
> SUM(tsp01.StoreRevenue)/SUM(tft02.TotalFunded)
> END
> FROM tsp01, tsp02
> where (tsp02.Track_ID = tsp01.Track_ID)
> GROUP BY Tsp02.Track_ID
> let me know if this works for you.
>

Divide by zero with Sub Query

Hello,
I need to dived a number by a summed value from a subquery. Any
suggestions on how to trap for zeros would be appreciated.
--
Thanks in advance,
sck10
SELECT
tsp01.StoreRevenue /
SELECT SUM(tft02.TotalFunded)
FROM
MyTable Tsp02
WHERE (Tsp02.Track_ID = Tsp01.Track_ID)
GROUP BY Tsp02.Track_ID)What do you want to do if you have found a 0?
Try
select 20 / nullif(2, 0) -- returns 10
select 20 / nullif(0, 0) -- returns NULL
Ben Nevarez, MCDBA, OCP
Database Administrator
"sck10" wrote:

> Hello,
> I need to dived a number by a summed value from a subquery. Any
> suggestions on how to trap for zeros would be appreciated.
> --
> Thanks in advance,
> sck10
> SELECT
> tsp01.StoreRevenue /
> SELECT SUM(tft02.TotalFunded)
> FROM
> MyTable Tsp02
> WHERE (Tsp02.Track_ID = Tsp01.Track_ID)
> GROUP BY Tsp02.Track_ID)
>
>|||SELECT
CASE
WHEN SUM(tft02.TotalFunded)=0 THEN 0 ELSE
SUM(tsp01.StoreRevenue)/SUM(tft02.TotalFunded)
END
FROM tsp01, tsp02
where (tsp02.Track_ID = tsp01.Track_ID)
GROUP BY Tsp02.Track_ID
let me know if this works for you.|||Thanks Zomer,
This worked...
"zomer" <noneee@.gmail.com> wrote in message
news:1143485957.082088.145680@.i39g2000cwa.googlegroups.com...
> SELECT
> CASE
> WHEN SUM(tft02.TotalFunded)=0 THEN 0 ELSE
> SUM(tsp01.StoreRevenue)/SUM(tft02.TotalFunded)
> END
> FROM tsp01, tsp02
> where (tsp02.Track_ID = tsp01.Track_ID)
> GROUP BY Tsp02.Track_ID
> let me know if this works for you.
>

Divide by Zero error

I am still getting this error. This is my formula:
=iif(ReportItems!BUDMTD_2.Value=0,0,ReportItems!MTDChange_2.value/ReportItems!BUDMTD_2.Value)
This works in another report but not here.IIF is a function call. Therefore all arguments get evaluated before IIF is
called and you see a division by zero.
Possible solutions can be:
1) Avoid 0 in divisor (consider
=iif(ReportItems!BUDMTD_2.Value=0,0,ReportItems!MTDChange_2.value /
iif(ReportItems!BUDMTD_2.Value = 0, 1,ReportItems!BUDMTD_2.Value ))
2) Write VB function SafeDivide (A,B) and call it when needed (search
"SafeDivide" in this group)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"David" <David@.discussions.microsoft.com> wrote in message
news:2FC621DF-8C78-480F-A9E6-512A438B75B5@.microsoft.com...
>I am still getting this error. This is my formula:
> =iif(ReportItems!BUDMTD_2.Value=0,0,ReportItems!MTDChange_2.value/ReportItems!BUDMTD_2.Value)
> This works in another report but not here.|||David,
just in case it helps...I got so tired of writing statements like this
and trying to test them (because you finish up writing a lot of them)
that I wrote my division routines into a custom assembly and called the
custom assembly...the custom assembly then gives me much more control
over how I deal with bad data in calculations...
Peter
www.peternolan.com