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 -

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 -