Crystal Reports - Running total excluding singular row

Question

I'm not sure the best way to phrase this, so I will try: Is there a way to count a total of distinct IDs, but exclude the IDs that only have code 1111? But include the rows that have 1111 PLUS any other codes.

ID	CODE
123	1111
123	8888
456	1111
789	1111
789	8888
789	9999

Total (2) would include IDs 123 and 789.

I have not tried anything yet, just brainstormed, and feel stuck.

Answer 1

First, let's tackle a simple case where you exclude rows (not groups) that contain the CODE 1111

Do a Distinct Count on a formula that returns the ID for the "good" cases and a Null value for the excluded cases. For example:

IF {CODE} <> "1111" Then {ID} ELSE {@Null_String}

To create the {@Null_String} formula, just create it and then save without any expression.

IF {ID} is a numeric column, use:

IF {CODE} <> "1111" Then {ID} ELSE Val({@Null_String})

When you shift the focus from row-level to group-level exclusion, things get more complicated. One option is to create a database View/SP or a Crystal Command that takes care of the exclusion of those groups.

Another option is to use a Crystal Reports UFL (User Function Library). Ken Hamady maintains a listing of 3rd-party Crystal Reports UFLs here. And at least one of them allows you to leverage one function in a Crystal formula to establish an in-memory table of all group values with non-1111 codes. The formula can use 'WhileReadingRecords;' so the results can be used at the top of the report or in any other report "pass". That UFL provides another function that can directly return a count of those groups in-memory table. Or you can do a lookup for any group value and see if it in the table.

Using similar logic, you can use a subreport in the report header to establish a shared array variable with all group values that contain a non-1111 code.