Phantom Dates???

[govmule]

Hello All,

I'm using Crystal Reports 8.5 connected to an Informix Dynamic Server 7.3 via native drivers.

I have a report that is grouped by a formula called 'ClientName' which is 'leadingcaps(client.last_nam & "," & client.first_nam). It's grouped by this so it will sort by Client Name.

The report is showing follow ups which there is a possible of four records in the DB per each client (1, 2, 3 & 4). It uses Follow Up Formula's like this: If follow up type = '1' then folup.interview_date to dispaly the dates these follow ups were made. I'm using a MAX of each follow up formula to get these results into the group footer.

Now I've noticed a couple of clients that have no follow up records in the database that are showing random dates for the MAX of these formula's when they should be blank.

Very wierd. Anyway any help is always appreciated.

 

[synapsevampire]

How are you constructing the MAX?

Copy and paste the SQL into your Informix query tool of choice to see what's going on.

You might also do a Database->Verify database just to make sure nothing has changed on you.

Applying the service pack is probably a good idea as well:

http://support.businessobjects.com/fix/hot/si75/default.asp

leadingcaps sounds like a UFL, and you don't really need it, just use:

uppercase({table.last})+", "+uppercase({table.First})

You can now group on this, and if you'd like, use the leadingcaps as your display formula.

A formula to use in lieu of a UFL would be:

whileprintingrecords;
Stringvar Input:="hello there world";
stringvar Out;
Booleanvar NewWord:=True;
numbervar Counter;
If len(trim(Input)) > 0 then
(
For Counter := 1 to len(trim(Input)) do(
If NewWord and
not(isnumeric(mid(Input,Counter,1))) then
Out:=Out+uppercase(mid(Input,Counter,1))
else
Out:=Out+mid(Input,Counter,1);
If mid(Input,Counter,1) = " " then
NewWord:=True
Else
NewWord:=False;
);
);
Out

Should be just as fast and you no longer have to deal with a UFL.

-k

 

[govmule]

Hello Vampire,

I just inserted a summary and used 'MAX'. The leadingcaps is not effecting this problem as I've removed it and still get the same results.

I did also verify the database and it did not help either. I also applyied the hot fix with no luck. This is so weird. The client had an exit date of 9/30/2004. There are no follow up records in the database but the report show the 1rst follow up interview as 9/23/02.

I did change the group from client name to client app num and it fixed the problem. The only thing now is I still need the report sorted by client last name.

Thanks for your help

 

[synapsevampire]

The caps formula wasn't intended to solve the proble, jsut get you out of using leadingcaps.

Is the relationship between client name and client app num supposed to be the same level?

I would guess that you have the same Client Name somewhere.

Since you're havign this difficulty, you might group by the client name, and then pass the client app to a subreport which is grouped by the client app to return the date of interest, but I would try to resolve the data issue.

-k

 

[synapsevampire]

One other thing, try changing the group to:

leadingcaps(client.last_nam & "," & client.first_nam)+totext({table.clientappnum},0,"")

That should help to identify the problem.

-k

 

[govmule]

Hey Vampire

Once again you've saved the day. When I added the clientappnum to the formula I was grouping on the phantom dates disapeared.

This is a very confusing issue for me. I can't understand how random data not in the database just appeared on a report.

Anyway, is there away to add the clientappnum to this formula but not have it displayed? Then it would fix the problem and the report would look the same.

As always, thank you!