Combining Multiple Lines of Data Into One Line

[Freefall27]

I am using Crystal XI and pulling data by ODBC connection to Oracle DB.

I have a report which displays records that need to be combined on one line.

Current Data Output:

Work Order Task Status Ready Time
WO1846 Billing Complete 8/31/06 10:58:56 A.M
WO1846 Close Complete 9/5/06 11:08:52 A.M

The ready time is the actual time that the order entered each queue. My goal is show how long the order was in billing. The billing ready time would be the start of the clock and the close ready time would be when the billing phase was actually complete and the order moved to the close queue.

Work Order Status Ready Time End Time (Time Stamp of Close Activity) End Time (Time Stamp of Close Activity)

WO1846 Complete 8/31/06 10:58:56 A.M 9/5/06 11:08:52 A.M


I am not sure how to accomplish this in CR XI and all help is greatly appreciated.

 

[synapsevampire]

Group by the workorder.

Suppress the details and the group header, and sort the rows by date descending.

In the group footer place the following alongside each other:

workorder
status

Ready time Formula:
whileprintingrecords;
if previous({table.workorder}) = {table.workorder}
and
previous({table.task}) = "Billing" then
previous({table.table.date})
else
if {table.task} = "Billing" then
{table.table.date}

End time formula:
whileprintingrecords;
if previous({table.workorder}) = {table.workorder}
and
{table.task} = "Close" then
{table.table.date}

Then go to Report->Selection Formula->Record and place:

{table.task} in ["Billing","Close"]

-k

 

[Freefall27]

I was in training for a few days and just getting back to this report. I put in the formula and recieve the error that The remaining text does not seem to be part of a formula with the second whileprintingrecords down highlighted.


//Ready time Formula:
whileprintingrecords;
if previous({TASK_VIEW.WO_NAME}) = {TASK_VIEW.WO_NAME}
and
previous({TASK_VIEW.TASK_NAME}) = "ADJUST BILLING: CARRIER" then
previous({WO_TASK_BUCKET.READY_TIME})
else
if {TASK_VIEW.TASK_NAME} = "ADJUST BILLING: CARRIER" then
{WO_TASK_BUCKET.READY_TIME}

//End time formula:
whileprintingrecords;
if previous({TASK_VIEW.WO_NAME}) = {TASK_VIEW.WO_NAME}
and
({TASK_VIEW.TASK_NAME}) = "CLOSE WORK ORDER" then
{WO_TASK_BUCKET.READY_TIME}

 

[lbass]

Those are two separate formulas.

-LB

 

[synapsevampire]

Reread the post, there are 4 fields to be placed alongside each other, two of which are formulas, and as LB suggests, you don't combine formulas.

Your post stated that it needed 4 things, right?

Hence I suggest that you have 4 things:

workorder
status

Ready time Formula:
whileprintingrecords;
if previous({table.workorder}) = {table.workorder}
and
previous({table.task}) = "Billing" then
previous({table.table.date})
else
if {table.task} = "Billing" then
{table.table.date}

End time formula:
whileprintingrecords;
if previous({table.workorder}) = {table.workorder}
and
{table.task} = "Close" then
{table.table.date}

-k

 

[Freefall27]

I separated into 2 separate formulas and constructed per your instruction. I am only getting the last date (Close Date) on the report.

Can I make the fields in the original data the headers for the output report? (Billing Adj and Close are task operations but need to be headers in output?)

CURRENT DATA:

Table.Field

TASK.WORK TASK.NAME TASK.OPER TASK.QUE TASK.ELEM WO.READY

WO10062 BILL BILLING ADJ BILL CUST PONC.UA 1/19/06 8:13:01 AM
WO10062 CLOSE CLOSE ENG PONC.UA 1/31/06 9:16:03 AM


DESIRED OUTCOME:

TASK.WORK BILLING ADJ CLOSE TASK.ELEM

WO10062 1/19/06 8:13:01 AM 1/31/06 9:16:03 AM PONC.UA


Can I include a formula to calculate date difference in days and add on the end of report? Do I have to include on summary line.

 

[Freefall27]

Just to add that the bill should always happen before the close when the woork order is entered properly. When I input the formula I only see the close date. Should this work based on your formula? maybe I am doing something wrong?

Thanks for any help.