2

I need the Crystal Reports syntax for this query below. Basically, I want to suppress a detail section if all of the records are not applicable. Appreciate your help on this.

if (select count(*) from appendix where is_applicable=0) = (select count(*) from appendix)
dizzyone
  • 43
  • 7
  • 2
    You can create a sql expression with your query, and use it in your formula like you want – Furtiro Aug 30 '16 at 13:19
  • yes, is_applicable is a yes/no field – dizzyone Aug 30 '16 at 13:25
  • There can be multiple sub-sections displayed of the appendix. If all of them are not applicable, then the entire section should be suppressed. However, if one or more are applicable, they should all show. – dizzyone Aug 30 '16 at 13:40
  • 1
    Ah, okay. That would *technically* be possible within Crystal, but you'll save time and effort if you just stick with a SQL expression like @Furtiro suggests. – 4444 Aug 30 '16 at 13:43

1 Answers1

1

This will be tricky, because summaries and running totals can only be evaluated after the records have already been printed. So if a SQL expression is out of the question, the easiest way I can think of would still be a little complicated:

  1. Add a new Group for is_applicable at the deepest level. (Right next to the Details section.) Suppress the header/footer and sort by descending order.
  2. In the Details section, create a Shared Boolean Variable that flips over to True every time it prints next to an is_applicable equal to 1. Reset the variable back to False every time the 2nd deepest Group changes.
  3. In the Details suppression section, write logic to suppress if the shared Boolean is True and is_applicable is equal to zero.

Done correctly, this should print any applicable entries first, and if none are found, the inapplicable entries won't display.

4444
  • 3,541
  • 10
  • 32
  • 43