If you know SQL, then it should be easier to get exactly what you want rather than filtering out what you don't want in crystal. It would also be better performance-wise since you know exactly what you want and therefore only get that.

Simply write your SQL query and add a Command to the report rather than selecting all of the tables when you're choosing your data source.

The recent amounts due report should be a matter of stating a where clause that specifies the date range. If the client wishes to specify his own date range, then you have the option of using parameters in the Command.

The second report I'm not sure what is meant by "first occurrence". Is it because there are duplicates? Or perhaps you want a list of unique customers rather than a list of all of the amounts that are due?

I interpreted the first report incorrectly.
This is a solution:

1: get all of the amounts due for all , ordered by date

2: create a group based on the clients. Each record in each group should be sorted by date from most recent to earliest (since the query ordered records by date). Since you are only interested in the most recent amount, you can move the "amount due" field into the group header. This should give you the most recent one as it should be the first record.

If you don't want to learn SQL, you can still create the report. You would pick the tables when you're first choosing the data source and then simply drag and drop the correct fields onto the report. You might need to do a little more sorting and grouping though.

I've never done that so I'm not sure how that would work

But if you are interested in learning SQL, which I recommend, there are many guides out there.

The query would look like pretty much any other SQL statement:

select client, amount, date

from cli_clients

order by date desc

You can do the second report after you finish the first one. I think the first one is easier.

Edited by Keikoku - 25 Mar 2011 at 6:35am