on 04-21-2009 8:48 PM
Newbie in Crystal having a problem with surpressing data
In the detail section, there is a class_name, a start_date and an end_date field for each staff's record
Class A-1 1/1/09 1/12/09
Class A-2 1/13/09 1/19/09
Class B-1 1/5/09 1/5/09
Class B-2 1/7/09 1/9/09
Class B-3 1/11/09 1/15/09
In the footer, there are fields for the minimum of start_date, the maximum of the end_date, grouped on the Class-name. There is also a formula subtracting the minimum_start_date from the CurrentDate to get the number of days the staff is working on each class.
Class A 1/1/09 - 1/19/09 19 days
Class B 1/5/09 - 1/15/09 11 days
The detail is surpressed already but I need to have the footer surpressed (and header too) if ALL the individual's classes are finished within 14 days.
I can't surpress on the formula counting the number of days worked on the classes, nor the dates. Any ideas would be appreciated.
Can you do this by grouping on the individual name instead of the class name?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
I think I need a bit more data to see this properly.
create table classData (
iInstructorID integer
, iStudentID integer
, cClassName c(20)
, tStart datetime
, tEnd datetime
)
insert into classData values
(1, 1, "Class A-1", '2009-01-01', '2009-01-12')
insert into classData values
(1, 1, "Class A-2", '2009-01-13', '2009-01-19')
insert into classData values
(1, 1, "Class B-1", '2009-01-04', '2009-01-05')
insert into classData values
(1, 1, "Class B-2", '2009-01-07', '2009-01-09')
insert into classData values
(1, 1, "Class B-3", '2009-01-11', '2009-01-15')
Is this more or less what you meant?
That's very close to what I'm working with - I added some changes
create table classData (
iInstructorID integer
, iStudentID integer
, cClassName c(20)
, cSection c(2)
, tStart datetime
, tEnd datetime
, iHours integer
)
insert into classData values
(1, 1, "Class A", u201CSection 1u201D, '2009-01-01', '2009-01-12', 1)
insert into classData values
(1, 1, "Class A", "Section 2",'2009-01-13', '2009-01-19', 1)
insert into classData values
(1, 1, "Class B", "Section 1", '2009-01-04', '2009-01-05',1)
insert into classData values
(1, 1, "Class B", "Section 2", '2009-01-07', '2009-01-09'),1)
insert into classData values
(1, 1, "Class B", "Section 3", '2009-01-11', '2009-01-15',2)
Ok. I created this data, then built a report on it. I added a group on iInstructorId, and then a group on cClassName. Then I created two formulas.
ClassLength (cClassName footer):
local datetimevar lnStart := minimum({classdata.tstart}, {classdata.cclassname});
local datetimeVar lnEnd := maximum({classdata.tEnd}, {classdata.cclassname});
DateDiff("d", lnStart, lnEnd);
AllClassLength (iInstructorId footer):
local datetimevar lnStart := minimum({classdata.tstart}, {classdata.iinstrid});
local datetimeVar lnEnd := maximum({classdata.tEnd}, {classdata.iinstrid});
DateDiff("d", lnStart, lnEnd);
.
Those functions returned:
Class A: 18
Class B: 11
Instructor 1: 18
When I told the various sections to suppress when {@AllClassLength} < 15, it appeared to suppress (or not) correctly.
Does this solve your problem, or am I missing part of it?
That didn't quite solve the problem.
Student XYZ, Class A: completed between 1/1/09 and 1/4/09,
Class B: completed between 1/26/09 and 1/30/09
Class A: 4
Class B: 4
AllClassLength: 30
The report needs to surpress if each class taken is completed within 14 days of starting the class. In this case, XYZ should surpress because both classes taken was completed within 14 days of starting each class.
What should show on the report are those students who take too much time completing a class.
Class A: 4
Class B: 12
Class C: 55
Does this make sense?
I'm working with Cache. I'll start over with the data
Each class consists of 4 hours, which can be completed over several days. Students take more than one class during the same period of time and each class can have more than one section.
table class_data
strInstructor:
strStudent:
strClass_Name:
strSection:
Date:
intHours:
(u201CAu201D, u201CJohnu201D, u201CABCu201D, u201CSection 1u201D, u20181/1/2009u2019, 1)
(u201CAu201D, u201CJohnu201D, u201CABCu201D, u201CSection 1u201D, u20181/12/2009u2019, 1)
(u201CAu201D, u201CJohnu201D, u201CABCu201D, u201CSection 2u201D, u20181/19/2009u2019, 2)
(u201CAu201D, u201CJohnu201D, u201CCDEu201D, u201CSection 1u201D, u20181/06/2009u2019, 1)
(u201CAu201D, u201CJohnu201D, u201CCDEu201D, u201CSection 1u201D, u20181/15/2009u2019, 2)
(u201CAu201D, u201CSamu201D, u201CABC,u201D u201CSection 1u201D, u20181/09/2009u2019, 2)
(u201CAu201D, u201CSamu201D, u201CABCu201D, u201CSection 2u201D, u20181/10/2009u2019, 2)
(u201CAu201D, u201CSamu201D, u201CCDEu201D, u201CSection 1u201D, u20181/12/2009u2019, 4)
(u201CAu201D, u201CMaryu201D, u201CABCu201D, u201CSection 1u201D, u20181/1/2009u2019, 1)
(u201CAu201D, u201CMaryu201D, u201CABCu201D, u201CSection 1u201D, u20181/31/2009u2019, 1)
The report is grouped by
Instructor
Student
Class_Name
The Detail sections has the class dates and hours - detail section is suppressed.
The group footer for Class_Name has the MinimumDate and the MaximumDate of the class and a formula that shows the number of days it took the student to complete the class.
A
John
ABC
1/1/2009 1/19/2009 19 days
CDE
1/6/2009 1/15/2009 11 days
_____________________________________
Sam
ABC
1/09/2009 1/10/2009 2 days
CDE
1/12/2009 1/12/2009 1 day
______________________________________
Mary
ABC
1/1/2009 1/31/2009 31 days
In this example, Samu2019s records should suppress because each class he took was completed with the time frame. Johnu2019s and Maryu2019s records should remain on the report. The end results of the report is to identify those individuals who took too many days to complete their classes.
Ok. Instead of adding a table for class_data, add a Command in the Database Expert, and put the following code there:
SELECT strInstr, strStudent, cClassName
, MIN(date) as MinDate, MAX(date) as MaxDate
, MAX(date) - MIN(date) + 1 as ClassDays
FROM classData
GROUP BY strInstr, strStudent, cClassName
Then, you can suppress on Maximum({Command.ClassDays}, {Command.strStudent}) <= 14.
User | Count |
---|---|
101 | |
13 | |
13 | |
11 | |
11 | |
7 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.