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
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
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
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
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
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
ASKER
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.
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.
ASKER
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
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
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
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
ASKER
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
//{SCV_COURSE_ACT.COURSE_I
then
{SCV_COURSE_ACT.TITLE}
course-report-10-13-11.pdf
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
//{SCV_COURSE_ACT.COURSE_I
then
{SCV_COURSE_ACT.TITLE}
course-report-10-13-11.pdf
ASKER
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
then
LastTitle:={SCV_COURSE_ACT
else
{@LastTitle}
Formula for TitleFor001
global stringvar Title1;
if uppercase({?Pm-?Course Code Grouping})='YES'
and
GroupName ({SCV_COURSE_ACT.COURSE_ID
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
Then
LastTitle:={SCV_COURSE_ACT
Formula for New Test
global stringvar Title1;
if uppercase({?Pm-?Course Code Grouping})='YES'
and
GroupName ({SCV_COURSE_ACT.COURSE_ID
Then
Local stringvar TitleToUse:= {SCV_COURSE_ACT.TITLE}
else
if uppercase({?Pm-?Course Code Grouping})='YES'
and
GroupName ({SCV_COURSE_ACT.COURSE_ID
Then
TitleToUse;
Title1:=TitleToUse
course-report-10-13-11-b.pdf
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
then
LastTitle:={SCV_COURSE_ACT
else
{@LastTitle}
Formula for TitleFor001
global stringvar Title1;
if uppercase({?Pm-?Course Code Grouping})='YES'
and
GroupName ({SCV_COURSE_ACT.COURSE_ID
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
Then
LastTitle:={SCV_COURSE_ACT
Formula for New Test
global stringvar Title1;
if uppercase({?Pm-?Course Code Grouping})='YES'
and
GroupName ({SCV_COURSE_ACT.COURSE_ID
Then
Local stringvar TitleToUse:= {SCV_COURSE_ACT.TITLE}
else
if uppercase({?Pm-?Course Code Grouping})='YES'
and
GroupName ({SCV_COURSE_ACT.COURSE_ID
Then
TitleToUse;
Title1:=TitleToUse
course-report-10-13-11-b.pdf
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
http://www.crystalbook.com/program/misc/umbrella.asp
mlmcc
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
James
ASKER
Mimcc,
Thank you for the tip. I had stumbled upon a video, and I believe that I will start over again with the variables.
Thank you for the tip. I had stumbled upon a video, and I believe that I will start over again with the variables.
ASKER
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] ?
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] ?
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
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
ASKER
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.
ASKER
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
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
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
James
mlmcc