Suppress Specific Sections

[Former Member]

I have a report that looks like this

hospital (group 1)

     doctor who (group 2)

          details .... details

group footer:   clients without services: 2 (summary of details)

                       caseload: 55 (summary of details)

     doctor John (group 2)

          details... details

group footer:   clients without services: 0

                        caseload: 32

primary care

     doctor Jane

         details... details

group footer:   clients without services: 0

                        caseloads: 66

My question is how do I suppress all sections where clients without services= 0. I tried to do this using a formula in the section expert > suppress > formula. However it does not let me use a summary field in the formula. Thanks for the help!

[DellSC · Accepted solution]

The only way I can think of to do this requires a Command (SQL Select Statement).  How are your SQL skills?

Basically the Command will pull the data for your groups and the sums for "clients without services" and "caseloads" - you'll set it up so that it doesn't pull data for docs who have no clients without services.

Then you would suppress the details section and in either a group2 header or footer create a subreport to get the details.

For more information about how to use commands, see here:  .

-Dell

[Former Member · Accepted solution]

Hmm ok, I am proficient in SQL, so I will see what I can do. Thank you.

[Former Member · Accepted solution]

I am working on my sql command, and I am wondering is it possible to make a table in a crystal reports sql command and then use that table in the same command later on?

[DellSC · Accepted solution]

No - Crystal doesn't work well with temp tables.  However, when you create the subreport for the details, you'll link to the doctor's ID number (or whatever you're grouping on) so that it pulls only the data for that doctor.  The query will run every time the subreport runs.

-Dell

[abhilash_kumar · Accepted solution]

Hi Nate,

How do you calculate 'Client without services' on the report?

Is it a formula?

-Abhilash

[Former Member · Accepted solution]

No, it's a summary of the details. I could probably try to do it using a formula, and the use that to suppress the sections maybe. Do you think that would work?

[DellSC · Accepted solution]

The problem with doing it in a formula is that it won't have a final value until after all of the detail records have been read.  At that point you could suppress the group footer, but not any of the sections above that.

-Dell

[Former Member · Accepted solution]

Actually, I don't think that will won't work, because eventually I want to suppress the details section of the report.

[abhilash_kumar · Accepted solution]

You can add a 'Group Selection Formula' (Report > Selection Formulas > Group):

Sum({field_to_sum}, {Group_field}) > 0

-Abhilash

[Former Member · Accepted solution]

Is it possible to reduce this process to one query?

*Oh and clients without services in my example is clients without services for 90 days.*

Right now in access:

Select d.doctor_id, , DateDiff("d", [last_date_of_service], date()) AS days INTO Table 1

from  doctor as d

Where DateDiff("d", [last_date_of_service], date()) >= 90

Then I inner join Table 1 with doctor on doctor_id, so I get all the doctors patients and not just the ones with no services for 90 days.

Or what would be the process for doing this in a subreport? I have just begun using CR, so I am only slightly familiar with subreports.

[Former Member · Accepted solution]

This does exactly what I need it to do. Thank you very much.