Working with database views for reporting

  • Release version: Xanadu
  • 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

    A database view in ServiceNow defines table joins specifically for reporting purposes, allowing you to combine data from multiple tables such as Incident, Metric Definition, and Metric Instance. This enables comprehensive reporting on related data fields without creating new tables. Several pre-built database views are available through the Database View and Database Views for Service Management plugins, covering most metric reporting requirements and minimizing the need for custom views.

    Show full answer Show less

    Any user with report creation privileges can use database views as a report source, though Access Control Lists (ACLs) on underlying tables and their parent tables are enforced to ensure security compliance.

    Key Features

    • Pre-installed views: Ready-to-use views address common metric reporting needs, reducing setup time.
    • ACL enforcement: Database views honor all ACLs on underlying tables, including contextual and role-based checks, ensuring secure data access.
    • Performance considerations: Performance may degrade with more tables and records; optimize by using indexed fields in 'where' clauses.
    • No licensing impact: Database views are not treated as custom tables, so they do not affect licensing.
    • Cross-application scope referencing: Tables or views from different application scopes can be referenced unless extending sysmetadata, which requires matching scopes.
    • Function fields and complex queries: You can add function fields in views to enhance display results; note that AND conditions are processed before OR conditions in queries.

    Limitations

    • Database views cannot be created on tables involved in table rotation.
    • Data within database views is read-only; editing is not supported.
    • Database view tables are excluded from FTP exports and cannot be used as data preservers in clone requests.
    • Use caution with reserved words in database views to avoid performance issues.

    Security and ACL Management

    Explicit ACLs on database view fields are not required; underlying table ACLs apply. You can enforce explicit read ACLs on views by setting the system property glide.security.expander.view.legacy to true, which is recommended for upgraded instances. Additional ACLs on views can be created and are evaluated after underlying table ACLs.

    Practical Benefits for ServiceNow Customers

    • Enables advanced reporting by joining multiple related tables without data duplication.
    • Maintains data security through consistent ACL enforcement.
    • Reduces custom development effort by leveraging pre-built views.
    • Improves report performance when designed with indexed fields and optimized query clauses.

    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, but ACLs on the underlying tables are honored. This means that the ACLs of the parent tables of those tables are also evaluated.

    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 do not need to create ACLs on fields in the view. The system honors contextual ACLs (ACLs with a condition or script) that exist on the underlying table. Non-contextual ACLs (ACLs with only role checks) are still honored just as with previous releases.

    To require explicit 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.

    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.