Sooner or later we need to add a field to a Cyrious Report. This is done by going to the crystal_guide_ch_2-_using_crystal_reports_with_cyrious_programs – the ,rpt document pointed to in the Reporting Setup for that Report. Earlier versions of Cyrious Templates used the manual table linking provided by the Crystal Database Manager. Since the entire table was brought into the Template, additional fields usually meant adding a table which was relatively easy if you knew what table contained the information and the appropriate link to an existing table in the Report. To improve response times, Cyrious then switched to passing a Sequel Query to Crystal; so the only fields available are those expressly “Selected” in the Query. In the Crystal Database Manager, this approach results in what appears to be a table but it a collection of fields from one or more tables.

The most efficient approach to adding fields is to add the desired fields to the existing “Command”. Fortunately, Crystal has a relatively easy way to do this–if you know the Table names, the field names and the linked fields which join the tables. If not, this WIKI has a listing of the database_table_structure and Fields. [For another example, see Lori's explanation in the Discussion Forum of how to add a vendor ##.]

Add fields to an existing Sequel Command, This How To assumes that you have a copy of Crystal Reports and have created a how_to_create_an_odbc_linksocket for access.

Sequel language is not intuitive and it is very easy for a novice to do something that renders the Query unusable. It is highly recommended that you Clone an existing report and copy the known good code to some text-based program before making any alterations. If you mess up, it is then very easy to paste the good code back in and start over.

  1. If you are modifying a Report written by Cyrious for the first time, you will need to establish an accessible Crystal template. This is done by Previewing the Report you are modifying in Cyrious and saving the report in Crystal format, usually to the Report Export folder. You can then open the report in Crystal which you have saved in the Report Export folder and access the “Command” which contains the Sequel Query, as described below. When you have tested your report using Crystals Report Preview and saved it, you will then need to go to Reporting Setup and load your modified report in the file selection in lieu of the original Cyrious report.
  2. Determine the table which contains the field you need. There is a listing of the database_table_structure and their fields as a part of the documentation on this WIKI. A shortcut is to bring the table into the Database Expert and expand it to be sure of the exact field names. . If you close the Database Expert window with this additional“ floating” table, Crystal will tell you that this is not recommended but you can still test and preview with it there. Just be sure to remove it when you have completed your modifications as it degrades performance considerably. For the purposes of this explanation, we have selected a Report “Active Employee Listing.”
  3. Open Crystal's Database Manager. You should see a screen with “Available Data Sources” column on the left and “Selected Tables” on the right. You should see one entry titled ActiveEmployeeListing.

  1. To add a field, you want to Edit the Command. Right click on that table and you will have an option to Edit or View the Query. There will be a box on the left with the caption: “Enter SQL Query in the box below. In that box you will see the Query shown in the Screen Shot and the code field below.

  1. SELECT Store.StoreNumber, Employee.FirstName, Employee.LastName, Employee.IsActive, Store.TagLine, EmployeePhoneNumber.PhoneNumber, EmployeePhoneNumber.FormattedText, Employee.Birthdate, Employee.HireDate, Employee.Position, Employee.Department, Employee.EmployeeStatus, Employee.IsSalesperson, Employee.ID, Employee.ClassTypeID, EmployeeAddress.FormattedText as Address
     FROM   ((dbo.Store Store LEFT OUTER JOIN dbo.Employee Employee ON Store.ID=Employee.StoreID) LEFT OUTER JOIN dbo.Address EmployeeAddress ON Employee.AddressID=EmployeeAddress.ID) LEFT OUTER JOIN dbo.PhoneNumber EmployeePhoneNumber ON Employee.MainPhoneNumberID=EmployeePhoneNumber.ID
     WHERE  Employee.IsActive=1
  2. The names following the Select Command are the fields which will be available in your report. The names following the FROM command are the tables which are included in the report. There is a main table “Store” and then 2 tables have been joined to it – Employee and Address. Sequel lets you rename a table on the fly in the query by following the table name with the “alias” name. So in this example, the table Address will appear in the report as EmployeeAddress. To add a table you must know the links. In this example, the Employee Table and EmployeeAddress IAddress) table are linked between the Employee.AddressID field and the EmployeeAddress.ID field by the convention Employee.AddressID = EmplyeeAddress,ID. If you were doing in this in the Crystal Links page you would drag a line between these two fields, click on Link Options and select Left Join.
  3. If you are are Sequel novices like us, we recommend formatting the list in the Edit box as shown below to help see what you are doing and to find mistakes, particularly if you are adding additional tables and multiple fields. Each field is a line as are the Join and WHERE (Search Criteria) phrases. You will note that the fields are delimited with commas between them –EXCEPT FOR THE LAST FIELD. You can study these queries to see where commas are required and where not. One missing or wrongly placed comma and the query will fail. Crystal will give you a message like “Failed to Retrieve Data” and you will have a blank page.
  4. SELECT
     Store.StoreNumber,
     Employee.FirstName,
     Employee.LastName, 
    Employee.IsActive, 
    Store.TagLine, 
    EmployeePhoneNumber.PhoneNumber,
     EmployeePhoneNumber.FormattedText,
     Employee.Birthdate, Employee.HireDate,
     Employee.Position, Employee.Department,
     Employee.EmployeeStatus,
     Employee.IsSalesperson,
     Employee.ID, Employee.ClassTypeID,
     EmployeeAddress.FormattedText as Address
     FROM   ((dbo.Store Store 
    LEFT OUTER JOIN dbo.Employee Employee ON Store.ID=Employee.StoreID) 
    LEFT OUTER JOIN dbo.Address EmployeeAddress ON Employee.AddressID=EmployeeAddress.ID) 
    LEFT OUTER JOIN dbo.PhoneNumber EmployeePhoneNumber ON Employee.MainPhoneNumberID=EmployeePhoneNumber.ID
     WHERE  Employee.IsActive=1
  5. For this example, we want to add the field Title. Create a line between any two fields and enter the field. If the table is already there you can usually add just the field name – in this case Title followed by a comma. Since you are going to drag this later from a list in the Crystal Field Explorer, it does not matter what order the fields are in. The safest way to add the field, however, is to use its full name – e.g. Employee.Title.
SELECT
 Store.StoreNumber,
 Employee.FirstName,
 Employee.LastName, 
Employee.IsActive, 
Store.TagLine, 
EmployeePhoneNumber.PhoneNumber,
 EmployeePhoneNumber.FormattedText,
 Employee.Birthdate, Employee.HireDate,
 Employee.Position, Employee.Department,
 Employee.EmployeeStatus,
 Employee.IsSalesperson,
Title,
--alternative is Employee.Title
 Employee.ID, Employee.ClassTypeID,
 EmployeeAddress.FormattedText as Address
 FROM   ((dbo.Store Store 
LEFT OUTER JOIN dbo.Employee Employee ON Store.ID=Employee.StoreID) 
LEFT OUTER JOIN dbo.Address EmployeeAddress ON Employee.AddressID=EmployeeAddress.ID) 
LEFT OUTER JOIN dbo.PhoneNumber EmployeePhoneNumber ON Employee.MainPhoneNumberID=EmployeePhoneNumber.ID
 WHERE  Employee.IsActive=1
  1. Return to your report and you should see the field in the Field Explorer. Drag it where you want it in your report.
  2. Format the field using the Crystal formatting options. Fields in their native form usually will need to be formatted to look right in the report, particularly dates and numbers. Crystal provides a myriad of options which are accessed by right clicking on the field and selecting “Format Field” from the list presented. Click on the one right for your report. Below is a Screen Shot of the options for formatting a date field. .

  1. If the field you need is not in one of the tables already called in the report, you can add the table using the conventions and language shown in this example with the exception that the dbo designation shown in the queries above is not necessary.

Contributor: Steve Gillispie, Acorn Sig Graphics)

Date: 04/18/2011

Version: Control 4.5

You could leave a comment if you were logged in.