000033281 - Guidelines for writing Report Chart SQL Queries in RSA Identity Governance & Lifecycle

Document created by RSA Customer Support Employee on Jun 15, 2016Last modified by RSA Customer Support on Jul 17, 2020
Version 3Show Document
  • View in full screen mode

Article Content

Article Number000033281
Applies ToRSA Product Set: RSA Identity Governance & Lifecycle 
RSA Version/Condition: All
 
IssueReport Charts (Reports > Charts) are used for reporting and custom dashboards in RSA Identity Governance & Lifecycle. Data for charts is gathered with SQL queries. There are a few caveats to keep in mind when writing SQL queries for Report Charts so that normally valid SQL statements will not result in errors or no data found. This RSA Knowledge Base Article contains guidelines and tips for avoiding problems when developing a Reports Chart Query.
 
ResolutionWhen creating a Report Chart, keep the following in mind:
  1. When testing the SQL query in Oracle SQL Developer or another query tool, connect to the database with user ACMDB. The RSA Identity Governance & Lifecycle report engine uses ACMDB for the database connection. To view the same information in the AVUSER schema, prefix the tables described in the RSA Identity Governance & Lifecycle Public Database Schema Reference with PV_. For example, table USER would be PV_USER.

NOTE: This is also true for Tabular Reports (Reports > Tabular).


  1. When creating/editing the query in a Report Chart, because the report engine uses the ACMDB schema, to use tables in the AVUSER schema, they need to be both prefixed with PV_ and with the AVUSER username. For example, table USER would become AVUSER.PV_USER in the query.

NOTE: This is also true for Tabular Reports (Reports > Tabular).


  1. Surround the entire SQL query with parenthesis and embed the query as a sub-select statement in the form (SELECT * FROM (your_query)). Sometimes only the outer parentheses are sufficient but the outer select allows for ORDER BY and other SQL clauses that may cause issues so it is a better practice to embed yoiur query in its own set of parentheses.

EXAMPLE: (connected as user ACMDB)

Query tested in SQL Query tool:



SELECT
    current_state   AS state,
    count(1)        AS TOTAL_COUNT
FROM
    change_request cr
WHERE
    cr.request_date_no_time >= trunc(current_date) - 30
GROUP BY
    current_state
ORDER BY current_state



Query reformatted for a Report Chart Query (Reports > Charts > {Chart name} > Edit > Query tab)



(SELECT * FROM
  (
  SELECT
    current_state   AS state,
    count(1)        AS TOTAL_COUNT
  FROM
    change_request cr
  WHERE
    cr.request_date_no_time >= trunc(current_date) - 30
  GROUP BY
    current_state
  ORDER BY current_state
  )
)


 

Attachments

    Outcomes