Problem Suppressing Group

[Former Member]

Post Author: bh1

CA Forum: General

I

have a report designed from SQL that reports on pupils who have passed

their 50, 100, and 150 metres swimming badges. If they have passed then

a 1 is displayed in the column, if they havenu2019t a 0 is displayed. The

Passed column is a formula field that will show a 1 (meaning Pass) if

they have passed all three (50,100 and 150) and a zero if they havenu2019t.

The report is grouped by Team and Tutor.

Team 1 (GROUP1)

Tutor Dave Harding (GROUP2)

Pupil ID Name 50m 100m 150m Passed

145 Ian Jones 0 1 0 0

243 Trevor Smith 1 1 1 1

456 Dave Davies 1 0 0 0

Each page is formatted so that a different tutor will be displayed,

with a list of his/her pupils. I have suppressed the Detail section to

only show pupils who ARENu2019T up to date so it will only display pupils

where the Passed column is 0.

The problem that I am having is that I want to suppress the Group

Header for the Team and Tutor if there are no pupils to display. I am

having trouble suppressing these sections as they are grouped. Can

anybody help?

[Former Member]

Post Author: bh1

CA Forum: General

I must apologie, due to the complexity and the sensitive data of the report - I haven't provided enough information. I will try again. (Thanks to all the people who have replied so far)

Team A

Tutor 1

Id

Name

50m

100m

150m

200

250

145

Ian

1

0

0

NOT UTD

1

1

UTD1

243

Trevor

1

1

1

UTD

0

1

NOT UTD1

456

Dave

1

1

0

NOT UTD

1

1

UTD1

Tutor 2

Id

Name

50m

100m

150m

200

250

145

Ian

1

1

1

UTD

1

1

UTD1

243

Trevor

1

1

1

UTD

1

1

UTD1

456

Dave

1

1

1

UTD

1

1

UTD1

The report I have done looks similar to this. The 50m, 100, 150m, 200, and 250 fields are all calculated fields in crystal where if the child has a record for 50m then display 1, or if null or no record display 0. The UTD and UTD1 field are complex fields that take into account the age of the child and whether the columns are equal to 1.

The report is divided into 3 groups - Team, Tutor and pupil.

I have tried using the record selection to select only Not UTD or Not UTD1 values but these formula fields dont appear in the Record Selection. Because the UTD and UTD1 use several formulas to calculate I cannot use the Record Selection.

I have used the suppress in the detail section to suppress if UTD and UTD1. This is fine but when I want to suppress the Tutor and possibly team if all of the pupils are up to date I am having problems. There will always be a record under the tutor on the drill down even if they are all Up to date.

Would it be possible to create some sort of function that starts off with the value False, scrolls through each tutors pupils and changes to TRUE if not up to date e.g. NOT UTD. The value would need to remain at TRUE even if it does come accross a person who is up to date.

The header for the tutor could then be suppressed witht the formula if value = true

e.g.

Team A

Tutor 1

Id

Name

50m

100m

150m

200

250

145

Ian

1

0

0

NOT UTD

1

1

UTD

U 243

Trevor

1

1

1

UTD

0

1

NOT UTD

456

Dave

1

1

0

NOT UTD

1

1

UTD

FALSE

Tutor 2

Id

Name

50m

100m

150m

200

250

145

Ian

1

1

1

UTD

1

1

UTD

243

Trevor

1

1

1

UTD

1

1

UTD

456

Dave

1

1

1

UTD

1

1

UTD

TRUE

Hope someone can help??

[Former Member]

Post Author: V361

CA Forum: General

I created a sudo report using your data. Formula "@test_me" to add up the results (if () = 3 then 1 else 0) I can then use this formula in the select statement. {@test_me} <> 0, if all the students in that section have not completed all three (50, 100, 150) then that section (group headers and all, is blank) if any one student, then group header and student details show. Hope this will help.

[Former Member]

Post Author: Charliy

CA Forum: General

You can use those formulas in the select formula.

{@50m} + {@100m} +{@150m} < 3 Would be the people who have not passed all three

[Former Member]

Post Author: bh1

CA Forum: General

Another twist in the tale,

Because 50 metres, 100 metres and 150 metres are already formula fields I am unable to use the select expert.

The original table looked like this

ID Name Swimming Badge

145 Ian Jone 100

243 Trevor Smith 50

243 Trevor Smith 100

243 Trevor Smith 150

456 Dave Davies 50

For each of the fields 50 metres, 100, 150 the formula looks like this

if = "50" then 1 else 0. The Select Record Formula doesn't display the calculated field. Is there anyway I can select the people that have passed without rearranging the whole report. The report that I am trying to achieve is far more complex than this but this it is the same issue I am having!!!

Help

[Former Member]

Post Author: Charliy

CA Forum: General

If the fields for 50m, 100m, and 150m are all in the same record, then you can put: ...and {@passed} = 0 ... in your select formula.

Use the Select Record Formula from the REpport menu - not the Select "Wizard"