cancel
Showing results for 
Search instead for 
Did you mean: 

Additional column required in report - data in new column dependent on calculation on data in existing column

Former Member
0 Kudos

Post Author: dsloan

CA Forum: Formula

Hope this makes sense.

We have a Crystal Report with the following columns: -

Customer ID Service Start date of service End date of service

Report selection is set up so that Service only displays Service A, Service B, and Service C, but not Service Z, so we have the following: -

Customer ID Service Start date of service End date of service01 Service A 01/01/07 03/01/0702 Service B 13/02/07 27/02/0703 Service B 14/04/07 21/05/0704 Service C 19/03/07 23/03/07

Although Service Z is not displayed, Service Z does have have a start date and end date.

What we would like to create an additional column in the report that indicates (Y/N) if the following applies: -

Did customer have an instance of Service Z occuring before an instance of Service A, Service B, or Service C, the end date of Service Z occuring 28 days or less before the start of the instance of Service A, Service B, or Service C.

i.e.

Customer ID Service Start date of service End date of service Did Customer get service Z 28 days or less prior to A/B/C?01 Service A 01/01/07 03/01/07 Y (as customer received Service Z from 21/12/06 to 25/12/06)02 Service B 13/02/07 27/02/07 N03 Service B 14/04/07 21/05/07 N04 Service C 19/03/07 23/03/07 N

In anticipation, thank you for your help.

David and Monica

Accepted Solutions (0)

Answers (2)

Answers (2)

Former Member
0 Kudos

Post Author: dsloan

CA Forum: Formula

Hi yangster.

Yes, just trying to get additional column that states Y or No - detail not required.

Using Crystal 10 and MS SQL Server database.

Yes, service Z would have the same customer ID - all records for service A, B, C and Z exist in the one view of the data and all have the same fields.

Regards,

David and Monica

Former Member
0 Kudos

Post Author: yangster

CA Forum: Formula

Are you trying to get just the column that states Y or No or are you after the details you also wrote next to it about the beginning and ending date?What version of crystal are you running and off of what db?And i'm assuming that a service Z would have the same customer id and that's how you are linking the 2 to see if the criteria is met?