Hello,

I am trying to create an active client list for the workers in my organization. I am having a very hard time due to how the data is stored in the database. I am using an ODBC connection and have included 2 tables in my report, linked as below:



A = active, W = waiting, T = terminated

Essentially every new status line in the same department cancels out the status before it.

I want a client name to display on the report IF the most recent STATUS in a specific DEPARTMENT = 'A' (active).

So using the sample above, client 898 is currently active in 2 departments (FS and TX) and is currently terminated from the INT department.

I have tried a bazillion ways to select and/or filter the report and I still run into the same issue. If I am trying to show a list of clients currently active in the "FS" department, because the client had a status of A in the INT department at some point in the past they are still showing up on my list when I use a formula like:
({department}="FS" and {status}="A")

(It's like Crystal is ignoring the 'and' in all my fomulas)

However, I guess what I really need is something like:
(most recent {department="FS"}
and
{status}="A")

I hope this is clear and someone out there can help end my frustration!!
Thanks!

Edited by alzniagara - 19 Feb 2014 at 10:05am

Yes, sorry I accidentally removed the image of the tables I included!

Hi - I'm not sure exactly what you mean by creating summaries for all my fields (I have a lot in the report!). What I did was create a maximum summary for status date.

I actually need the list to show all clients who have a current status of "A" OR "W" in department "CG" OR "FS". The way we track clients, every person with a status of "A" will have an older status of "W", so they are showing twice in each department group - which I was able to eliminate by suppressing if {CLIENT_NUMBER} = Previous({CLIENT_NUMBER}).

I think I have the data showing I need, but how do I sort the group alphabetically by client name? When I try to use a formula as the group sort order, the only fields it gives me access to are parameter fields?

You may have to create a sub-report to first filter the "A" status, then pass the results to the main report that will filter the "W" status.

I have never worked with sub-reports, but someone here may have.

I think this will work in you select statmetn use