Use rich text format templates

Agencies can create frequently used letters and forms that use runtime variables to populate data from the Civic Platform database. Variables can be standard session variables such as today’s date and your agency name. Variables can also be custom SQL queries that extract specific data. For example, a warning letter to a resident about specific code violations can populate with the current date and your agency name using session variables. You can use custom variables for the agency contact information, owner contact information as well as the code violation case # and description. The letter can also include an image, such as the agency’s logo.

You can develop letters and forms using Microsoft Word templates that you save as rich text format (RTF) files. You add these files to Report manager; they are accessible from the Reports page or portlet-specific reports menus. You can also save these files to an Electronic Document Management System (EDMS). You also define the EDMS object to save the report to a record, parcel, workflow, asset condition assessment, inspection, or set. For example, you can save an approval letter to build a new structure to the building permit record. For more information and instructions, see Save reports to a document management system.

The four major steps to create rich text format (RTF) template based reports include:

Create variables to embed in the report. A variable is an SQL statement that you add directly into report manager.
Add the variables to Civic Platform using Report Manager so they are available to the RTF report at runtime.
Create the report, including variables, using a word processor to create a template that you can save as an RTF file.
Add the RTF report to Civic Platform using report manager.

Topics:

Create RTF templates
Add RTF template reports to Report Manager

Create RTF templates

Rich text format (RTF) template based reports use variables to populate data from the Civic Platform database. Variables can be standard session variables that populate generic data such as today’s date, agency name, user name, and specific module. These types of variables do not require any configuration; you can use them as report parameters.

Variables can also be raw Microsoft SQL scripts that run against the database to extract specific information. You can add these scripts to Report Manager and use them in reports, specifically RTF template reports.

An RTF report template can have multiple variables. You surround these variables by the “<$” and “$>” signs such as, <$capID$>. When the user runs a report, it replaces the defined variables with the actual value from the SQL statement. You can return variable results in different formats such as a single column and a single row, a single column and multiple rows, or multiple columns and a single row. The examples below display how you can use the different formats within an RTF template report.

Example 1. rtf in Figure 3: Single column RTF template source file is an example of an RTF template that uses a variable named <$Fees$>. At runtime, Civic Platform replaces <$Fees$> with the results of an SQL query that returns a single column and multiple records. For an example, see Figure 4: Single column RTF template output.

In Figure 4: Single column RTF template output Civic Platform replaces <$Fees$> with a list of fee values returned from executing the following SQL statement:

SELECT FEE_VALUE “Fee List” FROM FEES WHERE...

Civic Platform replaces the heading for the variable <$Fees$> with the name of the column alias “Fee List” from the SQL query. If you do not use an alias, the column name [FEE_VALUE] displays as the column label.

Example 2.rtf Figure 5: Multiple column RTF template source file is an example of an RTF template that uses a variable named <$Fees$> which has multiple columns and multiple rows.

In Figure 5: Multiple column RTF template source file, Civic Platform replaces <$Fees[0]$>, <$Fees[1]$>, and <$Fees[2]$> with a list of fee values from executing the following SQL statement:

SELECT FEE_DATE “Fee Date," FEE_TYPE “Fee Type," FEE_VALUE “Fee Value”  
FROM FEES

WHERE...

Civic Platform preserves spaces, tabs, text, or punctuation added to the template, which enables you to embed variables within the text of a report. For example, the property <$address$> in the city of <$city$> has violated Ordinance No. <$ordinanceNo$>. The result of the report at runtime reads, “The property 111 N. 500 W. in Saint Mary’s City has violated Ordinance No. 0098791.”

Create a template based report using a word processor such as Microsoft Word. After you create a template, you must add it to Report Manager with the appropriate variables and report parameters for the report to function correctly.

To create an RTF Template

Do the following to create an RTF-template-based report skeleton:
1. Open a new or existing Rich Text Format (RTF) file or Microsoft Word document.
2. Format the template and leave place holders for the fields you want populated from Civic Platform.
  
  For example, enter brackets around the fields you want to populate. Civic Platform preserves spaces, tabs, text, punctuation, and/or images.
3. Save the template on your local drive.
Create or use existing MS SQL script variables to populate the placeholders in the RTF report. Your script might require more than one variable depending on the type of data.

For example, the RTF example requires two variables. The first populates the Owner Name [Full Name], and the second populates the following fee information: [Fee Date], [Fee Type], and [Fee Amount].

The column names in the select statement of the MS SQL script must match the order in which the variables appear in the report. For example, [Fee Date], [Fee Type], and [Fee Amount] populate in this order by entering the associated column names in the order they appear in the select statement of the SQL script as illustrated here:
Do the following to set variables in the RTF template:
1. Open the template from your local drive.
2. Replace the placeholders with the name you want to use to identify the variable and enclose them in <$ and $>.
  
  For example, if the MS SQL script variable returns fee information, use <$Fees$>. If the variable returns more than one value, add a number to define the order in which it displays in the SELECT statement; start with zero (“0”). The example below uses the SQL statement from Step 2.
3. Save the template in Rich Text Format (.rtf) on your local drive.

Do the following to add the RTF report to Report Manager:

In the Setup section, select the Report item and choose Report manager from the menu.
Right-click a Reports by category option in the left navigation tree and select New report.

Complete the required fields.

Field name	Field description
Name	Enter the name of the report. The Name field is the name that appears in the Reports page or portlet-specific report menu.
Category	Select the category under which you want the report to display.
Process service	Select the RTF process service.

Civic Platform updates the Report link to Template and displays a Browse button.

Select Browse to find the report on your local drive.
Navigate to and select the report you want to add.
Select Open to open the RTF report and display the file name for reference.

Complete the required fields.

Field name	Field description
Reports portlet 1 - 5	Select each Reports Portlet on which you want to display the report. For information and instructions on the location of these portlets and how to add them, see Form designer and page layout.
Save to EDMS	Specify whether you want to save the report to EDMS. For more information, see Save reports to a document management system.

Select Submit. Civic Platform adds the report to Report Manager and displays it the report under the specified category.
To preview the report, select Preview. This preview does not populate the variables but confirms you have the correct template.

Do the following to link variable parameters to the RTF report:

Select the Parameters tab.
Select New.

Complete the required fields.

Field name	Field description
Name	Enter the name of the parameter. For example, to add a variable parameter that returns fee information, use Fees.
Type	Select the Variable parameter type.
Report parameter name	Enter the name of the variable. Use this name in the RTF template to specify the location where you want to return and populate the variable values on your report. For example, the template displayed in uses the Fees variable. This name must match the exact spelling used in the RTF template.
Visible	Select NO to send the parameters directly to the database and automatically populate the RTF report without prompting the user.
Default value	Select the SQL script variable, that returns the values for the RTF report. For example, use Fees to populate the fee information.
Display order	Enter the display order for the variable as it displays in the RTF template. The display order defines the order in which Civic Platform runs the SQL script variables and then returns values.

Select Submit to create the variable parameter and display it in the Parameters tab.

Add RTF template reports to Report Manager

After you create your RTF template report and save it to your local drive, you can add it to Report Manager. To add an RTF report you must first define the RTF report service. See for complete instructions on adding an RTF report service.

If your RTF template contains variables, you must define those variables as report parameters. For example, if the template contains the variable <$Fees$>, you must identify and define a parameter for Fees. The name of the parameter must match the exact spelling of the variable in the RTF report. If your report contains more than one variable value, you can specify the order in which you want the values to display on the report by defining the display order in the variables; start with 0 (zero). Refer to the following sample screen which illustrates Fee Date, Fee Type, and Fee Value, along with their respective sort order definitions [0], [1], and [2]:

You can save RTF template reports to an Electronic Document Management System (EDMS) allowing the user to save a snapshot of data for historical purposes. You can also save RTF template reports as specific EDMS object types such as records, parcels, workflow, asset condition assessment, inspections and sets. Templates save automatically at runtime. For more information on saving to EDMS, see Save reports to a document management system.

You can save RTF reports in multiple formats, including Microsoft Word and Adobe Acrobat (PDF). The available output options are native to the report service. After you save an RTF report a Preview button becomes enabled so you can preview the report prior to printing. To update the RTF report, you must make the changes on your local drive and upload the updated template by selecting the Browse button in the Report detail tab.

The steps below provide instruction on how to add an existing RTF report for which you previously defined the variables. For instructions on creating a new RTF template report, see Create RTF templates.

To add RTF template based reports

From the Administration menu, select Report > Report manager.
Right-click the Reports by category option in the left navigation tree and select New report.

Complete the fields.

Field name	Field description
Name	Enter the name of the report. The Name field is the name that displays in the report portlet or portlet-specific report menu.
Category	Select the category under which you want to display the report.
Report service	Select the RTF report service. If the RTF report service is not available, you must set it up.

Civic Platform updates the Report link to Template and displays a Browse button.

Select Browse to find the template report.
Navigate to and select the report you want to add.
Select Open to open the RTF report and display the file name for reference.

Complete the fields.

Field name	Field description
Reports portlet 1 - 5	Select each reports portlet where you want to display the report. For information and instructions on the location of these portlets and how to add them, see Form designer and page layout.
Save to EDMS	Specify whether you want to save the report to EDMS. For more information, see Save reports to a document management system.

Select Submit.
To preview the report before saving, select Preview.

Do the following to link the variables in the RTF template:

Select the Parameters tab.
Select New.

Complete the fields.

Field name	Field description
Name	Enter the of the parameter. Example, date, status, fees, address info etc.
Type	Select the Variable parameter type.
Report parameter name	Enter the name of the variable. Use this name in the RTF template to specify the location where you want to return and populate the variable values on your report. For example, the report parameter name for the RTF template in Figure 5: Multiple column RTF template source file is ‘Fees’.
Visible	Select NO to send the parameters directly to the database and automatically populate the RTF report without prompting the user.
Default value	Select the variable (sql script), that returns the values for the RTF report.
Display order	Enter the display order for the variable as it displays in the RTF template. The display order defines the order in which the variable values display on your report.

Select Submit to create the variable parameter and display it in the Parameters tab.

To add the RTF template report to run from a specific page, such as the Building page, select the Portlets tab.