Jaspersoft Studio can easily be used to create reports based on data in a MS SQL Server database.
Jaspersoft Studio comes with a bundled TIBCO SQL Server JDBC driver but the following steps are instructions for using a native SQL Server driver.
Before proceeding, check the "Jaspersoft Supported Platforms Guide" of the relevant JasperReports Server version for the supported versions of MS SQL Server. For JasperReports Server 8.2.0, the supported database versions are MS SQL Server 2016, 2017, 2019.
- Make sure you have a reasonably current version of Java installed. ( http://www.java.com )
- Download and install the JDBC driver for MSSQL ( http://msdn.microsoft.com/en-us/sqlserver/aa937724.aspx ) . Check the driver compatibility at Microsoft JDBC Driver for SQL Server support matrix
- In Jaspersoft Studio, navigate to "Repository Explorer" and "Data Adapters"
- Select "Create Data Adapter"
- Select "Database JDBC Connection"
- Select "MS SQLServer (2005-2012)(com.microsoft.sqlserver.jdbc.SQLServerDriver)" for the JDBC Driver
- Enter "jdbc:sqlserver://MySQLServerAddress:1433;databaseName=MyDatabase" for the JDBC Url
- Enter SQL username and password
- On "Driver Classpath" tab click "Add" and enter the location of the installed JDBC driver from step 2. Example: "C:Program FilesMicrosoft JDBC Driver 4.0 for SQL Serversqljdbc_4.0enusqljdbc4.jar"
- Click "Test" to test the connection.
- Once the connection is tested successfully, click "Finish"
- Create Report
- Click on "Project Explorer" tab and then right-click on "MyReports", then New, "Jasper Report"
- Enter a File name for the report, then click Next
- Select the Data Adapter that you created above
- Enter a query, then click Next, such as:
SELECT * FROM myOrders a JOIN myOrderDetails b ON a.OrderID = b.OrderID;
- Follow remaining screens on the new report wizard
- Drag and drop fields from the Outline pane onto the Main Report pane
- Click on "Preview"
Note that calling stored procedures using standard EXEC myProcedure syntax works just fine in the query, as does including multiple statements (assuming each statement is terminated by a semicolon). All statements are executed against the same SQL connection.
Recommended Comments
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now