How to Extract SQL Query from Jaspersoft Report or Ad Hoc View

Products

• Clarity PPM On Premise

Issue/Introduction

The SQL code behind a Jaspersoft report is requested and there is a need to review the query.

How can the SQL code used by the report be accessed and viewed?

 

Environment

All Clarity versions

Cause

The stock reports are helpful, however customers may choose to further develop the reports into a customized version.

Resolution

There are 2 methods to extract queries

Method 1 - Advanced Reporting (report folder)

1. Before using this method, ensure the user has the Clarity right: Advanced Reporting - Administer
2. Go to  Home --> Advanced Reporting --> View --> Repository --> Select a report to view. For example, the report(Project Status Report List) is chosen.
3. Right click on the Report and click "Export".  It will export and display message: Export succeeded.
4. Extract the exported file.
5. Navigate to the folder: Resources/CA_PPM/reports/project_management to view the related files. The relative path will be displayed
6. Under the 'reports' folder, find the file named: main_jrxml.data
7. The file can be opened and edited in a text processing editor to view and extract the query.
   
   

Method 2  - Advanced Reporting (View Query)

1. Login to the Jaspersoft server via the URL
2. Go to Manage > Server Settings > Ad Hoc Settings
3. Checkmark 'Enable View Query in Ad Hoc Editor. Click 'Change' button to save settings.
   
   

Method 3 - Use Jaspersoft Studio

1. Create a Jaspersoft Studio connection to the Jaspersoft Server. The steps can be found in our documenation.
2. Once a connection to the server is made, expand the tree until the report folder is reached.
3. Double-click on the file: Main.jrxml
4. Once the report opens, click the icon labeled: "Dataset and Query Editor dialog".
5. The Query Editor opens with the report query.