Print Page | Close Window

variables don't seem to be working

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=19826
Printed Date: 14 May 2024 at 1:45am


Topic: variables don't seem to be working
Posted By: bwsanders
Subject: variables don't seem to be working
Date Posted: 30 Jul 2013 at 8:53am
so, in my report i'm initializing variables in the header, setting the variables in the output (supressed) and then calling the variable in the footer as to get one single line with all of my data for each employee.

I've got several formulas that look at one field that has many results and it's shows a true or false depending upon the data in the field. when i run the report it's returning all false however if i look at the raw data in sql i can see that several should be true.

i'm not quite sure what's happening.

any thoughts?


Replies:
Posted By: DBlank
Date Posted: 30 Jul 2013 at 10:01am
unsuppress them in the detail row and see what is happening there.

Posted By: bwsanders
Date Posted: 30 Jul 2013 at 10:04am
i tried that and then it just shows me several records for the first field only (emp id) and then shows me the footer. it's weird i'm not sure what is bunking out on this report. 

Posted By: DBlank
Date Posted: 30 Jul 2013 at 10:17am

but did you get the correct T/F value per record (assuming you are updating the value per row)?

 Can you show your formula?

Posted By: bwsanders
Date Posted: 30 Jul 2013 at 10:21am
here is the formula

If {ETax.tcode} In ["MED", "SS"]
    Then "Y"
Else
    If NOT ({ETax.tcode} In ["MED", "SS"])
        Then "N"

super simple

in the footer it shows N,N,N,N,N

when in fact it should be N,Y,N,N,Y

if i look at each individual line it would look like

N N N Y N
Y N N N N
N Y N N N
N Y N N N

i thought maybe the variable was resetting too soon so i tried it without it and still got the same results.

Posted By: DBlank
Date Posted: 30 Jul 2013 at 10:31am
where are you concantenating it?

Posted By: bwsanders
Date Posted: 30 Jul 2013 at 10:35am
in the footer i'm calling the variables like

var1 + "," + var2 "," and so on

Posted By: DBlank
Date Posted: 30 Jul 2013 at 10:45am
you can just use one formula to run through all the rows.
You need one formual in GH to set the variable to blank
one to run through the data (details
one to display (GF)
 
 
//group header
shared stringvar x;
x : = ""
 
//detail
shared stringvar x;
x : = x + (If {ETax.tcode} In ["MED", "SS"] Then "Y, " else "N, ");
 
//footer
shared stringvar x;
x := left(x,len(x)-2)

Posted By: bwsanders
Date Posted: 30 Jul 2013 at 11:27am
Thanks DBlank! for some reason still no luck. I'll do some more digging tomorrow. 

Posted By: DBlank
Date Posted: 30 Jul 2013 at 11:39am

to trouble shoot

unsuppress the detail section
place the detail formula next to the tcode field and watch how they interact to see if you can find a pattern

Posted By: bwsanders
Date Posted: 31 Jul 2013 at 4:14am
so when i move the group footer into the details field to see all of the data it looks like all of my formulas work and all of the data is there it's just not all on one line. when i put it back into the footer field it's all on one line but the data isn't correct.

this one has me scratching my head. i feel like it's gotta be something stupid somewhere that i may be over looking.

Posted By: DBlank
Date Posted: 31 Jul 2013 at 4:44am
you should not need to move your formula to the detail section.
Did you use the 3 shared stringvar formulas I gave you or are you still doing some other calulations var1 + var2...?

Posted By: bwsanders
Date Posted: 31 Jul 2013 at 4:51am
switched from var1 style to the straight formula in the detail section 

Posted By: bwsanders
Date Posted: 31 Jul 2013 at 4:55am
however, it does also throw this error "string length less than 0 or not an integer" based on this

//footer
shared stringvar x;
x := left(x,len(x)-2)

Posted By: DBlank
Date Posted: 31 Jul 2013 at 4:55am

So you

1 -placed 1 formula on the group header to set the value to ""
2- placed a formula on the detail section to evaluate and add to the string for each row in your dataset
3 - placed a display formula in the group footer
 
correct?

Posted By: DBlank
Date Posted: 31 Jul 2013 at 4:56am
ahh -
what do you want to do for groups with no values?

Posted By: bwsanders
Date Posted: 31 Jul 2013 at 4:58am
//group header
Shared StringVar Fica := "";

//detail
Fica := Fica + (If {ETax.tcode} In ["MED", "SS"] Then "Y" else "N");

// group footer
Shared StringVar Fica := Left(Fica,len(Fica)-2);

Posted By: bwsanders
Date Posted: 31 Jul 2013 at 4:59am
that's just it though, every single employee has Fica tax so everyone should either be a Y or N. it's weird. 

Posted By: DBlank
Date Posted: 31 Jul 2013 at 5:02am

so for groups with no values youwill get the error

change your group fotoer to handle this as
Shared StringVar Fica := if Fica=0 then "Whatever you want here" else Left(Fica,len(Fica)-2);

Posted By: DBlank
Date Posted: 31 Jul 2013 at 5:04am

also if you have nulls you need evalaute to "N" make sure the detail section  formula is set to 'use default values for nulls'

Posted By: DBlank
Date Posted: 31 Jul 2013 at 5:10am
Ahhh-I just noticed you altered the detail one to remove the space and comma
if you do not want a space between the Y or N for multiple rows then fix two things
details needs the comma after the Y or N
Fica := Fica + (If {ETax.tcode} In ["MED", "SS"] Then "Y," else "N,");
footer needs to only subtract 1 (removes the trailing ",")
Shared StringVar Fica := if Fica=0 then "Whatever you want here" else Left(Fica,len(Fica)-1);
 

Posted By: DBlank
Date Posted: 31 Jul 2013 at 5:13am
You were seeing no results or getting the error for groups with less than 3 detial rows.
Without the "," on it the length of Fica was only the number of rows (1 character per row).
The display was always subtracting 2 from the length.

Posted By: DBlank
Date Posted: 31 Jul 2013 at 5:16am
Even though you should alwasy have at least 1 fica record I would still leave the if-then potion in the display footer formula to acount for the possibility of an error. You can display "Missing" or "Error" instead of having the report 'crash' in case the DB gets screwy.

Posted By: bwsanders
Date Posted: 31 Jul 2013 at 5:46am
yeah that is a good point. i have it set up and fine tuning it to show correctly but so far this is what i'm getting.

every employee has 7 tax codes for various taxes according to payroll. so on the footer where all the records are shown in 1 line for this it will show like

NNNYNYN

becuase it's evaluating every tcode and answering accordingly but i only need a single Y if the employee as SS or MED tax code or a single N if they do not.

If i do something like this Shared StringVar Fica := Left(Fica,len(Fica)-6); in the footer then it will only show the last evaluated tcode instead of a correct Y or N.

Thanks for your help thus far. I'm just missing something.

Posted By: DBlank
Date Posted: 31 Jul 2013 at 5:52am

That is very different than what I understood. For clarification

do you need only a Y returned if ANY row has the MED or SS code (and no N even if some rows are not the SS or MED)
or
do you need both one Y and 1 N returned if the have a tleast one row meeting each condition?

Posted By: bwsanders
Date Posted: 31 Jul 2013 at 6:03am
sorry if i confused you there. what i am looking for a Y if any row has SS or MED

OR

an N if NO row has SS or MED.

for testing i tried this in my detail

Fica := Fica + (If instr(Fica,{@Fica})>0 Then "" Else {@Fica});

and that returns 1Y and 1N so then i tried what you suggested with the len-1 in my footer but that only shows N even though it should have been a Y

Posted By: DBlank
Date Posted: 31 Jul 2013 at 6:26am
If I understand your requirement correctly your going to be irritated...
create one formula as
 
If {ETax.tcode} In ["MED", "SS"] Then "Y" else "N"
 
Insert a summary as the maximum on this field at your group footer
maximum(formula,groupfield)

Posted By: DBlank
Date Posted: 31 Jul 2013 at 6:29am
you could also change your other formulas if you want...
 
 
//group header
Shared StringVar Fica := "N";

//detail
Fica := If {ETax.tcode} In ["MED", "SS"] Then "Y" else Fica;

// group footer
Shared StringVar Fica := Fica

Posted By: bwsanders
Date Posted: 31 Jul 2013 at 6:35am
damnit!! i knew it had to be something silly that i wasn't thinking of. 


Print Page | Close Window