Repeated info on label report 1

[techlady2000]

I've asked this question before and still don't have an answer. I am printing reports on labels so the information can "peel and stick" on a card.
The report is a year-end report card for student grades. The primary table I'm pulling from has a line for each student and each subject, so a student has 7 lines in the table. I can get the report I want, but the label repeats 7 times, all identical with all information for all subjects. How can I suppress so only 1 label prints for each child?
It's obvious I have some gaps in my Crystal knowledge base!

 

[Ngolem]

Show us how your report is set up...we are only guessing otherwise.

MY GUESS is that your labels are in the detail section rather tan suppressing the detail section...processing the labels you want then assigning them to individual labels in a footer

but I will not go any further until you say

1. How your report is grouped?
2. What section you are currently printing the labels from
3. Names of fields printed on each label Jim Broadbent

 

[techlady2000]

You are SO good!
1.I'm grouping by Teacher ID so teachers can easily get their student labels.
2.Yes, I'm printing from details
3. Each line in the table has individual six weeks grades and a final grade for one subject. That means just under 49 pieces of data for each label(not all have a final grade).

 

[Ngolem]

49 datafields/label?? wow! Big labels...How many labels on a page 8.5 X 11... 6 labels? I don't see how this repeat of 7 comes in. How are the labels arranged on the page if there isn't 6?

So if I have this correct...for each student there are 7 subjects..each of which have 6 weekly grade values + a possible final grade.

How are they laid out in the report?? Show me what you would like each label to look like.

I have a hard time imagining 6 labels/page

are they laid out like this?

Teacher
Student name/number:

Subject Hist Math English Science Art PhysEd Health

Week 1
Week 2
Week 3
Week 4
Week 5
Week 6
Final

this would take about 15 lines...completely across an 8.5 x
11 page in portrait mode .... suggesting 4 labels/page is that correct.

If this is the format I think I know how you should do it.

If it isn't then show me an example of what it shuold look like.
Jim Broadbent

 

[techlady2000]

The label is an Avery 5164 (3-1/3x4) 6 labels per sheet
The format is columnar
Student Name Etc
Reading sw1 sw2 sw3 sw4 sw5 sw6 final
Language " " " " " " "
Math . . .

It all fits nicely, but the extra copies drive me nuts!

 

[Ngolem]

ok...now we are close to a definate solution

just a couple of more short questions

the marks for sw1,sw2,sw3...final.... Are these fields within a subject record...or is each one in a separate record...?

what version of Crystal Reports are you using?

the marks...are they A,B,C,A+ variety or percentages?

I think I have a nice way of doing this...I originally thought that I could use a multi-column approach for doing this but this can only be done in the detail section...your labels should be printed from the Student group footer.

Your report probably should be grouped by

Teacher
Student
subject
mark week (unless all listed in the one subject record)

We will print the records every second student so that we can have a pair of labels printed at a time (with a provision for the last student being an odd number)

Please advise what student information is printed and we shall go from there.

Jim Broadbent

 

[JustaUser]

I have a formula that displays only the first record regardless of the record count. It has to be in the detail section of the report. Go to the section expert and hightlight the detail section. On the formula field type in the corresponding fields to match. This is the actual formula used;
PREVIOUS ({AR_CUST_MAST.CUSTOMER_NAME}) = {AR_CUST_MAST.CUSTOMER_NAME}

I use this formula in crystal 7 & 8 and works great.

 

[Phillipg]

maybe make a formula to get the next file. name the field "nextstudent" and the formula is as simple as next{'your student name or number field'}.

 

[Ngolem]

...this report is much more complicated than that....this cannot be processed in the detail section ....why??.... because there are 2 labels horizontally...in other words...2 student records must be processed before you can start printing the label...hence this must be done in the Student group footer and not in the detail section.

If there was only one label horizontally then it would be a trival problem easily accomplished in the detail section.

you cannot use Next() or previous() to get the next student's id for example since the student's name is repeated on several records before their information is collected...unless of course there is only 1 record /student which at 49 pieces of info/student I highly doubt. Jim Broadbent

 

[kenhamady]

If all of the fields used on the labesl are identical in each copy, you might try using the 'SELECT DISTINCT' option in the database menu. This only works in SQL databases, and assumes that there are no fields used by the reports that are unique to each duplicate record. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[Ngolem]

Even if the records are distinct Ken, You still cannot place 2 labels for different students...side by side unless you are formatting multi-column and that only works when all reported fields (over 49 fields it seems) are packed into one student record...which seems highly unlikely to me...unless I am missing something obvious. Jim Broadbent

 

[techlady2000]

To answer the short questions:

the marks for each subject are contained within the subject record

I'm using Crystal 8

Grades are numeric or letter, depending on subject.

I have the formulas constructed for the marks and they work fine.

 

[Ngolem]

ok...here is my approach...it is not the only approach but I think it should work fine.

So if I read you correctly all of the marks for a subject are contained in a single record

then you should group your report as follows:

Reportheader - (suppressed)
Pageheader - (suppressed)
GroupHeader1 - Teacher ID (suppressed)
GroupHeader2 - Student ID (suppressed)
GroupHeader3 - Subject (suppressed)
detail (suppressed)
GroupFooter3 - (suppressed)
GroupFooter2 - (not suppressed...this for label info)
GroupFooter1 - (suppressed)(enable "New Page After&quot
PageFooter - (suppressed)
ReportFooter - (suppressed)

to gather the information we will create variables and we will print these variables after every second student...that way we can get 2 labels side-by-side. We cannot store ALL the student information since it would be too difficult to create labels...but this should work fine

to control the reset and printing of the label information we shall use a counterflag in a formula as follows:

*****************************************************
@counterflagInitalize (placed in the report header)

WhilePrintingRecords;
numberVar countFlag := 0;
*****************************************************

the following will initialize the variable/arrays we need. the arrays will store the marks for each subject

*****************************************************
@Initialize (placed in the Group2Header)

WhilePrintingRecords;
numberVar countFlag ;

if countFlag = 0 then
(
stringVar Student_1 := "";
//array elements are sw1,sw2,sw3,sw4,sw5,sw6,final
stringVar array S1_Math := ["","","","","","",""];
(... add in the other 6 subjects... eg. S1_Lang)
stringVar Student_2 := "";
stringVar array S2_Math := ["","","","","","",""];
(... add in the other 6 subjects...)
countFlag := 1;
);
*****************************************************

now we collect Student Information

*****************************************************

@StudentInfo (placed in the Group2header)

WhilePrintingRecords;
evaluateAfter(@Initialize);
numberVar countFlag ;
stringVar Student_1 ;
stringVar Student_2;

if countFlag = 1 then
Student_1 := {table.SName} + "" + {table.StudentID}
else if countFlag = 2 then
Student_2 := {table.SName} + "" + {table.StudentID};

*****************************************************

Now we create the marks information gathering formula

*****************************************************
@GatherMarksInfo (placed in the detail section)

WhilePrintingRecords;
numberVar countFlag ;
stringVar array S1_Math ;
(...Plus the other 6 subject arrays....)
stringVar array S2_Math ;
(...Plus the other 6 subject arrays....)

if countFlag = 1 then
(
if {table.subject} = "Math" then
(
S1_Math[1] := {table.sw1};
S1_Math[2] := {table.sw2};
S1_Math[3] := {table.sw3};
...
S1_Math[7] := {table.Final};
)
else if {table.subject} = "Language" then
(
S1_Lang[1] := {table.sw1};
S1_Lang[2] := {table.sw2};
S1_Lang[3] := {table.sw3};
...
S1_Lang[7] := {table.Final};
)
[...repeat this structure for all subjects...]
)
else if countFlag = 2 then
(
if {table.subject} = "Math" then
(
S2_Math[1] := {table.sw1};
S2_Math[2] := {table.sw2};
S2_Math[3] := {table.sw3};
...
S2_Math[7] := {table.Final};
)
else if {table.subject} = "Language" then
(
S2_Lang[1] := {table.sw1};
S2_Lang[2] := {table.sw2};
S2_Lang[3] := {table.sw3};
...
S2_Lang[7] := {table.Final};
)
[...repeat this structure for all subjects...]
);

***********************************************

Now we have all our information stored...

to create the labels we require several formuals for each label...the reason is that not all marks will have the same number of characters (eg. one could have an A or A+ so it is impossible to garuantee a nice alignment if they were concatonated together since spaces and characters are treated differently in most fonts.

you require 2 formulas one for each label I will only show Student 1 values

@DisplayStudent1_info

WhilePrintingRecords;
stringVar Student_1 ;

"Student: " + Student_1;

***********************************************
@DisplaySubjectLabels (enable the Field "Can Grow&quot

//the first carriage return is to advance one line before
//printing (so the sw1 label won't interfere).

chr(13)+ chr(10) + "Math" + chr(13)+ chr(10) + "Science" +
chr(13)+ chr(10) + (...add in the rest of the subjects...);

***********************************************
@DisplayStudent1_SW1 (enable the Field "Can Grow&quot

WhilePrintingRecords;
stringVar array S1_Math ;
(...Plus the other 6 subject arrays....)

"sw1" + chr(13) + chr(10) + S1_Math[1]+ chr(13) + chr(10)+
S1_Science[1] + (...add in the rest of the subjects...);
***********************************************
Note: the same structure is repeated for SW2 to Final marks

Repeat for Student 2 and arrange the formulas in the labels appropriately.

NOW COMES AN IMPORTANT STEP...We want to suppress the Group2 Footer unless countFlag is equal 2 OR we are on the last record

In the conditional suppress for the Group2 footer, place this formula:

WhilePrintingRecords;

NumberVar countFlag;
NumberVar temp;

temp := countFlag;
countFlag := countFlag + 1;
if countFlag = 3 then countFlag := 0;
//the last statement controls wheter or not the suppress is
//done
if temp = 1 then true;


I think that is all....perhaps I have missed something or you may have questions....just ask if something isn't clear.

Jim Broadbent

 

[Ngolem]

oops....forgot something on the last statement

you want the labels printed:

1. every second label
2. when there is a change in teacher
3. on last record

so that last line should be

if temp = 1 and
not onlastrecord and
not isnullnext((table.teacherID})then
true
else
false; Jim Broadbent

 

[techlady2000]

I'm working on the formulas and everything has been great!
However, on the @DisplayStudent1_SW1 I get an error message "The result of a formula cannot be an array."
Here's my work

WhilePrintingRecords;
stringVar array S1_RDG;
stringVar array S1_LAN;
stringVar array S1_MAT;
stringVar array S1_SCI;
stringVar array S1_SOC;
stringVar array S1_PHY;
stringVar array S1_MUS;
stringVar array S1_ART;
stringVar array S1_HDW;

"SW1"+chr(13)+chr(10) +S1_RDG+chr(13)+chr(10) +S1_LAN +chr(13)+chr(10) +S1_MAT +chr(13)+chr(10)
+ S1_SCI +chr(13)+chr(10) +S1_SOC +chr(13)+chr(10) +S1_PHY +chr(13)+chr(10) +S1_MUS +chr(13)+chr(10)
+S1_ART +chr(13)+chr(10) +S1_HDW;

 

[Ngolem]

I am glad things are going well....your problem here is that you have specified the complete array....not an element of each array. Just add [1] as shown below:

WhilePrintingRecords;
stringVar array S1_RDG;
stringVar array S1_LAN;
stringVar array S1_MAT;
stringVar array S1_SCI;
stringVar array S1_SOC;
stringVar array S1_PHY;
stringVar array S1_MUS;
stringVar array S1_ART;
stringVar array S1_HDW;

"SW1"+ chr(13)+ chr(10) + S1_RDG[1] + chr(13)+ chr(10) +
S1_LAN[1] + chr(13)+ chr(10) + S1_MAT[1] + chr(13)+ chr(10)
+ S1_SCI[1] + chr(13)+ chr(10) + S1_SOC[1] + chr(13)+
chr(10) + S1_PHY[1] + chr(13)+ chr(10) + S1_MUS[1] + chr(13)+ chr(10) + S1_ART[1] + chr(13)+ chr(10) + S1_HDW[1] ;

that should work better Jim Broadbent

 

[techlady2000]

OK, it's been a long time. Let's just say it wasn't urgent then and now I want to get back to the report.
When I run the report with formulas as above, my labels pages are like this:
PAGE1:
no student no student
student2 no student
student2 student3

PAGE2:
no student no student
student4 no student
student4 student5

etc.....

I think it must have something to do with countFlag in formulas 1-3, but that's just my uneducated guess. Can you help?