Prev: Confused by Role Assignment for individual reports
Next: Error Message:
From: Sean L on 15 Feb 2010 01:10
There are 2 ways you can do this:
You could use a function, as it is a little cleaner than using the nested
IIF's

Sample : Just modify to suit your needs: (to add a function to your report,
right click in the area outside the report page and choose report
properties. Goto the code section and insert it there.)

Public Function CalcRatio(ByVal Numerator As Object, ByVal Denominator As
object, ByVal DivZeroDefault As Object) As Object

If Denominator <> 0 Then

Return Numerator/Denominator

Else

Return 0

End If

End Function


To use the function, enter code.CalcRatio(Fields!difference.Value
,Fields!total.Value )

hope this helps

"Andre" <no(a)spam.com> wrote in message
news:uiGvTznqKHA.3792(a)TK2MSFTNGP05.phx.gbl...
> Uri - clicking save password works - I wasn't entering my credentials in
> the Edit section, but rather I was entering them in the Credentials tab,
> where they weren't saved. I do use BIDS.
>
> Also thanks for the tip on RunningValue. I looked all over RS but didn't
> find this. Just like last night I couldn't find a way to format a number
> as "thousand separator with no decimals". A quick search of the web found
> the Format Code N0 does what I wanted. It's disappointing that the
> regular format editor doesn't have this as a choice and I was forced to
> scan the web for such a simple thing. So my question is where do I find a
> good tutorial/resource for such things as RunningValue? I wonder what
> other things are available that I don't know about?
>
> If I may ask another question you can probably help with...
> In Crystal I have a formula field with this code:
>
> If {rs;1.difference} = 0 Then 0
> Else
> if {rs;1.total} > 0 then
> ({rs;1.difference} / {rs;1.total})
>
> I tried to convert that to an expression in a field using this code:
>
> =IIF(Fields!difference.Value = 0, 0,
> IIF(Fields!total.Value > 0, (Fields!difference.Value /
> Fields!total.Value), 0))
>
>
> The problem is every time the report is run, it gives me a divide by zero
> error.
> The values where it has #Error are:
> Total: 0
> Difference: -6857
>
> From what I can tell the IIF formula should work; am I overlooking
> something?
>
>
> Thanks for your help - it's much appreciated.
>
> Andre
>
From: Andre on 18 Feb 2010 01:13
Andrew - that worked great, thank you. I'm sure I'll use this a lot!

Andre
From: Andre on 22 Feb 2010 01:16
Another thing Crystal lets me to is to suppress either a header or footer
group. From what I can see I can only suppress the entire group in RS. Is
that true, or is there a way to programmatically suppress a header or
footer? There are times I want to show the header, but suppress the footer.
The code I have in CR to suppress a footer group is this:

isnull({myrs.level3_name})
or {myrs.level3_name} = ''

Is there a way to use similar code to suppress a group footer in RS?

Thanks, Andre

From: Andre on 22 Feb 2010 01:54
Nevermind - I figured it out. I just found the "visibility" section on the
group footer, and it works great with an expression.

Andre

From: Andre on 23 Feb 2010 03:00
Sean,

Thank you for your help with the function. I thought I was going to get
away without using it but I found my calculations were not working, so I
needed to use it.

I copied exactly what you put and got an error. I removed the "ByVal
DivZeroDefault As Object" piece of code and it works great. The only thing
I'm having an issue with is the summary totals. I used the same code to
call the function and essentially what it's doing is repeating the first
detail line's amount in the summary line. Is there different code I need to
use to call the function in a summary line?


Separately, I think RS has a bug evaluating the IIF statement. When I found
my summary lines were not calculating properly, before I switched to your
function, I tried to use nested IIF's. I started getting the "divide by
zero" error again, so I simplified the calculation.

This works fine.
=IIF(Fields!difference.Value = 0, 0,
IIF(Fields!total.Value = 0, 0, 1 ))

On the line where Fields!difference.Value <>0 and Fields!total.Value = 0, I
get a 0 for my percent, which is correct.

If I change the formula to this, I get a "divide by zero" error. All I've
done is change the formula to divide "difference/value", instead of
returning 1. I suspect that RS is doing the division too soon, and it's
throwing the error.
=IIF(Fields!difference.Value = 0, 0,
IIF(Fields!total.Value = 0, 0, Fields!difference.Value /
Fields!total.Value ))


Andre

First  |  Prev  |  Next  |  Last
Pages: 1 2 3 4
Prev: Confused by Role Assignment for individual reports
Next: Error Message: