Calculation Error

I am designing a report and am getting an error when I run this calculation
in the row. Here is my expression;
"=iif(Fields!Hours.Value > 0 and Fields!SUM.Value > 0,
Fields!SUM.Value/Fields!Hours.Value,0)"
Basically, I am doing a divide calculation of two columns, but sometimes
there is a zero in the columns which causes an error. In my expression
above, I am saying if column A and column B are >0, then divide, if not, then
0.
Any ideas on what I am doing wrong? I have about 100 reports that I am
cranking out, so learning a lot.
Thanks in advance,
RyanOn Oct 19, 10:11 am, Ryan Mcbee <RyanMc...@.discussions.microsoft.com>
wrote:
> I am designing a report and am getting an error when I run this calculation
> in the row. Here is my expression;
> "=iif(Fields!Hours.Value > 0 and Fields!SUM.Value > 0,
> Fields!SUM.Value/Fields!Hours.Value,0)"
> Basically, I am doing a divide calculation of two columns, but sometimes
> there is a zero in the columns which causes an error. In my expression
> above, I am saying if column A and column B are >0, then divide, if not, then
> 0.
> Any ideas on what I am doing wrong? I have about 100 reports that I am
> cranking out, so learning a lot.
> Thanks in advance,
> Ryan
If I remember correctly, > 0 does not exclude null; so, you might want
to change your expression to something like:
=iif(Fields!Hours.Value > 0 and Fields!Hours.Value <> Nothing and
Fields!SUM.Value > 0 and Fields!SUM.Value <> Nothing , Fields!
SUM.Value/Fields!Hours.Value, 0)
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||Check the article here for expression examples using the IIF statement:
http://msdn2.microsoft.com/en-us/library/ms157328.aspx
All of the examples I've seen have involved using one expression in each
IIF so if you are doing Fields!Hours.Value > 0 and Fields!SUM.Value > 0
then you would need two nested IIFs. Also, Enrique is correct. You should
check for NULL before doing any comparisions to make sure you're getting
valid data.
Here is an example expression you can try:
=iif(Fields!Hours.Value > 0, IIF(Fields!SUM.Value > 0,
Fields!SUM.Value/Fields!Hours.Value,0),0)
If you do think you'll have NULLs in your data use:
=iif(IsNothing(Fields!Hours.Value), 0,
iif(IsNothing(Fields!SUM.Value),0,iif(Fields!Hours.Value > 0,
IIF(Fields!SUM.Value > 0, Fields!SUM.Value/Fields!Hours.Value,0),0))
Didn't test the above so don't hammer me for any mistakes I may have put in
there :)
--
Chris Alton, Microsoft Corp.
SQL Server Developer Support Engineer
This posting is provided "AS IS" with no warranties, and confers no rights.
--
> Thread-Topic: Calculation Error
> thread-index: AcgSYkRUoT7qoNO7S9WCFbXNnevQBA==> X-WBNR-Posting-Host: 207.46.19.168
> From: =?Utf-8?B?UnlhbiBNY2JlZQ==?= <RyanMcbee@.discussions.microsoft.com>
> Subject: Calculation Error
> Date: Fri, 19 Oct 2007 08:11:04 -0700
> Lines: 15
> Message-ID: <562C6023-A07E-4FA8-83B4-2B486EDDE038@.microsoft.com>
> MIME-Version: 1.0
> I am designing a report and am getting an error when I run this
calculation
> in the row. Here is my expression;
> "=iif(Fields!Hours.Value > 0 and Fields!SUM.Value > 0,
> Fields!SUM.Value/Fields!Hours.Value,0)"
> Basically, I am doing a divide calculation of two columns, but sometimes
> there is a zero in the columns which causes an error. In my expression
> above, I am saying if column A and column B are >0, then divide, if not,
then
> 0.
> Any ideas on what I am doing wrong? I have about 100 reports that I am
> cranking out, so learning a lot.
> Thanks in advance,
> Ryan
>|||On Oct 22, 2:08 pm, cal...@.online.microsoft.com (Chris Alton [MSFT])
wrote:
> Check the article here for expression examples using the IIF statement:http://msdn2.microsoft.com/en-us/library/ms157328.aspx
> All of the examples I've seen have involved using one expression in each
> IIF so if you are doing Fields!Hours.Value > 0 and Fields!SUM.Value > 0
> then you would need two nested IIFs. Also, Enrique is correct. You should
> check for NULL before doing any comparisions to make sure you're getting
> valid data.
> Here is an example expression you can try:
> =iif(Fields!Hours.Value > 0, IIF(Fields!SUM.Value > 0,
> Fields!SUM.Value/Fields!Hours.Value,0),0)
> If you do think you'll have NULLs in your data use:
> =iif(IsNothing(Fields!Hours.Value), 0,
> iif(IsNothing(Fields!SUM.Value),0,iif(Fields!Hours.Value > 0,
> IIF(Fields!SUM.Value > 0, Fields!SUM.Value/Fields!Hours.Value,0),0))
> Didn't test the above so don't hammer me for any mistakes I may have put in
> there :)
> --
> Chris Alton, Microsoft Corp.
> SQL Server Developer Support Engineer
> This posting is provided "AS IS" with no warranties, and confers no rights.
> --
>
> > Thread-Topic: Calculation Error
> > thread-index: AcgSYkRUoT7qoNO7S9WCFbXNnevQBA==> > X-WBNR-Posting-Host: 207.46.19.168
> > From: =?Utf-8?B?UnlhbiBNY2JlZQ==?= <RyanMc...@.discussions.microsoft.com>
> > Subject: Calculation Error
> > Date: Fri, 19 Oct 2007 08:11:04 -0700
> > Lines: 15
> > Message-ID: <562C6023-A07E-4FA8-83B4-2B486EDDE...@.microsoft.com>
> > MIME-Version: 1.0
> > I am designing a report and am getting an error when I run this
> calculation
> > in the row. Here is my expression;
> > "=iif(Fields!Hours.Value > 0 and Fields!SUM.Value > 0,
> > Fields!SUM.Value/Fields!Hours.Value,0)"
> > Basically, I am doing a divide calculation of two columns, but sometimes
> > there is a zero in the columns which causes an error. In my expression
> > above, I am saying if column A and column B are >0, then divide, if not,
> then
> > 0.
> > Any ideas on what I am doing wrong? I have about 100 reports that I am
> > cranking out, so learning a lot.
> > Thanks in advance,
> > Ryan- Hide quoted text -
> - Show quoted text -
Another way to work around this is with a custom code function.
In Layout mode, click Report > Report Properties > Code to open the
Custom Code editor.
In the editor, type:
Public Function DivideBy(exp1, exp2)
If exp2 = 0 Then
DivideBy = Nothing
Else DivideBy = exp1/exp2
End If
End Function
Now whereever you need to divide (with the potential for divide by
zero errors) insert this expression:
=code.divideby(exp1,exp2)
For you it would be:
"=code.divideby(Fields!SUM.Value,Fields!Hours.Value)"
If your shop has SQL 2005 enterprise edition, you can create a custom
assembly out of the function.|||On Oct 23, 11:55 am, toolman <t...@.infocision.com> wrote:
> On Oct 22, 2:08 pm, cal...@.online.microsoft.com (Chris Alton [MSFT])
> wrote:
>
>
> > Check the article here for expression examples using the IIF statement:http://msdn2.microsoft.com/en-us/library/ms157328.aspx
> > All of the examples I've seen have involved using one expression in each
> > IIF so if you are doing Fields!Hours.Value > 0 and Fields!SUM.Value > 0
> > then you would need two nested IIFs. Also, Enrique is correct. You should
> > check for NULL before doing any comparisions to make sure you're getting
> > valid data.
> > Here is an example expression you can try:
> > =iif(Fields!Hours.Value > 0, IIF(Fields!SUM.Value > 0,
> > Fields!SUM.Value/Fields!Hours.Value,0),0)
> > If you do think you'll have NULLs in your data use:
> > =iif(IsNothing(Fields!Hours.Value), 0,
> > iif(IsNothing(Fields!SUM.Value),0,iif(Fields!Hours.Value > 0,
> > IIF(Fields!SUM.Value > 0, Fields!SUM.Value/Fields!Hours.Value,0),0))
> > Didn't test the above so don't hammer me for any mistakes I may have put in
> > there :)
> > --
> > Chris Alton, Microsoft Corp.
> > SQL Server Developer Support Engineer
> > This posting is provided "AS IS" with no warranties, and confers no rights.
> > --
> > > Thread-Topic: Calculation Error
> > > thread-index: AcgSYkRUoT7qoNO7S9WCFbXNnevQBA==> > > X-WBNR-Posting-Host: 207.46.19.168
> > > From: =?Utf-8?B?UnlhbiBNY2JlZQ==?= <RyanMc...@.discussions.microsoft.com>
> > > Subject: Calculation Error
> > > Date: Fri, 19 Oct 2007 08:11:04 -0700
> > > Lines: 15
> > > Message-ID: <562C6023-A07E-4FA8-83B4-2B486EDDE...@.microsoft.com>
> > > MIME-Version: 1.0
> > > I am designing a report and am getting an error when I run this
> > calculation
> > > in the row. Here is my expression;
> > > "=iif(Fields!Hours.Value > 0 and Fields!SUM.Value > 0,
> > > Fields!SUM.Value/Fields!Hours.Value,0)"
> > > Basically, I am doing a divide calculation of two columns, but sometimes
> > > there is a zero in the columns which causes an error. In my expression
> > > above, I am saying if column A and column B are >0, then divide, if not,
> > then
> > > 0.
> > > Any ideas on what I am doing wrong? I have about 100 reports that I am
> > > cranking out, so learning a lot.
> > > Thanks in advance,
> > > Ryan- Hide quoted text -
> > - Show quoted text -
> Another way to work around this is with a custom code function.
> In Layout mode, click Report > Report Properties > Code to open the
> Custom Code editor.
> In the editor, type:
> Public Function DivideBy(exp1, exp2)
> If exp2 = 0 Then
> DivideBy = Nothing
> Else DivideBy = exp1/exp2
> End If
> End Function
> Now whereever you need to divide (with the potential for divide by
> zero errors) insert this expression:
> =code.divideby(exp1,exp2)
> For you it would be:
> "=code.divideby(Fields!SUM.Value,Fields!Hours.Value)"
> If your shop has SQL 2005 enterprise edition, you can create a custom
> assembly out of the function.- Hide quoted text -
> - Show quoted text -
Actually,
Public Function DivideBy(exp1, exp2)
If exp2 = 0 Then
DivideBy = 0
Else DivideBy = exp1/exp2
End If
End Function
is better so you get the zero as output.