Creating Custom Reports with JasperSoft Studio

This guide walks you through setting up JasperSoft Studio to create custom reports against the RAC/M Identity database. Custom reports use the JasperReport format and can be integrated into the RAC/M Identity interface.

Prerequisites

• Basic knowledge of SQL and query syntax
• Access to the RAC/M Identity database (see Obtain database connection information below)

Step 1 — Install JasperSoft Studio

Download and install JasperSoft Studio from the TIBCO Community Edition page.

JasperSoft Studio is the current design tool for creating reports in the JasperReport format. It replaces the older iReport Designer.

Step 2 — Download the MS SQL Server JDBC Driver

1. Download the latest Microsoft JDBC Driver for SQL Server from the Microsoft download page.
2. Extract the archive and note the full path to the .jar file (e.g. mssql-jdbc-<version>.jre11.jar). You will need this path when configuring the driver classpath in JasperSoft Studio.

Step 3 — Obtain database connection information

On-premises clients

Gather the following information from your database administrator:

Parameter	Description
Server name	The hostname or IP address of the SQL Server instance
Port	The TCP port (default is 1433)
Database name	The name of the RAC/M database (typically RACM)
Username	A SQL Server login with read access to the database
Password	The password for the above login

Recommendation

OKIOK recommends creating a dedicated read-only database user for reporting purposes. This limits the risk of accidental data modification and follows the principle of least privilege.

Common JDBC URL examples

Standard connection with port:

jdbc:sqlserver://servername:1433;databaseName=RACM;encrypt=true;trustServerCertificate=true

Named instance (without explicit port):

jdbc:sqlserver://servername\instancename;databaseName=RACM

Named instance with explicit port:

jdbc:sqlserver://servername:1433;instanceName=MYINSTANCE;databaseName=RACM;encrypt=true;trustServerCertificate=true

SaaS clients

If you are a SaaS client hosted by OKIOK, database access must be requested through the support portal.

1. An authorized user of the OKIOK support portal (JIRA) must submit a request to be granted access to the reporting database.
2. OKIOK support will then:
   1. Request the necessary information to grant access (e.g. source IP addresses for firewall rules).
   2. Provide the connection details (server, port, database name, credentials) needed to configure JasperSoft Studio.

Step 4 — Configure a Data Adapter in JasperSoft Studio

1. In JasperSoft Studio, open the Repository Explorer panel.

2. Right-click Data Adapters and select Create Data Adapter.

3. Choose Database JDBC Connection and click Next.

4. Fill in the connection parameters:

   Field	Value
   Name	A descriptive name, e.g. RACM Production
   JDBC Driver	com.microsoft.sqlserver.jdbc.SQLServerDriver
   JDBC URL	The URL from Step 3 or provided by OKIOK
   Username	Your database username
   Password	Your database password

5. Click the Driver Classpath tab, then click Add and browse to the Microsoft JDBC driver .jar file downloaded in Step 2.

6. Click Test to verify the connection, then click Finish.

Step 5 — Create a new report

1. Select File > New > Jasper Report.
2. Choose a template or start with a blank report.
3. When prompted, select the Data Adapter created in Step 4.

Be patient

Loading the metadata from the RAC/M database may take a significant amount of time because the database contains a large number of tables and relations. This is normal — wait for the process to complete before proceeding.

4. Build the SQL query that retrieves the data needed for your report. You can use the query editor or type the SQL directly.
5. Map the query result fields to the report and design your layout using bands, text fields, charts, and other components.
6. Preview the report by clicking the Preview tab to verify the output.

Advanced tips — Using subreports

For complex reporting needs, consider using subreports. OKIOK frequently uses subreports to build advanced reports that pull data from different queries or tables.

Subreports allow you to:

• Combine data from multiple SQL queries in a single report
• Reuse common report sections across different reports
• Keep individual report components manageable

To add a subreport, drag a Subreport element from the palette onto your report, then configure it with its own data source and query.

See Also

• Reporting — Generating standard and custom reports in RAC/M Identity
• Objects and Database Tables — RAC/M Identity data model reference