Only Data For First Record Shows Up??? 2

[govmule]

Hello Everyone,

I'm using Crystal Reports 8.5 with an ODBC connection to an Informix Dynamic Server 7.3. Here is my dilema:

My report is referencing 4 records in the same table, 'followup'. There are two fields being referenced

1) Follow Up Type - 1,2,3,4 for 1rst, 2nd, 3rd and 4th quarter.

2) Interview Date - The date the followup was conducted.

For display purposes I've added a group for a unique client id and placed all of the fields in the group footer. I did this so they would all display in one line.

I've then created four formula's, one for each follow up type. Here is an example:

If {follow_up_type} = '1' then {follow_up_intv_dt}
I've placed all four of these in the group footer as well.

My problem is the data will only be displayed if it is the first record in the follow up table. If a client has a first, second, and third qtr completed it will only show data for the first. If a client has not has his first, but has had his second and third, it will only show the second.

I had solved this problem using a subreport for each interview date but using this many subreports has caused me additional problems and is really hard on my limited version of Crystal Enterprise 8.

I hope this isn't too much detail and appreciate any help.

Thanks,

Jack Luster

 

[synapsevampire]

Add another group by {follow_up_type} and place your fields in that group header or footer.

You'll get one row per quarter (this assumes that {follow_up_type} is the quarter).

-k

 

[govmule]

Hi Vampire,

I was hoping to avoid that. That was originally how I designed the report. The users complained that they wanted all data on one line. Can you think of any other way? This report becomes four times as large this way and it's already extremely big.

If not, that's cool! The users will have to DEAL WITH IT!!!

I'm just kidding, sort of.

I appreciate your thoughts and help!

See ya,

Jack

 

[GLmill]

You could suppress the Blank sections. Place this in the formula field for the formatting of this group and check the suppress box.

if {follow_up_intv_dt}=0 then true else false;

At least then you can get rid of the blank lines.

-TO

 

[lbass]

If you only have one record per followup type, then place formulas like the following in the details section, e.g.:

If {follow_up_type} = '1' then {follow_up_intv_dt}

And then insert a maximum (right click, insert summary) on each formula, and this will carry the results into the group (client) footer. You can then add text boxes to identify the specific followup type.

-LB

 

[govmule]

YYYEEEAAAHHH!!!

Thank you everyone, especially lbass. This solution ranks up there with sliced bread! I so appreciate it! It also has shown me to pay more attention to the 'DETAILS'!

Your help is much appreciated (this worked perfectly by the way!)

Cheers,

Jack Luster

 

[govmule]

Hello,

I've got one more thing and this report is finished. I need to only display records where one of the four {follow_up_intv_dt} is null.

I've tried adding '(IsNull(Maximum ({@4th_qtr_int}, {@client_name}))' to the group selection formula. I expected this to return all records that had no {follow_up_intv_dt} for {follow_up_type} = '4'. Instead it returned 18 records displaying no information for the
{follow_up_intv_dt}.

Any ideas?

 

[lbass]

You could try this as your group selection formula:

minimum([{@type1date},{@type2date},{@type3date},{@type4date}]) = Date(0,0,0)

//where {@type1date} is the name of formula:

If {follow_up_type} = '1' then {follow_up_intv_dt}

This should work since the default for each of these formulas would be Date(0,0,0). I don't think you need to add anything to designate the group for this to work.

-LB

 

[vafishing]

I also have a similar problem. I am trying to show an alumni report in which the ID would show only once based on the school they graduated from. Here is an example
ID Name School AddressLocator
122 John Smith Business good
122 John Smith Business good
123 Jane Doe Humanities good
125 Eric Smith Dentistry good
The idea is to show a total of 3 records. I want to show Smith one time since he got 2 degrees in the same school. The disinct count works for the ID but I want to show under AddressLocator 3 good records and grouped by school. I tried the formula if {address_full.addrlocatr} in ["g"] and {@onfirstrecord}=true then 1 else 0 The onfirstrecord is this formula
if not onfirstrecord and {GENERIC.id_number} <>previous({GENERIC.id_number}) then true else false I get the number sequence okay but I cannot add it. The problem with running total is that it gives a total from 1 to ## and does not group it based on School. Any ideas?

 

[lbass]

You could just do a running total like this:
Select {table.personID}, distinctcount, evaluate based on a formula: {address_full.addrlocatr} = &quot;g&quot;
Reset never. This will give you one result per id.

If you want to display people only once, group on {table.personID} and then drag the fields into the group footer and suppress the details section. (The running total must appear in a footer (not header) section.

-LB