Specific data to display

[Shusha]

Hi there,

If anyone can point me in the right direction wld appreciate it immensely.
I am working with Cr 8.5. This is what I am trying to display on the report. Let's say I have 10 clients. Each of them have a start date and an end date for their resp. treatment. I want the report to display the clients who do not have an end date in their record. the data looks like this:

client name Problem date of activity
client 001 Activity started jan 01 02
client 001 activity ended Jul 01 02
client 002 Activity started Feb 01 02
Client 003 Activity started Jan 01 03
Client 003 Activity Ended Jan 10 03
Client 004 Activity Started Aug 01 02
Client 999 Activity started Nov 01 01

What I want to display are the clients with no end date that is based on the above records I have all i shd see is

client 002 activity started Feb 01 02
Client 004 " " Aug 01 02
Client 999 " " Nov 01 01

If i say give me clients with no activity ended all of them show up. IS there a way to ask the system to shwo me
clients with a start date and no end date..

Any help wld be much appreciated. I know I can go to the select criteria and ask the system to discard the clients by their last name. Right now i am workign with a small amt of data , but when it gets to larger ones it will pose a problem. So , please help.. thanks in advance.

Usha

 

[synapsevampire]

You're best served to do this on the database side.

An alternative in CR is to group by {client name} and sort (or group by) the {problem} and store the {date of activity} in a formula in the {client name} Group Header to make the determination of whether the details should be displayed, based on whether it is null or not.

Since the text "Activity ended" will be the first in the group/sort if it exists, the {client name} Group Header formula would be something like:

// example: Formula Name {@Suppressme}
If {problem} = "Activity Ended" then
True
else
False

Now in the details suppression formula use:

{@Suppressme}

If there isn't a "Activity Ended" then it will be displayed.

I'm a bit tired, so this isn't very efficient, but it's functional.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[Shusha]

HI there,

I tried ur method but did not work. So I found what the regst type code was ( like u mentioned do the sort at database query level) and excluded anyone who was closed thereby getting only active clients. Thanks for your prompt reply.

Usha

 

[synapsevampire]

Not sure what did not work means, but I'm glad that you figured out a means to resolve this.

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[dgillz]

Would a client with activity ended always have exactly 2 records? And if so would any client with 2 records have ended the activity? Please advise Software Sales, Training and Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com

 

[Shusha]

Each client will have 2 records, one for started and one for ended. the ones who have started new will have only one record. They get the end date status only when they have completed the service. If i say give me all clients with end date as blank , which means only active clients it does not work as that field does not exist until the secretary puts in Service ended and then a date attached to it. We cannot start up a service ended record and leave the date blank. So we were stuck with this problem.

Thanks
Usha

 

[GJParker]

You need to group by Client and sort your records by ascending date within the Client group. Then if you add a summary count of activity date to the group this will show you which client has 1 or 2 records.

You can then do a conditional suppress on the client groups with a summary count of 2.

You would have to suppress your details section and place your client information in the group header.

Hope this helps. Gary Parker
Systems Support Analyst

 

[synapsevampire]

Gary: That's what I suggested, but I don't think that they understand what it meant, and since they already found a solution, I let it go.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com