Environment: Crystal Reports 11 with Oracle database
I have a report that I am building to show all projects we have worked over the past few years. My company wants to show the Superintendent's name for the project (if it's available). I had to retrieve the values for this from a field that also contains the names of other people who worked on the project, so it's not like it is just a field called Superintendent.
FYI - I created a Formula Field that combines the first and last name into a field I've labeled as Superintendent.
There is another field called Role Code that works in tandem with the Contact field above. This field is equal to SUPT when it's a Superintendent.
My Problem:
When i run the report, it repeats the job name the same number of times as there are people who worked on the project.
Job # Name Superintendent Role Code
1 Jones Building Sam Adams SUPT
1 Jones Building Henry Franktown PM
1 Jones Building Betsy Covington OWNER
2 Invesco Stadium Pat Bowlen OWNER
2 Invesco Stadium Mike Shannahan SUPT
3 Coors Brewery Nathan Smith OWNER
4 Coors Brewery Adolph Coors PM
Here is what I want to do:
Job # Name Superintendent Role Code
1 Jones Building Sam Adams SUPT
2 Invesco Stadium Mike Shannahan SUPT
3 Coors Brewery Not Available (show N/A because I don't know who the SUPT is)
Where am I now?
I was successful in suppressing the records unless this field was equal to SUPT, but that did not deliver the results I needed. I ended up with 43 records that met this criteria versus the 900+ records that should appear.
All I want is to show the Superintendent's name if it's available while only listing the project one time. If the Superintendent's name was not available, I would like it to say Not Available as shown above.
Please help as I am stumped :) Please be gentle as I am pretty new to Crystal Reports 11
Thanks,
Jerad
IP Logged
