Creating a view for reports

1. Click <My Profile> | Administration | Customization | Primary Entities or Secondary Entities | <Entity> | Views.
2. Click New.
   
3. Enter a view name that starts with "v" and contains a single word with no spacing. For example, vSimpleCaseView.
   
4. To make the view available when creating a new report, select Reports View.
5. Enter a short description and a translation for the view. The translation is displayed on screen when the user selects the view.
6. Enter SQL in View Script and include the relevant unique identifier fields. For more information, see Unique identifier fields.
   Copy

   CREATE VIEWvSimpleCaseView AS SELECT comp_name, comp_status, comp_type, case_description, pers_firstname, pers_lastname, case_secterr, comp_secterr, pers_secterr, comp_primaryuserid, comp_channelid, pers_primaryuserid, pers_channelid, case_assigneduserid, case_channelid FROM company INNER JOIN cases ON comp_companyid = case_primarycompanyid INNER JOIN person ON case_primarypersonid = pers_personid

   The columns in the SELECT statement are available in the report. You can concatenate fields and use functions on the data to manipulate the data further.
   

   You can include derived or calculated fields in a report view. The following example creates a derived field called pers_fullname by concatenating the pers_firstname and pers_lastname fields.

   Copy

   SELECT rtrim("pers_firstname") +' '+rtrim(pers_lastname) as "pers_fullname", ...... from person;

   To control how the field appears in reports, ensure it's described in the custom_edits and custom_captions metadata tables. Create an extra field on the main table of the view. In the example above, create a new field on the person table called pers_fullname. This field doesn't hold data, but creating the field with the same name as your derived field creates the necessary metadata. You can then use the metadata to control the field's display properties and UI captions.
   

7. Click Save. The view is available to use in a report. For more information see Creating a report.