Listing Ad Hoc Reports and Domains via Database Query

Products

• Clarity PPM On Premise

Issue/Introduction

This article outlines how to obtain a comprehensive list of all ad hoc reports and their corresponding JasperSoft domains by querying the database.

Environment

Clarity 16.3.2

Resolution

While a pre-built, ready-to-use query is not available, you can retrieve ad hoc view/report and domain information directly from the JasperReports Server (JRS) database by following these steps:

To obtain a list of all domain-based ad hoc view reports from your JRS deployment using a SQL query, you will need to:

1. Identify Domain Resources: Locate all domain resources within the jiresource table where the resourcetype column is equal to 'com.jaspersoft.commons.semantic.datasource.SemanticLayerDataSource'.
2. Identify Ad Hoc Views: Find all ad hoc views within the jiadhocdataview table that use a domain as their report data source. This is achieved by matching jiadhocdataview.reportdatasource with jiresource.id.
3. Identify Reports from Ad Hoc Views: Identify all reports within the jireportunit table that utilize an ad hoc view as their report data source. This involves matching jireportunit.reportdatasource with jiadhocdataview.id

Subsequently, perform inner joins across these identified tables to retrieve the complete list of domain-based ad hoc view reports.