Data on one line

[postmanplod]

Hey folks,

Our ERP systems uses Crystal Report X1 R2 and I am wanting to create a report that will show customers and contacts all on one line.

For example:

Cust Add Add2 ConName ConPhone
ABC 123 Street Bilbo Baggins 123456
ABC 123 Street Lord Vader 567890


Needs to be displayed as:

Cust Add Add2 ConName ConPhone ConName2 ConPhone2
ABC 123 Street Bilbo Baggins 123456 Lord Vader 567890


How would I do this? It would also need to take into account that some customers have no contacts and some have more than 2 - 5 or 6 so it would need to be dynamic.

I have tried in the Crosstab but this did not procude the results I was after - can it be done using a formula?

Kind Regards,

Michael

 

[hilfy]

You would have to use variables and a running total. You also would have to have a finite number of contacts that you'll process - this can't be open-ended. Based on what you've written, I would use an upper limit of 6.

1. Group by Customer.

2. Create a running total that does a distinct count of Contact Name and resets on change of the customer group. For this example, I'll call it {#ContactCount}.

3. For each contact you'll create two formulas - one for the name and the other for the phone number. Here's an example of what they look like.

StringVar name;
if OnFirstRecord or {Customer} <> previous({Customer}) then name := '';
if {#ContactCount} = 1 then name := {Contact Name};
name

Use the same format for each of the formulas, changing the value for {#ContactCount} to 2, then 3, etc. until you have all 6 done.

4. Because this uses a running total, you will have to put these formulas in the contact group FOOTER to get the layout that you're looking for.

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[postmanplod]

Hey Dell,

Thanks for the reply.

I did as you said and grouped by Customer then Contact Name, however, I'm still getting a vertical list of Contact names instead of having a heading for each.

Regards,

Michael

 

[postmanplod]

Sorry I'll rephrase that - I not get a list of the same contact. So from the example above, I would just get a list of Bilbo Baggins, I would only need it once.

Thanks again,

Michael

 

[hilfy]

You need to create the formula 6 times - one for each possible contact, changing "if {#ContactCount} = 1" for each, so, for example, the second one would be "if {#ContactCount} = 2", the third would be "if {#ContactCount} = 3" etc.

Suppress the details section. Put these formulas across in the Group Footer section.

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.