Clarity PPM: How to Extract SQL Query from Jaspersoft Report

book

Article ID: 130566

calendar_today

Updated On:

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?

 

Cause

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

Environment

All Clarity versions

Resolution

There are 2 methods to extract the query.

Method 1 - Advanced Reporting

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: main_jrxml.data

The file can be opened and edited in a text processing editor to view and extract the query.
 

Method 2 - 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.

Attachments