Managing SmartChart Queries

Agencies that host their own Civic Platform permit applications can manage the queries that return the information that SmartCharts format. On-premise system administrators can manage these queries by clicking the SmartChart Queries link located in the Setup portlet of Civic Platform. These queries display the SmartChart queries in Civic Platform.

A “Super Agency” does not currently exist for SmartCharts; administrators can create queries only for the agency to which their log in applies. Administrators who want to create a query for multiple agencies must create the same query using an administrator log in for each agency in which they want to create the query.

Note:

Use caution when creating queries so that you do not inadvertently grant access to sensitive data. Queries can provide access to sensitive data and can require an in-depth knowledge of the Civic Platform database. Staff who create queries should have an in-depth knowledge of SQL and a copy of the Civic Platform ERD (Entity Relationship Diagram) for the Civic Platform version that they are using.

Topics

Creating a SmartChart Query

This section describes how to create a SmartChart query.

To create a SmartChart query

  1. Access the SmartChart Queries portlet (Navigating to the Setup).

  2. Click New.

    Civic Platform displays a form beneath the query list you use to enter query information.

  3. Enter information in the following fields:

    Query Name Enter a name that describes the information the query retrieves.
    Query SQL Enter the SQL for the Query.

    The return set of the SQL statement must always have the signature <NUMBER>, <STRING>. For example, the following statement is not valid because its signature is <STRING>, <STRING>: 

    Select b1_per_id1, b1_per_id2 from b1permit where serv_prov_code = ‘$$SERV_PROV_CODE$$’

    The following correction is valid, because “count(*)” is of type <NUMBER> and b1_per_id1 is of type <STRING>:

    Select count(*), b1_per_id1 from b1permit where serv_prov_code = ‘$$SERV_PROV_CODE$$’ group by b1_per_id1

    The reason for this requirement is intuitive for the Column Bar, Line, and Pie chart types. <NUMBER> is the value for the Y axis and <STRING> is value for the X axis.

    Note that even though the speedometer and the odometer chart types only expect a single numeric value, the SQL statement still requires the <NUMBER>, <STRING> signature, for consistency. You cannot enter statements that do not have the <STRING> part of the signature. For example, the following statement does not work for an odometer chart, because the signature of the return set is just <NUMBER>:

    Select count(*) from b1permit where 
serv_prov_code = ‘$$SERV_PROV_CODE$$’

    Instead, write the statement as follows:

    Select count(*), ‘BLANK’ from b1permit where serv_prov_code = ‘$$SERV_PROV_CODE$$’

    The <STRING> (‘BLANK’ in this case) can be any value except an empty string.

    Note:

    Civic Platform provides a few useful environment variables for your Queries. Prefix the variables with “$$” and terminate them with “$$” Remember to surround variables with single-quotes (‘) if you want Civic Platform to interpret them as strings. You can use the following environment variables anywhere in your Queries:

    $$SERV_PROV_CODE$$. The Service Provider Code (Agency) of the user currently logged-in.

    $$USERID$$. The User ID of the user currently logged-in.

  4. Click Submit.

    Civic Platform saves the query and adds it to the query list. This query is now available to administrators from the Query drop-down on SmartChart forms.

Searching for a SmartChart Query

This section describes how to search for a SmartChart query.

To search for a SmartChart query

  1. Access the SmartChart Queries portlet (Navigating to the Setup).

  2. Click Search.

    Civic Platform displays the SmartChart Query search form.

  3. Edit the following search criteria as desired:

    Query Name Edit the query’s name.
    Query SQL Edit the SQL for the query.

  4. Click Submit.

    Civic Platform returns a list of SmartChart queries that match your search criteria.

Editing a SmartChart Query

This section describes how to edit a SmartChart query.

To edit a SmartChart query

  1. Access the SmartChart Queries portlet (Navigating to the Setup).

  2. Click the link in the Query Name column of the query you want to edit.

  3. Edit information in the following fields, as desired:

    Query Name Edit the query’s name.
    Query SQL Edit the SQL for the query.

  4. Click Submit.

    Civic Platform validates the Query. If the query does not execute, or does not have the correct result set signature (<NUMBER>, <STRING>), then Civic Platform displays an error message. If there is no error message, the Query becomes available to administrators on the Query drop-down on SmartChart forms.

Deleting a SmartChart Query

This section describes how to delete a SmartChart query.

To delete a SmartChart query

  1. Access the SmartChart Queries portlet (Navigating to the Setup).

  2. Select the query you want to delete.

    Use caution when selecting a query to delete; if you delete the wrong query you might need recreate the entire query.

  3. Click Delete.

    Civic Platform deletes the query and updates the query list.