select where detail by group...

[robmason10]

teaster for you experts

3 tables
Clients
Directors
Transactions

The three tables basically link to show a list of directors for each client.

I need to be able to select clients where their list of directors is comprised of ONLY (but not ALL) 12 specific 'internal' directors (who have a unique ID)

For example:
Client 1 may have 4 directors - 3 are internal 1 isn't - this client should not show in the list.

Client 2 has 5 dirtectors - all of which are internal - this client should be selected

Client 3 has 3 directors none of which are internal - should not be selected.

Any thoughts?

 

[Naith]

Rob,

You can't generate the kind of SQL you'll need on the fly in this case.

There's a couple of ways you could go about this.

You could minimise your selection criteria, so Crystal returns all records. If you group by client, order by your external/internal identifier so that all the external records per client come first. With the first record placed (and suppressed) in the group header, suppress the Client group if the record is external.

The negative with this approach is that it may take a while to execute as it'll return rows excessive to your requirement.

The alternative is to write a stored procedure which works this out for you. The SQL would aim to compare the count of records by client with the count of records where directors were internal by client. Where the count is the same, feed it to the report. You could achieve the same making use of the minus function too. The advantage of this approach is that it'd run a lot quicker than the previous one.

Naith

 

[robmason10]

Thanks - but - how do I get just the first record in the group header and all other records in the detil? Or am I off the mark?

 

[SmpTraining]

Just copy the field(s) from the detail section into the header section. The first record in the group will appear in the Header.

By the sound of things, Naith is suggesting copying the internal/external field into the header and suppressing it.

Then conditionally supress the header based on the internal/external field.

Of course, you will also need to supress the details and the group footer. Steve Phillips, Crystal Consultant

 

[Naith]

You don't really need the full record in the group header. My misdirection there - sorry about that. You retain the record in the details section, and place the field which identifies the internal/external information in the group header. Suppress this field. Remember to order by the same field, and base the suppression of the group on the field.

Naith

 

[robmason10]

Sirs you are true gentlemen - all sorted.