Conditionally surpress

[Former Member]

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.

[Former Member · Accepted solution]

Can you do this by grouping on the individual name instead of the class name?

[Former Member · Accepted solution]

I didn't explain far enough - the report is grouped first on the instructor's name, then the individual's, then the class.

[Former Member · Accepted solution]

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?

[Former Member · Accepted solution]

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)

[Former Member · Accepted solution]

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?

[Former Member · Accepted solution]

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?

[Former Member · Accepted solution]

I don't have the report set up quite right to test this, but I think you can get what you want by suppressing on

Maximum({@ClassLength), {Data.cClassName}) <= 14

[Former Member · Accepted solution]

I've tried that one. Everytime I've try to surpress on a formula I get an error message:

"This field cannot be summarized"

I'm working in Crystal X

[Former Member · Accepted solution]

I think we need to let the back end do some of the lifting here. What database are you using?

Also, can you post sample data that will give the results from your previous post? That would be useful.

Edited by: Garrett Fitzgerald on May 6, 2009 10:15 AM

[Former Member · Accepted solution]

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.

[Former Member · Accepted solution]

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.