Crystal Reports: Place row data from grouped records into columns

Question

Okay, so my Crystal Reports datasource has rows that look like this:

|--------+----------+---------+------------+-----------+-----------+---------|
| SiteNo | SiteName | SiteMgr | ContType   | ContName  | ContState | ContZip |
|--------+----------+---------+------------+-----------+-----------+---------|
| 1262   | S. Belt  | Joe B.  | Landlord   | Mike      | CA        | 90017   |
| 1262   | S. Belt  | Joe B.  | Architect  | Paul      | TX        | 77040   |
| 1262   | S. Belt  | Joe B.  | Contractor | Chris     | AZ        | 85016   |
|--------+----------+---------+------------+-----------+-----------+---------|

There are hundreds of site numbers (SiteNo), with each site number having three rows... each record in the report needs to be formatted like this:

|------------+------------+------------+------------|
|    Site    |  Landlord  | Architect  | Contractor |
|------------+------------+------------+------------|
| 1262       | Mike       | Paul       | Chris      |
| S. Belt    | CA         | TX         | AZ         |
| Joe B.     | 90017      | 77040      | 85016      |
|------------+------------+------------+------------|

Since the first three columns (SiteNo, SiteName, SiteMgr) of the datasource are always the same for a particular site, the report is grouped by SiteNo. I have this part figured out. I placed it in the group footer. Then, the part I'm struggling with is, depending on the contact type (ContType: Landlord, Architect, or Contractor), the information needs to go in the associated column.

Not sure the best way to do this? Any help would be greatly appreciated.

Answer 1

You could achieve this by using Crystal formulas, but I think it would be easier to understand and maintain if you amended your SQL query, like so:

select SiteNo,
       max(SiteName)     SiteName,
       max(SiteMgr)      SiteMgr,
       max(case ContType
               when 'Landlord' then ContName
               else NULL
           end)          LandlordContName,
       max(case ContType
               when 'Landlord' then ContState
               else NULL
           end)          LandlordContState,
       max(case ContType
               when 'Landlord' then ContZip
               else NULL
           end)          LandlordContZip,
       max(case ContType
               when 'Architect' then ContName
               else NULL
           end)          ArchitectContName,
       max(case ContType
               when 'Architect' then ContState
               else NULL
           end)          ArchitectContState,
       max(case ContType
               when 'Architect' then ContZip
               else NULL
           end)          ArchitectContZip,
       max(case ContType
               when 'Contractor' then ContName
               else NULL
           end)          ContractorContName,
       max(case ContType
               when 'Contractor' then ContState
               else NULL
           end)          ContractorContState,
       max(case ContType
               when 'Contractor' then ContZip
               else NULL
           end)          ContractorContZip
from Contacts
group by SiteNo

Lengthen your report's detail section so that it can have three lines, then place:

• SiteNo, LandlordContName, ArchitectContName and ContractorContName on the first line;
• SiteName, LandlordContState, ArchitectContState and ContractorContState on the second line;
• SiteMgr, LandlordContZip, ArchitectContZip and ContractorContZip on the third line.

Answer 2

I think your best option, if you have the data relatively well constrained, as described in your example, is to modify your stored procedure to return the data as it needs to be displayed in the report.

For example, one option would be:

SELECT (SiteNo + CHAR(13) + SiteName + CHAR(13) + SiteMgr) AS SiteDetails ,
       (ContName + CHAR(13) + ContState + CHAR(13) + ContZip) AS LandlordDetails ,
       (SELECT ContName + CHAR(13) + 
               ContState + CHAR(13) + 
               ContZip 
          FROM Contacts 
         WHERE SiteNo = c.SiteNo and ContType = 'Architect') AS ArchitectDetails ,
       (SELECT ContName + CHAR(13) + 
               ContState + CHAR(13) + 
               ContZip 
          FROM Contacts 
         WHERE SiteNo = c.SiteNo and ContType = 'Contractor') AS ContractorDetails 
 FROM Contacts c
WHERE c.ContType = 'Landlord'

This method retrieves all of the Landlord details, then performs subqueries to retrieve the other related contacts. This is very specific to your problem and may not be the best general solution. However, I have dealt extensively (much more than I care to have) with Crystal Reports and have always found that it can be much, much easier to generate the data that you need in SQL rather than trying to bend Crystal to your will (it won't happen).