• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 292
  • Last Modified:

Variable assistance with Crystal Reports please

Hello,

 I need further assistance please.  I have another issue with Crystal Reports and variables.  I am using variables with groups.  So far all is working well, except when one condition is met (value for 002 only) a left over from previous value of 001 is diaplayed (see group #27; value should be Staff Communications and not Proofreading).  When I was attempting to initialize the formulas, I would receive a blank value for formula New Test.  It's probably something extremely simple.  Thank you for everything.
 
Formula:  @TitleFor001 (is to look for and retain the value ending with 01)

global stringvar Title1;

if uppercase({?Pm-?Course Code Grouping})='YES'
and
GroupName ({SCV_COURSE_ACT.COURSE_ID}) [11 to 12] = '01'
Then
Title1:={SCV_COURSE_ACT.TITLE};
Title1

The Formula LastTitle (is to look for and retain the value ending with 02)

global stringvar LastTitle;

if uppercase({?Pm-?Course Code Grouping})='YES'
and
GroupName ({SCV_COURSE_ACT.COURSE_ID}) [11 to 12] = '02'
Then
 LastTitle:={SCV_COURSE_ACT.TITLE};
LastTitle

Formula TitleFor003 (is to look for and retain the value ending with 03)

global stringvar Title003:= '';

if uppercase({?Pm-?Course Code Grouping})='YES'
and
GroupName ({SCV_COURSE_ACT.COURSE_ID}) [11 to 12] = '03'
Then
 Title003:={SCV_COURSE_ACT.TITLE};
Title003

The Formula High Level Name (for the purpose of seeing what the values are next to 001, 002, and 003 respectively)
global stringvar Title1;
global stringvar LastTitle;
global stringvar Title003;

if uppercase({?Pm-?Course Code Grouping})='YES'
and
GroupName ({SCV_COURSE_ACT.COURSE_ID}) [11 to 12] = '01'
then
{@TitleFor001}
else
if uppercase({?Pm-?Course Code Grouping})='YES'
and
GroupName ({SCV_COURSE_ACT.COURSE_ID}) [11 to 12] = '02'
then
{@LastTitle}
else
{@TitleFor003}

Formula New Test (To show the final results.  If the ending 01 is there print that course name) if no 01 but 02 is the lowest, put in the name associated with 02.  Likewise show the name ending in 03 if 01 is blank and 02 is also blank or not there)

global stringvar Title1;
global stringvar LastTitle;
global stringvar Title003;

if (uppercase({?Pm-?Course Code Grouping})='YES'
and
{@TitleFor001}<> '')
then
{@TitleFor001}
else
if (uppercase({?Pm-?Course Code Grouping})='YES'
and
{@TitleFor001}= ''
and
{@LastTitle} <> '')
then
{@LastTitle}
else
if (uppercase({?Pm-?Course Code Grouping})='YES'
and
{@TitleFor003}<> '')
then
{@TitleFor003}
course-report-10-25-11-b.pdf
ScreenShot-of-Sub-Report.doc
0
mtrout
Asked:
mtrout
  • 15
  • 7
  • 5
5 Solutions
 
mlmccCommented:
Where in the report are these formulas?

It may make more sense to use variables to maintain a value rather than rerunning the formula?

mlmcc
0
 
mtroutAuthor Commented:
Thank you.

 I hope that the screen shot was sent.  The formulas are used in GF2 that will be suppressed but the information will be displayed in GF1.  Also, there is a new 'wrinkle' where an average is sorted in decending order based on weighted averages.  Now, that I am trying to display the full name for any class ending in 01 or 02 if there is no 01, the averages seem to  be out or order.

When you say use variable to maintain a value and not rerunning the formula, please explalin.  I thought that placing them in the header (GH2) would make it better.  It doesn't.  When I have the formulas just in GF2 and GF1, it's till the same.

0
 
mlmccCommented:
You don't need the variables in the formulas

Try changing them to

Formula:  @TitleFor001 (is to look for and retain the value ending with 01)

if uppercase({?Pm-?Course Code Grouping})='YES'
and
GroupName ({SCV_COURSE_ACT.COURSE_ID}) [11 to 12] = '01'
Then
{SCV_COURSE_ACT.TITLE}


The Formula LastTitle (is to look for and retain the value ending with 02)

if uppercase({?Pm-?Course Code Grouping})='YES'
and
GroupName ({SCV_COURSE_ACT.COURSE_ID}) [11 to 12] = '02'
Then
 {SCV_COURSE_ACT.TITLE};


Formula TitleFor003 (is to look for and retain the value ending with 03)


if uppercase({?Pm-?Course Code Grouping})='YES'
and
GroupName ({SCV_COURSE_ACT.COURSE_ID}) [11 to 12] = '03'
Then
 {SCV_COURSE_ACT.TITLE};

mlmcc

0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
mtroutAuthor Commented:
When this is done, the incorrect value shows up.  For example if 01 and 02 are present (a few on page 1), then 02 will always show as the name.  this is not wanted.  If 01 and 03 are present (some examples on page 1 and page 2), then 03 will show. But, if just 01 is present(some on page 1and page 2), 01 shows and if just 02 is present(one on page 2), the name associated with 02 will show.  I have included a sample of the results.
course-report-10-25-11-b-no-vari.pdf
0
 
mlmccCommented:
Then change
Formula New Test (To show the final results.  If the ending 01 is there print that course name) if no 01 but 02 is the lowest, put in the name associated with 02.  Likewise show the name ending in 03 if 01 is blank and 02 is also blank or not there)

global stringvar Title1;
global stringvar LastTitle;
global stringvar Title003;

if (Title1 <> '')
then
    Title1
else
if (LastTitle <> '')
then
  LastTitle
else
   TitleFor003
   
0
 
mtroutAuthor Commented:
Oh no, making the change made New test completely blank with no values at all.  thisis what I have now for New Test:  I'm lost, where are the variables?  Where do I need to put them?  I removed them from the formula as you stated before.  results are attached.
// 10-25-11
global stringvar Title1;
global stringvar LastTitle;
global stringvar Title003;

if (Title1 <> '')
then
Title1
else
if (LastTitle <> '')
then
LastTitle
else
Title003
course-report-10-25-11-b-no-valu.pdf
0
 
mlmccCommented:
Use the version with the original formulas

mlmcc
0
 
mtroutAuthor Commented:
I am back to where I was.  What do we  try next? It looks good except for having 002 display when ther is just 002 present.  001 show up when just 001.  If 001 and 002; 001 shows (this is good). If 001 and 003;  001 shows, this is good.  What have I missed?
course-report-10-25-11-b.pdf
0
 
mlmccCommented:
WHich column is which?

mlmcc
0
 
mtroutAuthor Commented:
I hope that this helps:
Looking at the first page, top, COMM711D001 is GF2 Group #2 Name Stress Management  is the result from GF2 @High Level Name  Nothering is in @Last Title, Stress Management which is listed towards the right is the result from @TitleFor001, and nothing is in @TitleFor003

Now where 1 COMM7117D is this is the result of: GF1 is the Group Number, Comm7117D is the result of the formula used in Group #1 Name, Stress Management is the result from the formula @New Test

Looking further on the same page (page 1):
GF2 COMM7005D001 ( Group #2 Name) Communication Skills (@High Level Name) @LastTitle is empty @TitleFor001 has Communication Skills, and nothing is in @TitleFor003

again in GF2:
COMM7005D002 (Group #2 Name) Communications Skills (@High Level Name) Communications Skills (@LastTitle) , Communication Skills (@TitleFor001) and nothing is in @TitleFor003

in GF1:
1 (group number), COMM7005D (Group #1 Name) Communication Skills (Result from @New Test)

same page (page 1) looking at WRIT7007D001 and WRIT7007D003
GF2 WRIT7007D001 (Group #2 Name) Effective Government Correspondence (@High Level Name),  
@LastTitle does not have a value, Effective Government Correspondence (Value for @TitleFor001), nothing in @TitleFor003

WRIT7007D003 (Group #2 Name) Effective Government Correspondence Contract (@High Level Name), @LastTitle does not have a value, Effective Government Correspondence (@TitleFor001), and Effective Government Correspondence Contract (the value in @TitleFor003)

The shorten name below  next to 13 WRIT7007D has the lowest version name next to it which is Effective Government Correspondence (result from @New test).

Page 2 Near the bottom
GF2:
WRIT7016D002 (Group #2 Name) Staff Communications (Result from @High Level Name), Staff Communications (Result from @LastTitle.  This holds 02 Value), Proofreading (Result from @TitleFor001 and this value is not supposed to be here, it's from something above), there is no value for @TitleFor003

GF1:
27 (group Number), WRIT7016D( Group#1 Name) Proofreading (the result from @New Test) @New Test should have the value of Staff Communications

I am unable to get the logic correct.  Another footnote @LastTitle is to keep the value of full name of the course ending in 02, it was called LastTitle becasue I was looking for 01 and 02 and now need to consider 03 also.

Thank you again. I have enclosed the pirntout to refer to, not the one sent previously.


course-report-results-from-10-24.pdf
0
 
mtroutAuthor Commented:
Mlmcc,

How the report should look.  All is well except for having the correct name  (lowest version listed next to the shortened name).
 In this output, the group numbers are all in order with nothing missins.  Each segment is a separate subreport.  I had extended and sent the first sub report only, to show everything without a limitation on the average.  Although I have done that, there are still group numbers missing.  This report shows before changing subreports 2-5.  I owuld change one subreport first, then implement the exact change to the other sub reports.

course-report-results-full-10-24.pdf
0
 
mtroutAuthor Commented:
Mlmcc,

I tried something else.  I made a few changes to the formulas that I could use your review please.  I dod not want to remove the  variables, but will suppress them.  I used the minimum in the New Test formula.  I still need your assistance please with the group numbering.  Thank you.
course-report-10-27-11-b-full.pdf
course-report-10-27-11-b-sub.pdf
Formulas-and-Variables-10-27-11-.doc
0
 
James0628Commented:
Looking back at your original post, I see a couple of potential problems.

 You start @TitleFor003 with this:

global stringvar Title003:= '';

 That's going to reset the variable to "" each time the formula is evaluated, which is probably not what you want.  Let's say that you have COURSE_ID values of ABCDEFGHIJ03 and ABCDEFGHIJab.  @TitleFor003 would see the ABCDEFGHIJ03 and put that in Title003.  But then when you got to the next value (ABCDEFGHIJab), @TitleFor003 would start by setting Title003 to "", erasing the ABCDEFGHIJ03 value that it had saved, and you'd be left with nothing in Title003.

 I think you should leave the ":=''" off of that line.


 The other basic issue is that you presumably need to reset these variables at some point, and, AFAIK, you're not doing that.  This could explain why you were seeing "a left over from previous value of 001".  If you're saving values to be shown in GF1, you probably want to reset those variables in GH1, so that they start fresh for each group 1.  If you don't do that, you can get the value from the previous group 1.  So, if you haven't already, I would create a formula like the following and put it in GH1.

global stringvar Title1;
global stringvar LastTitle;
global stringvar Title003;

Title1 := '';
LastTitle := '';
Title003 := ''


 James
0
 
mtroutAuthor Commented:
Hi James,

          I have made the changes.  I already moved the reset from all of the formulas:

This is called Reset Variables; it's placed in GH1

global stringvar Title1;
global stringvar LastTitle;
global stringvar Title003;

Title1:='';
LastTitle:='';
Title003:='';

Formula TitleFor001:  Placed in GF2

global stringvar Title1;

if uppercase({?Pm-?Course Code Grouping})='YES'
and
GroupName ({SCV_COURSE_ACT.COURSE_ID}) [11 to 12] = '01'
Then
Title1:={SCV_COURSE_ACT.TITLE};
Title1

Formula for LastTitle  in GF2

global stringvar LastTitle;

if uppercase({?Pm-?Course Code Grouping})='YES'
and
GroupName ({SCV_COURSE_ACT.COURSE_ID}) [11 to 12] = '02'
Then
 LastTitle:={SCV_COURSE_ACT.TITLE};
LastTitle

Formula for TitleFor003 in GF2

global stringvar Title003;

if uppercase({?Pm-?Course Code Grouping})='YES'
and
GroupName ({SCV_COURSE_ACT.COURSE_ID}) [11 to 12] = '03'
Then
 Title003:={SCV_COURSE_ACT.TITLE};
Title003



This is formula New Test  Placed in GF1

global stringvar Title1;
global stringvar LastTitle;
global stringvar TitleFor003;

if (uppercase({?Pm-?Course Code Grouping})='YES'
and
{@TitleFor001}<> '')
then
{@TitleFor001}
else
if (uppercase({?Pm-?Course Code Grouping})='YES'
and
{@TitleFor001}= ''
and
{@LastTitle} <> '')
then
{@LastTitle}
else
{@TitleFor003}


course-report-10-28-11.pdf
0
 
James0628Commented:
It might help a lot if you could just post the report.

 Looking at that last PDF file, if the numbered lines (like 27) are in GF1, and you have the reset formula in GH1, I don't see how you could be getting those results.  I don't know if it will help, but I would add a copy of the reset formula to the subreport report header (so that you have that formula in the subreport report header _and_ GH1).  There seem to sometimes be problems with global variables if they're not declared at the beginning of the report (or, in this case, subreport).


 I would also change your Newtest formula.  You declare the variables, but then you don't use them.  You use the formulas that set those variables instead.  At the very least, there's no point in declaring the variables in the formula if you're not going to use them.  I would keep the variable declarations, and replace the formulas with the variables.  I think your version will work.  It's just less efficient and, IMO, confusing.  I'd change NewTest to:

global stringvar Title1;
global stringvar LastTitle;
global stringvar Title003;

if (uppercase({?Pm-?Course Code Grouping})='YES'
and
Title1<> '')
then
Title1
else
if (uppercase({?Pm-?Course Code Grouping})='YES'
and
Title1= ''
and
LastTitle <> '')
then
LastTitle
else
Title003


 Technically, you also had the 03 variable name wrong in NewTest in your last post.  You had TitleFor003, but the variable in the @TitleFor003 formula is Title003.  Since you weren't actually those variables in NewTest, it didn't matter that the name was wrong.

 I would also make the same changes to your HighLevelName formula.  It's the same situation - You declare the variables, but don't use them.  Instead, you use the formulas that set the variables.  I'd just use the variables instead.

 James
0
 
James0628Commented:
Oh.  Another thought.  You could add WhilePrintingRecords to the beginning of each formula.  Put it on a separate line, with a ; at the end.  That will ensure that all of the formulas are evaluated during the same pass through the report (apparently that's another source of potential problems when using global variables).

 James
0
 
mtroutAuthor Commented:
James,

            Thank you.  I made the changes noted above.  At first I was not getting any text at all   for GF1 for new test and GF2 for High      I know I must have done something totally wrong.  Then I put in the WhilePrintingRecords with the ; as you directed and now I have the text.  I noticed that I do not get Staff Communications for WRIT7016D002.  The database has Staff Communications for WRIT7016D002.  There is only one version listed '002' and the name Technical Writing is not part of the WRIT7016D classes.   It looks at if what is in  GF1 is carried to GF2  immediately below and the courses are not related.

         I'm wondering if I should be looking at a different way to do this?  like what?  I'm not sure.  I saw something called minimum, but I would need to try it and see.  But I definitely do not want to give up on Variables if this is a good choice.

The attached pdf shows the the first subrespor extended without restrictions.
Course-Report-10-31-11-Variables.rpt
course-report-10-31-11-variables.pdf
0
 
James0628Commented:
You don't have WhilePrintingRecords in all of the formulas that use those variables.  You need to be consistent.  Add it to @ResetVariables, @TitleFor001, @LastTitle and @TitleFor003 (and any other formulas that use those variables).  That may or may not change anything, but if you've got it in some of those formulas, it should be in all of them.

 In @HighLevelName, after WhilePrintingRecords, you need to add these lines:

EvaluateAfter ({@TitleFor001});
EvaluateAfter ({@LastTitle});
EvaluateAfter ({@TitleFor003});


 @HighLevelName uses the variables that are set by those formulas, but it's in the same section as those formulas, and I don't think there's any guarantee which formulas will be evaluated first.  So, @HighLevelName may use a variable before the formula that sets it is evaluated, in which case it would get the old variable value from a previous group 2.  Those EvaluateAfter functions will force CR to evaluate those other formulas first, so the variables will be set.  Try that and see what you get.  There's no data in the report that you posted, so I can't test it.

 James
0
 
mtroutAuthor Commented:
James,

      Thank you.  I made the changes as described and the correct names are showing.  How would I go about making the group numbers consecutive?  Some are skipped. Thank you.
course-report-11-1-11-variables.pdf
Course-Report-11-1-11-Variables.rpt
course-report-11-1-11-variables-.pdf
Sub1-Screen-shot.doc
0
 
mtroutAuthor Commented:
James,

           Resending the report again.
Course-Report-11-1-11-Variables.rpt
0
 
mtroutAuthor Commented:
James,

        For the groups, I used the following:
whileprintingrecords;
global numbervar linenumber=0;
linenumber:=linenumber +1;

and placed it at GF1 and the numbers are in consecutive order for the groups.  I will test with the variables and noting if the numbering of the groups are numbered in consecutive order and not skipping.
Course-Report-11-1-11-Variables-.pdf
0
 
James0628Commented:
I've never used Group Number, but from a test I just ran, it appears to include all group levels.  If there is one group 2 for a group 1, then Group Number doesn't get incremented an extra time, but if there are 2 or more group 2's for a group 1, Group Number is incremented for the other group 2's.  Something like this:

G1 - 1
  G2 - 1
G1 - 2
  G2 - 2
G1 - 3
  G2 - 3
  G2 - 4
G1 - 5
  G2 - 5

 Group Number was 5 on the last G1, instead of 4, because there were two G2's in the G1 before that.

 So, the upshot is that a manual count using a variable, as in your last post, is probably the way to go.

 However, there is an error in the formula that you posted.  You have:

global numbervar linenumber=0;

 That declares linenumber and compares it to 0.  You have =, instead of :=.  However, you don't want := there either.  That would reset linenumber to 0 every time, so when you added 1, you'd just keep getting 1 as the result every time.  Just take out the =0.

global numbervar linenumber;


 FWIW, I would add a formula to the subreport report header to initialize linenumber.  You do _not_ want to add linenumber to your ResetVariables formula, because you also have that formula in GH1, so it would reset linenumber for each new group 1.  Create a new formula like the following and put it in the subreport report header.

whileprintingrecords;
global numbervar linenumber;
""

 The "" at the end is just so the formula doesn't produce any visible output on the report.  You could also just suppress the formula  field.

 James
0
 
mtroutAuthor Commented:
James,

           Thank you for everything. It looks the way is should.  Thank you so very much.  I was shy about putting the reset in because I want the group numbers to continue from one sub report to the other.  I removed =0, but crystal let it work any way.  Odd, I rather do it correctly.  Thank you again.  Please see the attached.

course-report-11-2-11-variables-.pdf
0
 
mtroutAuthor Commented:
thank you for your patience!!
0
 
James0628Commented:
You're welcome.

 If you want the group numbers to continue between subreports (eg. the first subreport has 1 - 10, and the second subreport starts with 11), then, as you said, you don't want to reset the variable at the beginning of the subreport, but, FWIW, the last formula that I posted just declared the variable.  It didn't actually set it to anything.

 However, more importantly, if you want the count to be continued from one subreport to the next, you'll have to change the linenumber variable from Global to Shared.  Global variables only exist within a single report, or subreport, and for a subreport, they're recreated each time the subreport is run.  So, with a Global variable, the count will restart at 0 for each new subreport.  Shared variables are shared between the main report and all of the subreports in that report.


 As for the =0 part, it may not be what you wanted, or even make much sense, but as long as the syntax is OK, CR won't give you an error.

global numbervar linenumber=0;

 That line would just return true or false.  If that was the last line in the formula, then the formula would have produced True or False on the report.  But the last line was where you incremented linenumber, so linenumber was the result that you saw on the report.

 Or, to put it another way, every line in a CR formula produces some kind of result.  But the only visible result is the one from the last evaluated line in the formula.  So, with the =0, that line produces a True/False result.  Without the =0, it produces the current value of linenumber.  Either way, since it's not the last evaluated line in the formula, you don't actually see that result on the report, and it doesn't affect the result of the formula.

 Hope that makes sense.

 James
0
 
mtroutAuthor Commented:
James,

           Thank you! Thank you! Thank you so much!!!  I really appreciate everything.  The comment was very helpful.  Until the next question...which could come soon...  Thank you again.
0
 
James0628Commented:
You're welcome again.  :-)

 James
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 15
  • 7
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now