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

Need additional assistance with Variables. How to display value found in one group to display in another group

Hello,
I have set up a variable, to what I believe, is capturing the first value in the sorted group. The value is the title of the course.
I have set up another variable to capture the change.   The group GF2 can have a changed value.  Sometimes, there are 7 or more different names.
Since Group Footer 1 (GF1) is already set up the way I want, calculations are correct and sorted correctly; all except for having the name for course, ending in 001 or the lowest of the names grouped to be displayed.  This was the request.

The formula: @TitleFor001 is holding the name for the course ending in 001.  If a course ending with 001 does not show, I'm in trouble.

The formula  @LastTitle shows all other values

The formulas work in GF2.  I have tried to modify the formula @NewTest so that the value of  @TitleFor001 would display.  No luck.

The goal to to place the title associated with the lowest level course next to it's shortened value in GF1.

Page 3 of the attached shows an example where no value is next to record 35 and page 6 has an example where there are several versions represented and the first value is not written next to record 79.  There are several misses throughout the example.
I will need to replicate the same in several sub reports.  This is a subreport and all the values which needs to be passed are passed.  

Thank you.

I have tried following a previous example using variables, but could not follow it. course-report-10-7-11-.pdf Formulas.doc Course-Report-10-7-11-CH.rpt
0
mtrout
Asked:
mtrout
  • 8
  • 6
  • 2
1 Solution
 
mlmccCommented:
I don't fully understand the problem or what the report should show.

mlmcc
0
 
James0628Commented:
FWIW, your report appeared to have saved data, but I could not see it.  When I opened the report in CR 10, I was prompted for a db connection.  When I canceled that, I got a blank preview window (for both the main report and the subreport).

 So, I wasn't able to test this, so no guarantees, but you could try this.

 Change @LastTitle as follows (so that it saves the last non-001 title in a variable):

global stringvar LastTitle;

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


 Change @NewTest to:

global stringvar Title1;
global stringvar LastTitle;

if Title1 <> ""
Then
Title1
else
LastTitle


 I think there are at least a couple of problems with your NewTest formula, but I think the basic problem is that you're trying to check COURSE_ID in the group 1 footer, and at that point the formula will only see the COURSE_ID from the last record in group 1.  So, the idea in my formulas is to save the last 001 title in a variable (which you were already doing, but you weren't using that value in NewTest), and save the last non-001 title in another variable, and then use the 001 title if you have one (if the Title1 variable is not blank), otherwise use the non-001 title (the LastTitle variable).

 Also, I would put a formula like the following in the report header in the subreport, to declare those variables.  There seem to sometimes be problems using global variables if you don't declare them first in the report header.  It's fine if the section is suppressed.  The formula will still be evaluated.

global stringvar Title1;
global stringvar LastTitle;
Title1 := "";
LastTitle := ""


 I would also put a copy of that formula in the group 1 header, to reset the variables for each new group 1.  Otherwise, if, for example, a group 1 has no 001 titles at all, Title1 will be left with the 001 title from the previous group 1.  Maybe that shouldn't happen (every group 1 should have at least one 001 title and non-001 title), but no harm in making sure the variables are reset, just in case.  Unless, of course, you want to use the value from the previous group 1.

 James
0
 
mtroutAuthor Commented:
Thank you James,
I will test what you have suggested.  Pardon the delay in response to you.  I have not had the opportunity to get back to the report in a couple of days.
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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:
Hello James,

I have put in the changes as suggested, but still coming up with an empty space.  I think that I'm missing something.  Could you double check please?  thanks.


course-report-10-12-11.pdf
Course-Report-10-12-11.rpt
0
 
James0628Commented:
I noticed a couple of things in the first subreport (Is there something in the other subreports that I need to check?) .

 The main thing is probably that you left the last two lines out of NewTest.  You didn't include

else
LastTitle


 Without those lines, you'll get Title1 if it's not blank, and if it is blank, you get nothing (blank).


 You also have NewTest in RHb and GH1.  I'm guessing that that has something to do with the last part of my last post, but you were supposed to create a new formula like that one that I posted, to declare those two variables and (re)set each of them to an empty string, and put that new formula in the report header and group header.  I don't think you want NewTest there.  That's just meant to output the results at the end (after the variables have been set).

 James
0
 
mtroutAuthor Commented:
James,

           Thank you.  You're so patience.  I will be replicating what is done in this sub report to the others.  But now I'm confused.  I went back to trying to understand the whole concept behind variables.  I'm going to try to take the first value (mostly will have 001) and try to hold that one.  I had adjusted a formula to show the high level name when it's equal to 001.  I will follow the assignment you made with the variable.  So far, if 001 is represented (only; no other versions), then I was able to have that show up where I want it (in @New Test).  WHEW!

If version 001 and others are represented, I still need to show version 001 in @New Test.  I will be re-reading your posts. I feel that I am close.  And I bet that I'm just following what you put in post earlier.  Somehow, for what ever reason, it's not sinking in yet.

For New test Formula:
global stringvar LastTitle;

if uppercase({?Pm-?Course Code Grouping})='YES'
and
GroupName ({SCV_COURSE_ACT.COURSE_ID}) [10 to 12] = '001'
//{SCV_COURSE_ACT.COURSE_ID}[10 to 12]= '001'
then
{SCV_COURSE_ACT.TITLE}

course-report-10-13-11.pdf
0
 
mtroutAuthor Commented:
James,
Do you have a good reference on how to use variables?  I could use it please if you know of any.   Personally, I'm all over the place on this one.  Thank you.

Formula for High Level Name
global stringvar LastTitle;

if uppercase({?Pm-?Course Code Grouping})='YES'
and
GroupName ({SCV_COURSE_ACT.COURSE_ID}) [10 to 12] = '001'
then
LastTitle:={SCV_COURSE_ACT.TITLE}
else

{@LastTitle}

Formula for TitleFor001
global stringvar Title1;

if uppercase({?Pm-?Course Code Grouping})='YES'
and
GroupName ({SCV_COURSE_ACT.COURSE_ID}) [10 to 12] = '001'
Then
Local stringvar TitleToUse:= {SCV_COURSE_ACT.TITLE};

Title1:=TitleToUse

Formula for LastTitle
global stringvar LastTitle;

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

Formula for New Test

global stringvar Title1;

if uppercase({?Pm-?Course Code Grouping})='YES'
and
GroupName ({SCV_COURSE_ACT.COURSE_ID}) [10 to 12] = '001'
Then
Local stringvar TitleToUse:= {SCV_COURSE_ACT.TITLE}

else
if uppercase({?Pm-?Course Code Grouping})='YES'
and
GroupName ({SCV_COURSE_ACT.COURSE_ID}) [10 to 12] <> '001'
Then
TitleToUse;


Title1:=TitleToUse







course-report-10-13-11-b.pdf
0
 
mlmccCommented:
I have used the Peck series of Crystal books.  As I recall there is a good explanation of variables and formulas

http://www.crystalbook.com/program/misc/umbrella.asp

mlmcc
0
 
James0628Commented:
Sorry.  I don't have any reference on variables.  Just experience.  :-)

 You've added (if uppercase({?Pm-?Course Code Grouping})='YES') to the formulas.  At least, I think that's new.  What do you want those formulas to do if that is not "YES"?  Produce nothing?  The formulas may need some tweaking, depending on your answer.

 It's not a big deal, but you're using GroupName ({SCV_COURSE_ACT.COURSE_ID}) now, and I don't see any need to use GroupName.  You're grouping on COURSE_ID.  That will be the group name.  So, you could just use {SCV_COURSE_ACT.COURSE_ID}, assuming that COURSE_ID is a string (which I think it is, because I think you were using it as a string before).  There's no harm in using GroupName.  It just adds something unnecessary (I think) to the formulas.  But if it helps you in some way to use it, then, by all means, use it.  And if I'm wrong and COURSE_ID is actually numeric, nevermind.  :-)

 Some comments on your formulas:

 High Level Name:
 I'm not really sure what the purpose is for this formula, but you end it with {@LastTitle} (the formula), and I think you may want to end it with LastTitle (the variable) instead.  I'm not really sure of that, and it may not actually make any difference.  That was just my first thought when I looked at the formula.


 TitleFor001:
 I think that will wipe out your 001 titles in some situations, and that may be part of your problem.  Here's the formula that you posted:

global stringvar Title1;

if uppercase({?Pm-?Course Code Grouping})='YES'
and
GroupName ({SCV_COURSE_ACT.COURSE_ID}) [10 to 12] = '001'
Then
Local stringvar TitleToUse:= {SCV_COURSE_ACT.TITLE};

Title1:=TitleToUse


 If COURSE_ID has "001", you put TITLE in TitleToUse.  If not, you put nothing in TitleToUse.  But then, _either_ _way_, you put TitleToUse in Title1.  I think that's wrong.  For example, if you have COURSE_ID ABCDEFGHI001 and then COURSE_ID 12345, Title1 would be set to the TITLE for ABCDEFGHI001, but then when you got to 12345, Title1 would be set to "".  So, at the end, Title1 would be empty, instead of keeping the TITLE from the 001 group.

 If I'm right, the simplest thing is to just forget the TitleToUse variable and set Title1 directly.

// TitleFor001
global stringvar Title1;

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

Title1


 The Title1 at the end is just so that the formula will always output the current value of Title1 at the end, because that's what your version of the formula did.  If you have the field suppressed, then you can leave off that last line with just Title1 on it.  Without that line, the formula will output Title1 whenever it changes, or nothing (an empty string).


 The LastTitle formula:
 This formula looks OK.  It's similar to TitleFor001, but it doesn't use a second variable to set LastTitle (like your TitleFor001 formula did).  It just sets LastTitle directly (like my version of TitleFor001), which I think is what you want.


 New Test:
 You're still checking COURSE_ID in that formula, which I think is wrong.  As I understand/recall, your report has:
 Group 1 - Some field
   Group 2 - COURSE_ID

 You want to remember the title for the last (?) "001" course and "non-001" course in each group 1.  In the group 1 footer, you want to produce the "001" title, if there is one.  Otherwise, you want to produce the "non-001" title.  Correct?

 If you put @NewTest in GF1 and it checks COURSE_ID, it will only see the last COURSE_ID in that group 1.  If my description of what you're trying to do was correct, you should be looking at the Title1 and LastTitle variables at that point.  Title1 should be the last "001" title in that group 1 (once you make the changes to TitleFor001 that I mentioned earlier), and LastTitle should be the last "non-001" title in that group 1.  So, if you want to show the "001" title if there is one, otherwise the "non-001" title, NewTest would look like the version that I posted before:

global stringvar Title1;
global stringvar LastTitle;

if Title1 <> ""
Then
  Title1
else
  LastTitle


 Or, if you want/need to add that {?Pm-?Course Code Grouping} parameter:

global stringvar Title1;
global stringvar LastTitle;

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


 James
0
 
James0628Commented:
OK, maybe I'm being picky here, but I just realized that my example with COURSE_ID's ABCDEFGHI001 and 12345 kind of doesn't work.  It depends on 12345 coming after ABCDEFGHI001, and with the default sort, 12345 would be first.  Just replace 12345 with Z12345.  :-)

 James
0
 
mtroutAuthor Commented:
Mimcc,
  Thank you for the tip.  I had stumbled upon a video, and I believe that I will start over again with the variables.

0
 
mtroutAuthor Commented:
James,

        I need some time to review and digest your comments.  When I reviewed what I have sent to you before I am over writing something.  From what you had supplied, there are formulas that I can do without and make modifications to some others.  I will review, try and get back to you.  I have seen a value such that instead of 001 I need to compare 01,(the last two characters only).  The reason is that I saw one with V01.  Just a minor adjustment to compare from [10 to 12] to [11 to 12].  Maybe I should just compare [12] ?
0
 
James0628Commented:
Testing for [12] = '1' will match any string with a "1" in the 12th position (with any other characters before or after that).  If that gives you what you need, fine.  Otherwise, you may have to expand your test to look for something(s) more specific.

 Also, keep in mind that using [12] assumes that your strings will always be at least 12 characters long.  If any of them are shorter than that (eg. 10 characters), you'll get an error.

 James
0
 
mtroutAuthor Commented:
Thank you so much James for all of your responses. I will work with what you have given me and get back to you if I have further questions.  We will close this portion for now.  Thank you again and again.
0
 
mtroutAuthor Commented:
James,
Thank you.  I am seeing what I need to see thanks to you!  I just needed to make one minor adjustment to formula New Test
if uppercase({?Pm-?Course Code Grouping})='YES' then
if {@TitleFor001}<> ""
then
{@TitleFor001}
else
{@LastTitle}

I made all of the changes to the other formulas you referenced; thank you again.  When I referenced the formula, I would get the title I need to show in GF1.  Thank you so much again.  I think that I'm good for now....

course-report-results-10-24-11.pdf
0
 
James0628Commented:
I thought you'd want/need to use the variables (Title1 and LastTitle) in that formula, instead of the formulas that set those variables (@TitleFor001 and @LastTitle), but if you're getting the results that you wanted using the formulas, great.  IAC, glad I could help.

 James
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 8
  • 6
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now