Working with database views for reporting

  • Release version: Zurich
  • Updated August 7, 2025
  • 2 minutes to read
    • Summarize
    Summarized using AI
    This content was generated using new OpenAI-powered functionality. Results are provided on an as is basis and are not guaranteed to be accurate or complete.

    Summary of Working with database views for reporting

    Database views in ServiceNow define table joins specifically for reporting purposes, allowing users to combine data from multiple tables into a single, unified source. For example, a view can join the Incident table with Metric Definition and Metric Instance tables to facilitate comprehensive incident metrics reporting. These views simplify report creation by reducing the need to build new joins from scratch.

    Show full answer Show less

    Several pre-built database views are available through the Database View and Database Views for Service Management plugins, covering most common metric reporting requirements. Any user with report creation permissions can use database views as a data source, with access controlled by ACLs evaluated either at the view level or inherited from underlying tables.

    Key Features

    • Access Control: Database view ACLs take precedence over table-level ACLs if defined; otherwise, underlying table ACLs apply.
    • Performance Considerations: Performance impact increases with the number and size of joined tables. Optimizing 'where' clauses to use indexed fields helps maintain good performance.
    • No Licensing Impact: Database views are not treated as custom tables and do not affect licensing.
    • Data Restrictions: Data output from database views is read-only; editing data through views is not supported.
    • Application Scope: Tables or views referenced from different application scopes can be used unless they extend sysmetadata, which requires the same scope.
    • System Properties for ACLs: The glide.security.expander.view.legacy property controls whether database view ACLs or underlying table ACLs are enforced, allowing flexibility in security enforcement.
    • Reserved Words: Using MySQL reserved words in database views may cause performance issues and should be avoided.
    • Advanced Query Support: Supports joining tables into single views, adding function fields for enhanced displays, and handling complex queries with conjunction and disjunction logic.
    • Base System Views: Some database views come pre-installed with the core plugins to support common reporting scenarios.

    Limitations

    • Cannot create database views on tables involved in table rotation.
    • Data cannot be edited through database views.
    • Database view tables are excluded from FTP exports and cannot be used as data preservers in clone requests.

    Practical Implications for ServiceNow Customers

    ServiceNow customers can leverage database views to efficiently create complex reports combining multiple tables without custom development. By understanding ACL behavior and performance factors, customers can ensure secure and optimized reporting. Awareness of limitations and best practices—such as avoiding editing data through views and optimizing queries—helps maintain system stability and report accuracy. The built-in views and plugins provide a strong foundation for metric reporting, minimizing the need for custom views and accelerating reporting capabilities.

    A database view defines table joins for reporting purposes.

    For example, a database view can join the Incident table to the Metric Definition and Metric Instance tables. This view can be used to report on incident metrics and may include fields from any of these three tables.

    Several useful database views are installed with the Database View plugin and the Database Views for Service Management plugin. These database views cover most metric reporting needs and greatly reduce the need to define new ones.

    Any user who can create a report can use database views as the report source. Access control follows this hierarchy:
    1. If ACLs exist on the database view itself, those ACLs are evaluated and take precedence.
    2. If no database view ACLs exist, the system honors ACLs from the underlying tables.
    By design, database view ACLs override table-level ACLs.
    Note:
    • The accumulated impact on performance grows as the number of tables that are included in the view and the number of records that those tables contain increases. To maximize the performance of the database view, ensure that the ‘where’ clauses that are defined in the database view are based on indexed fields.
    • A database view is not treated like a custom table, so there is no licensing impact.
    • Database view tables are not included in FTP exports.
    • Database views evaluate the ACLs of the parent table of a table included in the database view.

    Limitations

    • Database views cannot be created on tables that participate in table rotation.
    • It is not possible to edit data in the database view output.
    • Database view tables cannot be added as a data preserver in clone requests.
    • You can reference a table or database view from a different application scope in a Table Name field. However, if the field belongs to a table that extends sys_metadata, the table or database view must belong to the same application scope as that table.

    ACLs and database views

    You don't need to create ACLs on fields in the view. If field-level ACLs exist, the system evaluates them.

    To require that read ACLs be added to the database views, set the glide.security.expander.view.legacy property to true. On upgraded instances, add this system property and set it to true.

    When glide.security.expander.view.legacy is true, underlying table ACLs are ignored. When the property is false (the default), the underlying table ACLs are used if there are no database view ACLs.

    You can still create additional ACLs on the database views. These ACLs are evaluated last and are always honored.

    Database view reserved words

    Using the terms may cause unintended or undesirable performance. For more information, see the MySQL reserved words document.