Use variables in reports

Variables are raw database scripts that you execute directly against the Civic Platform Oracle or MS SQL Server database, outside of a report writer or a development tool. You can incorporate these scripts into a report or report template as parameters and populate them with data from Civic Platform. For example, to extract the full name of the primary owner, use the database script displayed below in the Report variable form.

This tool provides a wide variety of reporting possibilities and is only available to super administrative users with a working knowledge of database scripts. Complicated queries that return large amounts of data effect the performance of the database and slow the responsiveness of the application. For information on how to become a super administrator, contact your Accela implementation specialist.

Report Manager includes a set of standard out-of-the-box session variables. This set of variables include frequently used parameters from Civic Platform such as Department, Module, or Service provider code, Today. For more information and a list of these session variables, see .

Add a variable

You must be a super administrator user to add variables to Report Manager. If you do not have access, contact your Accela Implementation Specialist to change your permissions.

Variables are raw database scripts that you execute directly against an Oracle or MS SQL Server database outside of a report writer or a development tool. You use variables as report parameters to populate specific data in RTF template type reports developed using Microsoft Word. You can also use variables as parameters in a URL string for a URL report. For example, to extract the primary owner full name, use the database script in the report variable form. For instructions on adding this variable to an RTF template, see Use rich text format templates or Use URL reports.

Civic Platform includes a set of frequently used runtime variables as a standard, out-of-the-box configuration. You can use runtime variables in the WHERE clause of an SQL statement. You must surround runtime variables by “$$” signs and must enclose them in single quotes for Civic Platform to return the values as a string. For example, to retrieve the agency code for the logged in user, use $$ServiceProviderCode$$. See the image below for an example of how to use the session variable within an SQL script. For a list of the standard runtime variables, see Use variables in reports.

Best practice recommendation

It is best practice to test your variables and your scripts in a test environment before running them against a production database or making them available to reports.

This feature provides a way to run queries and simple scripts to return small amounts of data. Do not use this tool to run complicated queries or scripts that return large amounts of data. Running these types of queries against a production database can negatively affect the database and degrade application performance, which causes pages and lists to load slowly or become unresponsive. It is also possible to lock the database, which prevents you from saving records and causes applications to stop responding.

A best practice recommendation is to add security parameters to these variables. You might have users that do not have permission to view sensitive information, so it is important to write scripts that filter sensitive data according to your agency’s best practices. For example, agencies that share a database must add the $$ServiceProviderCode$$ parameter to display only records created by their agency.

To add a variable

  1. From the Administration menu, select Report > Report manager.

  2. Expand Settings in the navigation tree.

  3. Right-click Variables and select New variable.

  4. Complete the fields.

    Field name Field description
    Name Enter the name to describe the variable. For example, you might name a script that returns the primary owner first and last name, “Primary Owner Full Name.”
    SQL Enter the database script into the SQL window. Make sure to test the script against the database first. When you filter data, you can use standard session variables or hard code the value. You can specify multiple columns in the Select statement as the value of the variable.

    If the variable populates a list parameter and its Select statement contains multiple columns, the list parameter only reads the first column value from the Select statement.

  5. Select Submit to save the variable.

Edit a variable

Editing a variable might be necessary if a query fails against the database or you need to update it for any reason. Any changes to variables results reflect in the reports using the variable. It is important to keep a list of all variables used in each report and review them before making any changes.

To edit a variable

  1. From the Administration menu, select Report > Report manager.

  2. Expand Settings in the navigation tree.

  3. Expand Variables in the navigation tree.

  4. Double-click the variable you want to edit.

  5. Edit the applicable fields.

    Field name Field description
    Name Update the name of the variable. For example, Primary Owner Full Name.
    SQL Update the database script in the SQL window. Make sure you test the script against the database first.

  6. Select Submit to update the variable.

Remove a variable

To remove a variable, you must first remove the variable from any reports that use it, specifically rich text format (RTF) template and URL reports. After you remove a variable it is no longer available for any reports.

To remove a variable

  1. From the Administration menu, select Report > Report manager.

  2. Expand Settings in the navigation tree.

  3. Expand Variables in the navigation tree.

  4. Right-click the variable you want to remove and select Remove variable.

Report manager variable parameters table

Variable Name Description
$$ID$$ ID1 Retrieves the record ID from a specific portlet. This variable works only for portlet-specific reports.
$$ID$$ ID2 Retrieves a second record ID from a specific portlet. This variable works only for portlet-specific reports.
$$capID1$$ CAPID1 Retrieves the first 5 characters of the record ID from a specific portlet. Example: CAPID = 01BLD-00000-00018
$$capID2$$ CAPID2 Retrieves the second 5 characters of the record ID from a specific portlet. Example: CAPID = 01BLD-00000-00018
$$capID3$$ CAPID3 Retrieves the last 5 characters of the record ID from a specific portlet. Example: CAPID = 01BLD-00000-00018
$$department$$ Department Retrieves the department of the logged-in user.
$$gaUserID$$ GAUserID Retrieves the ID of the logged-in user.
$$module$$ Module Retrieves the module from a specific portlet. For example, if the report is available in the Building list portlet, Civic Platform sends the value “building” to the report. This variable works only for portlet-specific reports.
$$serviceProvCode$$ ServiceProviderCode Retrieves the ID for the agency. You can use this to filter reports to include records only for the specified agency. It is a required parameter for many reports and agencies that share data.
$$FirstName$$ FirstName Retrieves the user’s first name.
$$MiddleName$$ MiddleName Retrieves the user’s middle name.
$$LastName$$ LastName Retrieves the user’s last name.
$$userfullname$$ UserFullName Retrieves the user’s full name.
$$userGroup$$ UserGroup Retrieves the group of the logged-in user.
$$userID$$ UserID Retrieves the ID of the logged-in user.
$$today$$ Today Retrieves today’s date. Civic Platform determines this date by the workstation's system date. You can use this variable parameter in calculations to specify a date range. For example, enter $Today$ - 7 to query for all records over the last week.
$$portletID$$ Portlet ID Retrieves information on a portlet, such as a field name.