Crystal Reports are the main reporting tool used in CMS. This programme is an industry standard coming free with applications like ArcGIS and MapInfo. Often organisations have people skilled in writing Crystal Reports meaning you can write your own reports at any time.

One failing however of Crystal is that it does not render all rich text format (rtf) tags and CMS uses these extensively in our memo fields. Rtf tags allow you to format your text like in Word. It allows the use of different fonts, font sizes, bold, italics, bullet points, tables and many other features. However, not all rtf tags are supported by Crystal and the two problematic ones are bullet points and tables.

If you insert a bullet point in CMS's text editor, it uses the tag " \pntext" but this is not handled by Crystal. The two alternatives to get bullet points working are to copy and paste them in from Word or instead of the CMS Editor bullet point tool to use the ALT+0149 key. To use this, type 0149 while holding the ALT key down.

Tables are the most problematic as Crystal just doesn't handle them and outputs each cell in a different row. We recommend that in Word, you convert your table to text separating the text with tabs. In this way, your table will appear correctly in the Crystal report, just without the cell borders. Not ideal but until Crystal is improved, we have no choice.

If you need to check if you have used tables anywhere in CMS, if you paste the code below into the SQL Scratchpad in System Manager - Utilities, this will identify any tables used in memo fields. NOTE: Be sure to copy all the text below in blue.

SELECT "Sites" as [Table], "Site Description"  as [Field], Sites.SiteName as [Details]
FROM Sites INNER JOIN SitesExtra ON (Sites.SiteCode = SitesExtra.SiteCode) AND (Sites.SiteCode = SitesExtra.SiteCode)
WHERE (((SitesExtra.Description) Like "*\trowd*"));

UNION SELECT "Site Description" as [Table], "Heading Code" as [Field], Sites.SiteName & ": " & [PlanText].[HeadingCode] AS [Details]
FROM Sites INNER JOIN PlanText ON Sites.SiteCode = PlanText.SiteCode
WHERE (((PlanText.Text) Like "*\trowd*"));

UNION SELECT "Features" as [Table], "Feature Description" as [Field], Sites.SiteName & ": " & [features].[featurenum] & ": " & [features].[featurename] AS [Details]
FROM Sites INNER JOIN Features ON Sites.SiteCode = Features.SiteCode
WHERE (((Features.FeatureDescription) Like "*\trowd*"));

UNION SELECT "Objectives" as [Table], "Vision" as [Field], Sites.SiteName & ": " & [features].[featurenum] & ": " & [features].[featurename] AS [Details]
FROM Sites INNER JOIN (Features INNER JOIN Objectives ON (Features.SiteCode = Objectives.SiteCode) AND (Features.ObjectiveNum = Objectives.ObjectiveNumber)) ON Sites.SiteCode = Features.SiteCode
WHERE (((Objectives.Description) Like "*\trowd*"));

UNION SELECT "ObjectiveStatus" as [Table], "Condition Details" as [Field], Sites.SiteName & ": " & [features].[featurenum] & ": " & [features].[featurename] AS [Details]
FROM Sites INNER JOIN ((Features INNER JOIN Objectives ON (Features.SiteCode = Objectives.SiteCode) AND (Features.ObjectiveNum = Objectives.ObjectiveNumber)) INNER JOIN ObjectiveStatus ON (Objectives.ObjectiveNumber = ObjectiveStatus.ObjectiveNum) AND (Objectives.SiteCode = ObjectiveStatus.SiteCode)) ON Sites.SiteCode = Features.SiteCode
WHERE (((ObjectiveStatus.CurrentCondition) Like "*\trowd*"));

UNION SELECT "Project Plans" as [Table],"Project Description" as [Field],  [sites].[sitename] & ": " & [projectplans].[projectcode] & "/" & [projectplans].[projectnumber] AS [Details]
FROM Sites INNER JOIN ProjectPlans ON Sites.SiteCode = ProjectPlans.SiteCode
WHERE (((ProjectPlans.ProjectDescription) Like "*\trowd*"));

UNION SELECT "Annual Projects" as [Table],"Annual Project Summary" as [Field],  [sites].[sitename] & ": " & [projectplans].[projectcode] & "/" & [projectplans].[projectnumber] & " - " & [annualprojects].[financialyearstart] AS [Details]
FROM (Sites INNER JOIN ProjectPlans ON Sites.SiteCode = ProjectPlans.SiteCode) INNER JOIN AnnualProjects ON (ProjectPlans.ProjectNumber = AnnualProjects.ProjectNumber) AND (ProjectPlans.ProjectCode = AnnualProjects.ProjectCode) AND (ProjectPlans.SiteCode = AnnualProjects.SiteCode) AND (ProjectPlans.ProjectNumber = AnnualProjects.ProjectNumber) AND (ProjectPlans.ProjectCode = AnnualProjects.ProjectCode) AND (ProjectPlans.SiteCode = AnnualProjects.SiteCode)
WHERE (((AnnualProjects.AnnualSummary) Like "*\trowd*"));

UNION SELECT "Annual Projects" as [Table],"Annual Project Report" as [Field],  [sites].[sitename] & ": " & [projectplans].[projectcode] & "/" & [projectplans].[projectnumber] & " - " & [annualprojects].[financialyearstart] AS [Details]
FROM (Sites INNER JOIN ProjectPlans ON Sites.SiteCode = ProjectPlans.SiteCode) INNER JOIN AnnualProjects ON (ProjectPlans.ProjectNumber = AnnualProjects.ProjectNumber) AND (ProjectPlans.ProjectCode = AnnualProjects.ProjectCode) AND (ProjectPlans.SiteCode = AnnualProjects.SiteCode) AND (ProjectPlans.ProjectNumber = AnnualProjects.ProjectNumber) AND (ProjectPlans.ProjectCode = AnnualProjects.ProjectCode) AND (ProjectPlans.SiteCode = AnnualProjects.SiteCode)
WHERE (((AnnualProjects.AnnualReport) Like "*\trowd*"));

UNION SELECT "Annual Projects" as [Table],"Work Programme" as [Field],  [sites].[sitename] & ": " & [projectplans].[projectcode] & "/" & [projectplans].[projectnumber] & " - " & [annualprojects].[financialyearstart] AS [Details]
FROM (Sites INNER JOIN ProjectPlans ON Sites.SiteCode = ProjectPlans.SiteCode) INNER JOIN AnnualProjects ON (ProjectPlans.ProjectNumber = AnnualProjects.ProjectNumber) AND (ProjectPlans.ProjectCode = AnnualProjects.ProjectCode) AND (ProjectPlans.SiteCode = AnnualProjects.SiteCode) AND (ProjectPlans.ProjectNumber = AnnualProjects.ProjectNumber) AND (ProjectPlans.ProjectCode = AnnualProjects.ProjectCode) AND (ProjectPlans.SiteCode = AnnualProjects.SiteCode)
WHERE (((AnnualProjects.WorkProgramme) Like "*\trowd*"));