Print Page | Close Window

if help

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=18215
Printed Date: 05 May 2024 at 1:49am


Topic: if help
Posted By: fabrizio
Subject: if help
Date Posted: 10 Dec 2012 at 1:38am
Hi guys  !!!!
 
Im a new member!!!! I have never used crystal reports 2008 before but my boss asked me to learn it as soon as possibleCry.
 
I have a problem with IF formula (Basic Syntax):
 
if "J10B" in {VR_CLASSIFICA_PS.ID_ATT_ORIG}  and {#countCapre}>0 then
    formula = 0
elseIf "J09B" in {VR_CLASSIFICA_PS.ID_ATT_ORIG} And {#countePecore}>0 THEN
    formula = 0
elseif "J11"in{VR_CLASSIFICA_PS.ID_ATT_ORIG} and {#countScrofe}>0 then
    formula = 0
elseif "J02" in {VR_CLASSIFICA_PS.ID_ATT_ORIG} and ({#nr_Vitellini}<={#nrVacche}) then
    formula = 0
elseif "J02" in {VR_CLASSIFICA_PS.ID_ATT_ORIG} and ({#nr_Vitellini}>{#nrVacche}) then
    formula = ({#nr_Vitellini}-{#nrVacche})*{VR_CLASSIFICA_PS.PS} 
else
    formula ={VR_CLASSIFICA_PS.PS_AGG_NO_P}
end if
 
the program tells me the formula is correct but I don't get back any result but just blank cell.
 
 
Please help me !!!!!!!!!!!!!!!!!
 
 
 


Replies:
Posted By: comatt1
Date Posted: 10 Dec 2012 at 4:18am
"J10B" in {VR_CLASSIFICA_PS.ID_ATT_ORIG}

I believe you have reversed logic. You dont need to use 'in' here.

IF {VR_CLASSIFICA_PS.ID_ATT_ORIG} like "*J10B*" ....
also you don't have to assign a value to anything. Just say

IF {FIELD} like 'BLAH' then
0
else
1

.... If you try this with Crystal Syntax I think it will work

if ({VR_CLASSIFICA_PS.ID_ATT_ORIG} like '*J10B*' and {#countCapre}>0) or
({VR_CLASSIFICA_PS.ID_ATT_ORIG} like '*J09B*' And {#countePecore}>0) or
({VR_CLASSIFICA_PS.ID_ATT_ORIG} like '*J11*' and {#countScrofe}>0) or
({VR_CLASSIFICA_PS.ID_ATT_ORIG} like '*J02*' and ({#nr_Vitellini}<={#nrVacche}))
then
0
else
if {VR_CLASSIFICA_PS.ID_ATT_ORIG} like '*J02*' and ({#nr_Vitellini}>{#nrVacche}) then
({#nr_Vitellini}-{#nrVacche})*{VR_CLASSIFICA_PS.PS}
else
{VR_CLASSIFICA_PS.PS_AGG_NO_P}

Posted By: fabrizio
Date Posted: 10 Dec 2012 at 4:28am

First thanks for your helpSmile
 
I tried in this way but doesn't work, maybe I have a problem with subtotals !!!!

if {VR_CLASSIFICA_PS.ID_ATT_ORIG} like "*J10B*" and {#countCapre}>0 then
    formula = 0
elseIf {VR_CLASSIFICA_PS.ID_ATT_ORIG} like "*J09B*" And {#countePecore}>0 THEN
    formula = 0
elseif {VR_CLASSIFICA_PS.ID_ATT_ORIG} like "*J11*" and {#countScrofe}>0 then
    formula = 0
elseif {VR_CLASSIFICA_PS.ID_ATT_ORIG} like "*J02*" and ({#nr_Vitellini}<={#nrVacche}) then
    formula = 0
elseif {VR_CLASSIFICA_PS.ID_ATT_ORIG} like "*J02*" and ({#nr_Vitellini}>{#nrVacche}) then
    formula = ({#nr_Vitellini}-{#nrVacche})*{VR_CLASSIFICA_PS.PS} 
else
    formula ={VR_CLASSIFICA_PS.PS_AGG_NO_P}
end if

Posted By: fabrizio
Date Posted: 11 Dec 2012 at 3:13am
I tried it but it doesn't work I think because the program didn't read the subtotals in this way I have a null values.
I haven't any idea to resolve this problem

Posted By: comatt1
Date Posted: 11 Dec 2012 at 6:14am
check the null handling within the report, on File (toolbar), Options -> Reporting (tab). Make sure 'Convert Database null to default' and 'Other nulls to default' are checked.

Then within the formula you can set 'Defaults for Nulls' instead of 'Exceptions for Nulls'.

Try this.

Posted By: fabrizio
Date Posted: 12 Dec 2012 at 12:01am
I tried it and now the formula works but I have just one problem it doesn 't read one subtotal {#nrVacche}
if {VR_CLASSIFICA_PS.ID_ATT_ORIG} like '*J02*' and ({#nr_Vitellini}>{#nrVacche}) then
 ({#nr_Vitellini}-{#nrVacche})*{VR_CLASSIFICA_PS.PS} 
 else
if ({VR_CLASSIFICA_PS.ID_ATT_ORIG} like '*J10B*' and {#countCapre}>0) or
({VR_CLASSIFICA_PS.ID_ATT_ORIG} like '*J09B*' And {#countePecore}>0) or
({VR_CLASSIFICA_PS.ID_ATT_ORIG} like '*J11*' and {#countScrofe}>0) or
({VR_CLASSIFICA_PS.ID_ATT_ORIG} like '*J02*' and ({#nr_Vitellini}<={#nrVacche}))
 then
0
else
{VR_CLASSIFICA_PS.PS_AGG_NO_P}
 
 
the formula can't read that (red text) I don't know why !!!!
 
 
Thanks you are very helpful

Posted By: comatt1
Date Posted: 12 Dec 2012 at 2:27am
I am not too confident I can help any further with that particular area of code.

Is this for a farm or something?


Posted By: fabrizio
Date Posted: 12 Dec 2012 at 3:14am
yes this is a farm
#nr_Vitellini=sum of VR_CLASSIFICA_PS.ID_ATT_ORIG} like '*J02*
#nrVacche=sum of VR_CLASSIFICA_PS.ID_ATT_ORIG} like '*J07*' and  '*J08*'
 

Posted By: comatt1
Date Posted: 12 Dec 2012 at 3:20am
That may have answered my question. If you are working with the evaluate of the running total, what is the EXACT logic in that formula?

sum of VR_CLASSIFICA_PS.ID_ATT_ORIG} like '*J07*' and '*J08*' -- would be incorrect

{VR_CLASSIFICA_PS.ID_ATT_ORIG} like '*J07*' or {VR_CLASSIFICA_PS.ID_ATT_ORIG} like '*J08*' ----
would be the correct logic assuming we are on same page.

My assumptions are that there can only be one 'J' code per record, so and would ALWAYS fail if the logic you posted was in the evaluate section.

Posted By: fabrizio
Date Posted: 12 Dec 2012 at 4:43am

Local numberVar x :=x ;

Local numberVar y :=y ;

 x :=sum({VR_CLASSIFICA_PS.Dimensione},{VR_CLASSIFICA_PS.ID_ATT_ORIG} like '*J02*');

 y :=sum({VR_CLASSIFICA_PS.Dimensione},{VR_CLASSIFICA_PS.ID_ATT_ORIG} like '*J07*' or {VR_CLASSIFICA_PS.ID_ATT_ORIG} like '*J08*');

if ({VR_CLASSIFICA_PS.ID_ATT_ORIG} like '*J10B*' and {#countCapre}>0) or
({VR_CLASSIFICA_PS.ID_ATT_ORIG} like '*J09B*' And {#countePecore}>0) or
({VR_CLASSIFICA_PS.ID_ATT_ORIG} like '*J11*' and {#countScrofe}>0) or
({VR_CLASSIFICA_PS.ID_ATT_ORIG} like '*J02*' and (x<=y))
then
0
else
if {VR_CLASSIFICA_PS.ID_ATT_ORIG} like '*J02*' and x>y then
 (x-y)*{VR_CLASSIFICA_PS.PS}
else
{VR_CLASSIFICA_PS.PS_AGG_NO_P}

 focus on the red text: I'm sure is not correct the assignments but I would like to calculate the sum of dimension for ID.activities=J02 etc etc


Posted By: comatt1
Date Posted: 12 Dec 2012 at 4:54am
Honestly, it would be easiest to use Running Totals, and then work with them within the report. Your variables were declared correctly as far as I could tell though. If it's not working, I am not sure what is happening.

Personally, as a visual guy, I like running totals. This is how you can set them up.
----------------

In the running total setup like

Field to Summarize - PS_DIMENSIONE
Type of Summary - sum


Evaluate

use a formula
{VR_CLASSIFICA_PS.ID_ATT_ORIG} like '*J02*'


Reset
Never

-------- set this up as 'x' -----
---------------------------------
Field to Summarize - PS_DIMENSIONE
Type of Summary - sum


Evaluate

use a formula
{VR_CLASSIFICA_PS.ID_ATT_ORIG} like '*J07*' or {VR_CLASSIFICA_PS.ID_ATT_ORIG} like '*J08*'


Reset
Never


setup as 'y'

----


Posted By: fabrizio
Date Posted: 12 Dec 2012 at 5:16am
I have done it but it doesn' work
 
it works just for J02
 
but not for J07 and J08

Posted By: comatt1
Date Posted: 12 Dec 2012 at 5:22am
Try creating one for with just J07 (name x1) and another for J08 (name x2).

then just add one to the other within the formula and see if that works -- I honestly have no idea what is causing the issue with the second running total.

Matt

x1

{VR_CLASSIFICA_PS.ID_ATT_ORIG} like '*J07*'

x2

{VR_CLASSIFICA_PS.ID_ATT_ORIG} like '*J08*'

Posted By: fabrizio
Date Posted: 13 Dec 2012 at 3:17am
Dead I tried in this way too .....but nothing
 
the second running total doesn't work
 
 
Local numberVar x :=x ;
Local numberVar y :=y ;
Local numberVar z :=z ;

 x :={#nr_Vitellini};
 y :={#nr_Vacche};
 z :=x-y;

if ({VR_CLASSIFICA_PS.ID_ATT_ORIG} like '*J10B*' and {#countCapre}>0) or
({VR_CLASSIFICA_PS.ID_ATT_ORIG} like '*J09B*' And {#countePecore}>0) or
({VR_CLASSIFICA_PS.ID_ATT_ORIG} like '*J11*' and {#countScrofe}>0) or
({VR_CLASSIFICA_PS.ID_ATT_ORIG} like '*J02*' and (x<=y))
then
0
else
if ({VR_CLASSIFICA_PS.ID_ATT_ORIG} like '*J02*') and x>y then
 z*{VR_CLASSIFICA_PS.PS}
else
{VR_CLASSIFICA_PS.PS_AGG_NO_P}
 

Posted By: comatt1
Date Posted: 13 Dec 2012 at 3:22am
Originally posted by fabrizio



[IMG]smileys/smiley11.gif" height="17" width="17" align="absmiddle" alt="Dead" /> I tried in this way too .....but nothing
 
the second running total doesn't work
 
 Local numberVar x :=x ;
Local numberVar y :=y ;
Local numberVar z :=z ;
 x :={#nr_Vitellini};
 y :={#nr_Vacche};
 z :=x-y;
if ({VR_CLASSIFICA_PS.ID_ATT_ORIG} like '*J10B*' and {#countCapre}>0) or ({VR_CLASSIFICA_PS.ID_ATT_ORIG} like '*J09B*' And {#countePecore}>0) or ({VR_CLASSIFICA_PS.ID_ATT_ORIG} like '*J11*' and {#countScrofe}>0) or ({VR_CLASSIFICA_PS.ID_ATT_ORIG} like '*J02*' and (x<=y))then 0 else if ({VR_CLASSIFICA_PS.ID_ATT_ORIG} like '*J02*') and x>y then z*{VR_CLASSIFICA_PS.PS}else {VR_CLASSIFICA_PS.PS_AGG_NO_P}
 


Small reminder for z, you have to keep that logic you had for if x<=y then 0.

What happens with the failing running total? Do you just get 0?

Posted By: fabrizio
Date Posted: 13 Dec 2012 at 4:04am
When there is this condition
if ({VR_CLASSIFICA_PS.ID_ATT_ORIG} like '*J02*') and x>y
 
I get the result of this operation : first subtotal(vitellini)* PS
 
so it doesn't run the second subtotal to make the difference ((vitellini)-(vacche))*PS
 

Posted By: comatt1
Date Posted: 13 Dec 2012 at 4:15am
try laying out all of your running totals on the report body, and evaluate what is pulling correctly and what isn't

Posted By: fabrizio
Date Posted: 13 Dec 2012 at 4:16am
is there anyway to debug the formula

Posted By: comatt1
Date Posted: 13 Dec 2012 at 4:21am
Other than the debugging that happens on save, I am not aware, however, I don't see any issue with your formula.

I would look at the data getting passed to the formula first. So put all the fields you can on the report body and see what looks as you expect and what doesn't.


Posted By: fabrizio
Date Posted: 13 Dec 2012 at 4:25am
ok thanks

Posted By: fabrizio
Date Posted: 13 Dec 2012 at 11:22pm
I putted all the fields on the body report and everything is as I expcted
 
Local numberVar x :=x ;
Local numberVar y :=y ;
Local numberVar z :=z ;

 x :={#nr_Vitellini};
 y :={#nr_Vacche};
 z :={@diff_vit_vac};

if ({VR_CLASSIFICA_PS.ID_ATT_ORIG} like '*J10B*' and {#countCapre}>0) or
({VR_CLASSIFICA_PS.ID_ATT_ORIG} like '*J09B*' And {#countePecore}>0) or
({VR_CLASSIFICA_PS.ID_ATT_ORIG} like '*J11*' and {#countScrofe}>0) or
({VR_CLASSIFICA_PS.ID_ATT_ORIG} like '*J02*' and (x<=y))
then
0
else
if {VR_CLASSIFICA_PS.ID_ATT_ORIG} like '*J02*' and x>y then
 z*{VR_CLASSIFICA_PS.PS}
else
{VR_CLASSIFICA_PS.PS_AGG_NO_P}
 
I added z as difference between vitellini and vacche . It works on the body report but not in the formula

Posted By: fabrizio
Date Posted: 13 Dec 2012 at 11:36pm
Maybe I understood where is the problem I can't read the code wich start with the same letter J02 J07 because if I punt vitellini(J02)=34 on the body record I have the same value for J04 J06 J07 but the real value for J04 is =0 and J06=0 and J07=21

Posted By: comatt1
Date Posted: 14 Dec 2012 at 4:21am
Sounds like you may not be grouping correctly, how is the report grouped, where are you putting the values in the body of the report.

sounds really like an evaluate issue with the running totals.

Copy and paste the evaluate code from the running totals in question.



Posted By: fabrizio
Date Posted: 14 Dec 2012 at 4:42am
 
I putted  the values in the details part in the body of report
 
 
sum of VR_CLASSIFICA_PS.DIMENSIONE (#nr_vacche)
{VR_CLASSIFICA_PS.ID_ATT_ORIG} like '*J07*' or {VR_CLASSIFICA_PS.ID_ATT_ORIG} like '*J08*'
 
sum of VR_CLASSIFICA_PS.DIMENSIONE (#nr_vitellini)
 
{VR_CLASSIFICA_PS.ID_ATT_ORIG} like "J02"

Posted By: comatt1
Date Posted: 14 Dec 2012 at 5:46am
{VR_CLASSIFICA_PS.ID_ATT_ORIG} like "J02" should be

{VR_CLASSIFICA_PS.ID_ATT_ORIG} like '*J02*'

Posted By: fabrizio
Date Posted: 19 Dec 2012 at 12:06am

It works Smile .... I'm very happy and my boss too.......... I have to say thanks to you for your incredible help, the problem was the order of the values on the report


Print Page | Close Window