I have a record set that has three 1

[redtoad]

I have a record set that has three rows per person. All of the columns and rows are the same except for the last column. The first line will have field1 in the last column, the second line will have field2 in the last column and the third line will have field3 in the last column. Additionally, if the value of field1, field2 or field3 is null, than the respective row will be supressed.

Name Number Department
John Smith 100045 Sales
John Smith 100045 Customer Support
John Smith 100045 Null (supress this row)

Any thoughts would be appreciated.

 

[SmpTraining]

You don't mention why you can't add a query excluding the null fields.

Also, how does field1, then field2, then field3 appear in column3? Is there just the one table or two? A little more explanation is required I think. Steve Phillips, Crystal Consultant

 

[redtoad]

My record set will appear like:

Name Number Field1 Feild2 Field3
John Smith 100045 Sales Customer
Support

I need the report to appear like:

Name Number Department
John Smith 100045 Sales
John Smith 100045 Customer Support


I need to show field1, field2 and field3 in one column. In this case there is no value in field3 so this row should be suppressed. I use left outer joins to include the null fields. If I use a regular join, no rows will return with a null value. I could design a query that uses variables to get around this, but the application I use does not support the use of variables in sql.

 

[redtoad]

My formatting on the above recordset was a little off, it should appear like this:

Name Number Field1 Field2 Field3
John Smith 100045 Sales Customer Support

 

[SmpTraining]

I am still a bit confused about the joins.

On the one hand you seem to be saying you have one record like:

Name: John Smith
Number: 100045
Field1: Sales
Field2: Customer Support
Field3: NULL

but then you talk about join types. Can you clarify please. Steve Phillips, Crystal Consultant

 

[redtoad]

Let me try to explain how the report is generated in the application I'm using. Disregard the joins.

I write a SQL query, and put it in the client's version of my company's application. The query can consist of the standard keywords, such as selects, joins, creating temp tables, etc. Our report engine does not support certain words or characters, such as inserts, deletes or "@" for declaring variables. So the way I've written the query and resulting output is probably not going to change that much.

The report engine then spits out a recordset that looks similar to an excel file. Then we use this data to generate the crystal report.

Our application has rendered the following data (which will be used to generate the crystal report):

Name: John Smith
Number: 100045
Field1: Sales
Field2: Customer Support
Field3:

I need crystal to display a row for John Smith and each of field1, field2, field3. If there is a null value in field1, field2 or field3, then this row should be suppressed. So there will be a range of 0-3 rows for each person. In this case crystal would display the preceeding record set like:

John Smith 100045 Sales
John Smith 100045 Customer Support

 

[SmpTraining]

After all your typing I'd better get you the right answer!!

How about:

1. Split the details section into details a, b, c and d
2. Put the Name and Number fields into details a
3. Put Field1 into details b
4. Put Field2 into details c
5. Put Field3 into details d
6. Set 'Underlay following sections' for details a
7. Set the 'Suppress if blank' option for details b,c and d

That should do it. Your only problem will be if all three fields are blank but I don't know whether that can happen Steve Phillips, Crystal Consultant

 

[redtoad]

Thanks for your patience. I followed your advice and the only issue was the Name and Number fields did not show up on the second line.

I solved this by putting the name and number data fields in detail section a, b, c and put field1 in a, field2 in b and field3 in c. I then used conditional suppression formula to suppress b and/or c if they are null. Field1 will always have a value.

Your suggestions really put me on the correct path. Thanks!