Prev: Sending Report with Dynamic Report Name
Next: Differential backup
From: Sean L on 8 Jan 2010 02:11
Hi,
Using Reporting Services 2008
I have a matrix for which i have inserted a calculated field. Because there
are 0 values in some of the fields, i had to use an
IIF expression to ensure no "Divide by Zero" or "infinity results in the
calculation". The expression looks like this:
Field A = text
Field B = text
Field C = Summed Numeric
Field D = Summed Numeric
Field E = Summed Numeric
Field F = Calculated IIF Expression (Field C/Field D) - Expression =
IIF(Fields!C.Value = 0, 0, (Fields!C.Value / IIf(Fields!D.Value = 0, 1,
Fields!D.Value)))
Field G = Calculated IIF Expression (Field E/Field D) - Expression =
IIF(Fields!E.Value = 0, 0, (Fields!E.Value / IIf(Fields!D.Value = 0, 1,
Fields!D.Value)))

The expression works fine for all the rows and returns a 0.00 when ever
Fields C,D or E are 0.
The problem i am having is that i have inserted a total row and a few sub
total rows, and the results in Fields E and F now return an error : #error
How do i get around this? I tried adding the sum function into the
expression, but get an error that aggregate functions are not allowed in a
IIF expression

Sean



From: Rictastic on 8 Jan 2010 07:07
On 8 Jan, 07:11, "Sean L" <se...(a)centriq.co.za> wrote:
> Hi,
> Using Reporting Services 2008
> I have a matrix for which i have inserted a calculated field. Because there
> are 0 values in some of the fields, i had to use an
> IIF expression to ensure no "Divide by Zero" or "infinity results in the
> calculation". The expression looks like this:
> Field A = text
> Field B = text
> Field C = Summed Numeric
> Field D  = Summed Numeric
> Field E = Summed Numeric
> Field F = Calculated IIF Expression (Field C/Field D) - Expression =
> IIF(Fields!C.Value = 0, 0, (Fields!C.Value / IIf(Fields!D.Value = 0, 1,
> Fields!D.Value)))
> Field G = Calculated IIF Expression (Field E/Field D) - Expression =
> IIF(Fields!E.Value = 0, 0, (Fields!E.Value / IIf(Fields!D.Value = 0, 1,
> Fields!D.Value)))
>
> The expression works fine for all the rows and returns a 0.00 when ever
> Fields C,D or E are 0.
> The problem i am having is that i have inserted a total row and a few sub
> total rows, and the results in Fields E and F now return an error : #error
> How do i get around this? I tried adding the sum function into the
> expression, but get an error that aggregate functions are not allowed in a
> IIF expression
>
> Sean

Try this:

http://www.reportingservicestips.co.uk/RSTip008.html

From: Sean L on 8 Jan 2010 08:14
Thanx,
For those still with some issues, here is some samples:

To use the Code.Function: I Needed to modify it slightly as the numerator or
denominator could be 0: Code used as follows:
_______________________________________
Function CalculateFraction(ByVal Numerator As Double, ByVal Denominator As
Double) As Double

If Denominator = 0 Then
CalculateFraction = 0
ElseIf Numerator = 0 Then
CalculateFraction = 0
Else

CalculateFraction = Numerator / Denominator

End If

Return CalculateFraction

End Function

**Additionally, as this was for Subtotal and Total and total rows, i changed
the expression in the field from :
= Code.CalculateFraction(Fields!Numerator.Value, Fields!Denominator.Value)
To:
=Code.CalculateFraction(SUM(Fields!Numerator.Value),
SUM(Fields!Denominator.Value))
________________________________________________________


Alternatively, i put an expression directly into the field (and not by
adding calculated field to the dataset). The original IIF expressions worked
with a minor change - Just added the SUM to each Fields!value:
Field F = IIF(SUM(Fields!C.Value) = 0, 0, (SUM(Fields!C.Value) /
IIf(SUM(Fields!D.Value) = 0, 1,
(SUM(Fields!D.Value))))


Thanx to Rictastic for pointing in the right direction


"Rictastic" <rickdayman(a)gmail.com> wrote in message
news:ff9a8e04-6ebb-46c3-83d0-358c55065b66(a)22g2000yqr.googlegroups.com...
> On 8 Jan, 07:11, "Sean L" <se...(a)centriq.co.za> wrote:
>> Hi,
>> Using Reporting Services 2008
>> I have a matrix for which i have inserted a calculated field. Because
>> there
>> are 0 values in some of the fields, i had to use an
>> IIF expression to ensure no "Divide by Zero" or "infinity results in the
>> calculation". The expression looks like this:
>> Field A = text
>> Field B = text
>> Field C = Summed Numeric
>> Field D = Summed Numeric
>> Field E = Summed Numeric
>> Field F = Calculated IIF Expression (Field C/Field D) - Expression =
>> IIF(Fields!C.Value = 0, 0, (Fields!C.Value / IIf(Fields!D.Value = 0, 1,
>> Fields!D.Value)))
>> Field G = Calculated IIF Expression (Field E/Field D) - Expression =
>> IIF(Fields!E.Value = 0, 0, (Fields!E.Value / IIf(Fields!D.Value = 0, 1,
>> Fields!D.Value)))
>>
>> The expression works fine for all the rows and returns a 0.00 when ever
>> Fields C,D or E are 0.
>> The problem i am having is that i have inserted a total row and a few sub
>> total rows, and the results in Fields E and F now return an error :
>> #error
>> How do i get around this? I tried adding the sum function into the
>> expression, but get an error that aggregate functions are not allowed in
>> a
>> IIF expression
>>
>> Sean
>
> Try this:
>
> http://www.reportingservicestips.co.uk/RSTip008.html
>
 | 
Pages: 1
Prev: Sending Report with Dynamic Report Name
Next: Differential backup