Repeating rows 1

[micro2005]

Crystal Report 10 and SQL server 2000.

I have a report with one group (area). Each of these areas have severl complaints and then there are different dates associated to each complaint. For example file date, investigation request date, hearing date, etc. All these dates are based on formula that I have created.
The problem I am facing is that when a complaint has more than one date associated to it the rows repeat. I would like to get all these dates in one row per case.

Current:

Details section:

complaint # File date Investigation date Hearing date
123 1/1/2005
123 1/2/2005
123 1/3/2005

Desired:

complaint # File date Investigation date Hearing date
123 1/1/2005 1/2/2005 1/3/2005

I have tried adding a group on complaint # but then only one date shows up. Can someone please tell me what I am doing wrong here?

 

[synapsevampire]

Group by the complaint, the modify your formula to store the dates for display at the group footer (suppress the group header and the details):

A shame that you felt stating "For example file date, investigation request date, hearing date, etc. All these dates are based on formula that I have created." was more important than sharing the actual formula used.

It sounds like you have multiple dates in the recordset returned, and that your formula is deriving something.

Anyway, since you're basing these dates on some formula used, use 3 different running totals and in the evaluate->Use a formula place the criteria for each, and select group by the complaint.

If you need further assistance, please post your formula.

-k

 

[micro2005]

Here are the details of my three formula.
@filedate
if {complaintevents.eventsubtype} = 9241.00 and
{complaintevents.eventtype} = 100.00
then {complaintevents.startdate}

@ivestigationreqdate
if {event_462_view.keyname} = "f_investigationStart"
then {complaintevents.startdate}

@hearingdate
if {event_462_view.keyname} = "f_hearingStart"
then {complaintevents.startdate}

I tried your suggestion of adding these formulas in the group footer but that didnt work. I appreciate your help.

 

[synapsevampire]

The formulas wouldn't go in the group footer, they would go in the details and then be referenced at the group footer:

Example would be:

Group header (used to reset it)
whileprintingrecords;
datevar Filedate:=cdate(0,0,0)

@filedate
whileprintingrecords;
datevar Filedate;
if {complaintevents.eventsubtype} = 9241.00 and
{complaintevents.eventtype} = 100.00 then
Filedate:={complaintevents.startdate}

Now in the group footer use:

whileprintingrecords;
datevar Filedate

Another means is to use Running Totals:

In the evaluate use a formula for the FileDate RT place:

{complaintevents.eventsubtype} = 9241.00 and
{complaintevents.eventtype} = 100.00

Group by the complaint and place the RT in the group footer.

Either method works.

-k

 

[micro2005]

I have tried both the approaches but dont get any results. Using the first approach when I right click and go to browse field data, it is blank for all three formulas.

Using the second approach:
Field to summarize = {complaintevents.startdate}
Type of summary = maximum

Evaluate use formula
{complaintevents.eventsubtype} = 9241.00 and
{complaintevents.eventtype} = 100.00

Grouped by the complaint and placed the RT in the group footer, it is also blank.

The {complaintevents.startdate} is a datetime field. Does that make any difference? I appreciate your patience and help.

 

[synapsevampire]

Not sure why you are right clicking anything, just set up the 3 formula method as described, it should work fine.

You'll only get dates for those that match the criteria.

You'll need to build out formulas for each of your 3 scenarios.

-k

 

[micro2005]

The reason why I right clicked was because I was not getting any dates for any cases. I know for sure that most of these cases have file date. If I try my regular formula and place it in the details section most the cases come back with file date. Any other ideas?

 

[synapsevampire]

Place my details formula alongside, you should also get the same date as your formula as yours picks it up, however it should remain the same date throughout the details for that group afterwards.

Then the group footer should have that date passed on to it's formula.

-k

 

[micro2005]

Yes, you are right. It is working now. I dont know what made it work but hey its working. Thank you for all your help. I can always count on your great expertise.