GROUP HEADER - FORMULA SORT

Hi - Crystal Reports 2011 - ODBC connection to Pervasive DB

 I've written a production report to list all arrivals and all departure for a specific production date.

 The user selects the nominated Production Date and then runs the report.
 e.g. Record selector formula

 {QUOTE.ARRIVAL_DATE} = {?Production date}
 OR
 {QUOTE.DEPARTURE_DATE} = {?Production date}


 Both the "ARRIVALS" and "DEPARTURES" are grouped via the following formula:-

 IF {QUOTE.ARRIVAL_DATE} = {?Production date} THEN "ARRIVALS"
 ELSE
 IF {QUOTE.DEPARTURE_DATE} = {?Production date} THEN "DEPARTURES"

 This works fine with the exception when arrival and departure dates are the same date. i.e. the vehicle is arriving and leaving on the same day

I've tried

IF {QUOTE.ARRIVAL_DATE} = {?Production date} THEN "ARRIVALS"
ELSE
IF
(
{QUOTE.ARRIVAL_DATE} = {?Production date} and
{QUOTE.DEPARTURE_DATE} = {?Production date}
)
OR
(
{QUOTE.DEPARTURE_DATE} = {?Production date}
)
THEN "DEPARTURES"

but Crystal still processes only into arrivals

How can I force Crystal to review records and reflect the record in both the arrival and departures grouping?
Murphy1992Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

James0628Commented:
CR can only include a record in one group.

 The "simplest" solution would be to include those records once for each group.  For example, have a query (eg. a stored procedure or CR Command) that reads the records where  {QUOTE.ARRIVAL_DATE} = {?Production date}, and then reads the records where {QUOTE.DEPARTURE_DATE} = {?Production date}, and use Union to combine the results.  That way, you have separate "Arrival" and "Departure" records.

 Another option would be to have the main report read the "Arrival" records and a subreport read the "Departure" records, so you have a separate query for each type of record.  But if you're doing any summaries that include both the arrival and departure records, that will complicate things.

 There are probably other ways you could handle it, but those are a couple of ideas.

 James
mlmccCommented:
Are the arrival and departure dates in the same record?
If so then you can't have it in both groups.

You could have 3 groups Arrival/Departures, Departures, Arrivals

If {QUOTE.DEPARTURE_DATE} = {?Production date} 
   AND {QUOTE.ARRIVAL_DATE} = {?Production date}
 THEN "DEPARTURES"

 ELSE If {QUOTE.DEPARTURE_DATE} = {?Production date}  THEN
      "DEPARTURES"
Else
    "Arrivals"

Open in new window


You could use a command as the data source with SQL like

SELECT {QUOTE.DEPARTURE_DATE} , rest of fields, 'A' as ArrivalDeparture
FROM QUOTE
WHERE   {QUOTE.ARRIVAL_DATE} = {?Production date}

UNION ALL

SELECT {QUOTE.ARRIVAL_DATE} , rest of fields, 'D' as ArrivalDeparture
FROM QUOTE
WHERE   {QUOTE.DEPARTURE_DATE} = {?Production date}

Open in new window


Group on the ArrivalDeparture field

mlmcc

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Murphy1992Author Commented:
Thanks for comments and suggestions.

Unfortunately the record is one in the same.

I liked the idea using the union function unfortunately I don't have access at data source level db due to it being a proprietary sys.  I could create new table within Access but probably the easiest option, in this instance, is to create the subreport.

I certainly will be able to apply this information on a couple of other reports still under construction.

Thanks for the multiple option responses too.  Great to hear peoples thoughts on alternative approaches.

Mlmcc, love the out of box thought in potentially creating third group.  Thanks for level of detail too.  Much appreciated
mlmccCommented:
You don't need database access to use a command.  When you create the report one of the options is to use a command.

mlmcc
Murphy1992Author Commented:
Oh really.  Never used it.  Will take a look.  Even better if works!
Thanks for additional info.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.