Can I combine multiple records in a group on one line?

[elsenorjose]

I have a report that has 6 levels of grouping:

Project Type
Project Status
Project End Date
Lot #
Project Name
Task Name

The record fields are in the Project Name group footer - GF5

Is it possible to combine the data from 2 different tasks onto 1 row? We have a conditional date formula that retrieves a date from 1 table if it exists or from another if it does not:

Sample Arrival Date

If ({usrSampleTardy.TaskName} Like '*sample*' and IsNull({LOT.DATE_CREATED}))
Then {usrSampleTardy.TaskScheduledEndDateTimeUTC}
Else {LOT.DATE_CREATED}

and

Sample Approval Date

If ({usrSampleTardy.TaskName} Like '*qc*' and IsNull({LOT_SAMPLING_POINT.DISPOSITION_DATE}))
Then {usrSampleTardy.TaskScheduledEndDateTimeUTC}
Else {LOT_SAMPLING_POINT.DISPOSITION_DATE}

The problem is that only the data for the 'sample' task is being retrieved even when I have both a sample and qc task. I believe this is due to the fact that I have the data in a group footer and the last task type is retrieved, i.e.; 'sample'. Is there anything I can do so I get both dates to display? Basically, I need every record to have a date for sample arrival and approval depending on the task and whether we have a date from the LOT or LOT_SAMPLING_POINT tables. The users do not want to see multiple rows per project which is why we have the data grouped and not in the details section.

I'm using CRXI on Windows XP on a SQL Server 2005 backend.

 

[Madawc]

You could create a pair of running totals, each with a selection formula, one for sample and one for qc task. You'd probably need to use 'Maximum' for both. Reset for change of grou.

Note that if a running totals finds no suitable records it ends up as a null. Your display might need to use a formula field that would test for nulls and say "Nothing found", or else the date turned into a string using ToTask.

The use of Crystal's automated totals is outlined at FAQ767-6524.

Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[lbass]

If there is only one date per formula per project name, then you should be able to insert a maximum on each formula at the project name level.

-LB

 

[elsenorjose]

Sorry for the late reply. I've been on holiday.

Madawc, what would I create the running total on? I need to display a date based on certain conditions. How does a running total come into play?

LB, there can be more than one date, as in my formula. If the date in the LOT or LOT_SAMPLING_POINT tables is null, then we use the scheduled end date but if there are dates in LOT or LOT_SAMPLING_POINT, we use those. Basically, the scheduled date is just that, scheduled. Once the task is complete, the other tables get updated so we still have a scheduled date as well as the LOT or LOT_SAMPLING_POINT dates.

Thanks for your help.

 

[Madawc]

A running total can be set on any field. If you use the automatic option, accessed by right-clicking on the field or else developing it from the Field Explorer, you can set a formula condition for adding.

Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[elsenorjose]

Thanks Madawc but I'm still not clear on what I'm totaling. I need to retrieve a date based on a certain condition. Where does totaling come into play?

Thanks again for your assistance.

 

[Madawc]

Running totals can give you a Maximum. Try writing an experimental report, just to check what they can do.

Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[lbass]

I think you misunderstood me. For a partiular formula, if there is only one formula result that meets your criterion per project name, then you can then insert a maximum on each formula at the project name result.

If this isn't the case please show some sample data.

-LB

 

[elsenorjose]

Hi guys, thanks again for your ongoing help.

I'm a little confused about the suggested solution of using a maximum. In response to your last post LB, for both Sample Arrival and Sample Approval, I can have 2 dates present, the Scheduled date and the actual date. If there is a value for the actual dates (from LOT or LOT_SAMPLING_POINT), I want to display that, otherwise display the scheduled date. The problem I'm encountering is that when I put both formulas in the group footer for the project, I only get back the results for Sample Arrival but not Sample Approval. When I put the task field in the footer for testing, it only shows the 'sample' task even though there is also a 'QC' task. I need both to display.

 

[lbass]

Place the formulas in the detail section and insert maximums on them at the group level.

-LB