Filter by field in Crystal Reports without selecting it (distinct)

Question

I have a report in which I would like to filter the data based on one field, let's call it status. I have activated distinct values, as the select statement returns duplicate records. My problem is that the generated select statement now includes the displayed fields and the filter column status, something like this:

SELECT DISTINCT column1, column2, status
FROM table1
WHERE status <> 'Retired';

This leads to the situation that the added status generates "duplicate" records in the view:

column1 | column2 | (status not shown)
A       | B       | Active
A       | B       | Inactive
C       | D       | Active
E       | F       | Active

How can I remove the status from the generated select statement and keeping the where condition?

Thanks in advance!

Answer 1

I have a solution, which is not so nice, but works fine. This makes use of groupings by using them as detail cells.

1. Insert a group for every column you want to show in your grid (column1, column2 in our example)
2. Suppress the drill down for all but the last group header
3. Suppress the details
4. Suppress all group footers
5. Move the group headers from the suppressed group into the last visible group row, so that they match the headers
6. Optional: remove bold formatting of group headers, which are now the "detail" cells

Result in the design tab.