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

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


Article ID: 130566


Updated On:


Clarity PPM On Premise


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?



All Clarity versions


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


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. I

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:

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 here.

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.