Using JasperReports with Coldfusion MX 6.1
I recently found myself in a situation where I needed to do some report generation with Coldfusion, but more complicated than plain HTML output could really handle. CFREPORT is poorly documented and has extremely limited functionality, and we don't want to fork out a ton of cash for a web based reporting tool like Crystal Enterprise.
Enter JasperReports, a free, open-source, java-based, full-featured reporting engine with the ability to generate PDF documents, Excel Spreadsheets, HTML documents, and other output formats.
After doing some searching on the internet and posting to some mailing lists, I determined that nobody has successfully done this, or if they have, they haven't bothered to talk about it publically. So in order to share the knowledge from the past few days, I thought I would write this article.
The first thing you need to do is download the JasperReports JAR file from jasperreports.sourceforge.net. There are three downloads - the project file, and two jar files.. you only need the jar file that is NOT the applet jar file. Save it to your <CFMXROOT>/wwwroot/WEB-INF/lib folder.
You'll also notice a number of requirements on this page. Get the jar files from each of the required projects, and save them into the same directory, <CFMXROOT>/wwwroot/WEB-INF/lib. You only need the jar files, but you may have to download the entire projects in ZIP or TAR.GZ format.
After you've saved the JAR files to the server, stop and restart the Coldfusion Application Server service.
Now, the following code will compile a report design, fill the report, and generate a PDF file.
<cfscript>
// we need to set the classpath to include the jars, because CFMX loads the
// stuff in the WEB-INF directory automatically, but they are NOT in the class
// path, so the java compiler, when used, won't see them.
system = CreateObject("java", "java.lang.System");
classpath = system.getProperty("java.class.path");
if (FindNoCase("jasperreports", classpath) LTE 0)
{
classpath = classpath & ";C:\CFUSIONMX\wwwroot\WEB-INF\lib\bsh-1.3.0.jar;C:\CFUSIONMX\wwwroot\WEB-INF\lib\commons-beanutils.jar;C:\CFUSIONMX\wwwroot\WEB-INF\lib\commons-collections.jar;C:\CFUSIONMX\wwwroot\WEB-INF\lib\commons-digester.jar;C:\CFUSIONMX\wwwroot\WEB-INF\lib\commons-logging-api.jar;C:\CFUSIONMX\wwwroot\WEB-INF\lib\commons-logging.jar;C:\CFUSIONMX\wwwroot\WEB-INF\lib\iReport.jar;C:\CFUSIONMX\wwwroot\WEB-INF\lib\itext-1.02b.jar;C:\CFUSIONMX\wwwroot\WEB-INF\lib\jasperreports-0.5.2.jar;C:\CFUSIONMX\wwwroot\WEB-INF\lib\jcmdline-1.0.2.jar;C:\CFUSIONMX\wwwroot\WEB-INF\lib\jcommon-0.9.3.jar;C:\CFUSIONMX\wwwroot\WEB-INF\lib\jfreechart-0.9.18.jar;C:\CFUSIONMX\wwwroot\WEB-INF\lib\log4j-1.2.8.jar;C:\CFUSIONMX\wwwroot\WEB-INF\lib\mysql-connector-java-3.0.8-stable-bin.jar;C:\CFUSIONMX\wwwroot\WEB-INF\lib\poi-2.0-final-20040126.jar;C:\CFUSIONMX\wwwroot\WEB-INF\lib\tinylaf.jar;C:\CFUSIONMX\wwwroot\WEB-INF\lib\tools.jar;C:\CFUSIONMX\wwwroot\WEB-INF\lib\xercesImpl.jar;C:\CFUSIONMX\wwwroot\WEB-INF\lib\xmlParserAPIs.jar";
system.setProperty("java.class.path",classpath);
}
// the following SQL connection code was written by Aaron Johnson
// http://cephas.net/blog/2004/05/10/coldfusion_mx_and_javasql.html
clazz = CreateObject("java", "java.lang.Class");
// replace the package/class name of your db driver
clazz.forName("macromedia.jdbc.MacromediaDriver");
driverManager = CreateObject("java", "java.sql.DriverManager");
// replace w/ your server, database name, username & password
conurl = "jdbc:mysql://localhost/DATABASENAME?user=USERNAME&password=PASSWORD";
connection = driverManager.getConnection(conurl);
// the JasperManager class is a facade which simplifies the access to the
// Jasper engine API. First we're going to instantiate the JasperManager
// object, load a report design, and compile the report design.
jasperManager = CreateObject("java", "dori.jasper.engine.JasperManager");
jasperDesign = jasperManager.loadXmlDesign("E:\Inetpub\wwwroot\jasperreports\ProfServFaq.xml");
// I was getting all kinds of crazy errors here for a while, until I
// added the stuff regarding the classpath above. If you get a strange
// compilation error that says "See Errors Above", look for them in
// <CFMXROOT>/runtime/logs/default-err.log
jasperReport = jasperManager.compileReport(jasperDesign);
// The fillReport method requires a Parameters object, and it can't be null,
// so we create the parameters object and put something random in it.
parameters = CreateObject("java", "java.util.HashMap");
parameters.put("ReportTitle", "Basic JasperReport");
// "Create" the report.. ie, run the sql query, etc... This creates a JasperPrint
// object that can be converted to a PDF file, HTML file, etc...
jasperPrint = jasperManager.fillReport(jasperReport, parameters, connection);
// Save the report to a PDF file.
jasperManager.printReportToPdfFile(jasperPrint, "E:\Inetpub\wwwroot\jasperreports\ProfServFaq.pdf");
// close the database connection.
connection.close();
</cfscript>
<H3>Success!</H3>
<A HREF="ProfServFaq.pdf">ProfServFaq.pdf</A>
About Report Design
If you're not a java programmer, and you have no experience with report design tools, you're in for some fun. I used iReport-0.3.0 to generate my first report design. It's a java-based application, and you'll need to install a Java SDK on your computer, whereever you'll be running the report designer. I used the Sun Java SDK 1.4.2_02 available here. If that link doesn't work, just go to java.sun.com. In order to successfully run iReport, you'll need to make sure that the java stuff is in your path.
Good luck! If you have any questions, please ask. I may or may not be able to help.
You are not logged in, so your subscription status for this entry is unknown. You can login or register here.
You'll want to look here:
http://media.datadirect.com/download/docs/jdbc/jdbcref/jdbcsqlsrv.html#wp965207
Based on what I can see, you need to specify "sqlserver" rather than "mssql".
The following should allow you to connect to a named instance using the specified username and password. It's just like it shows in the manual except you remove the reference to "datadirect"...
jdbc:sqlserver://server1\\instance1;User=test;Password=secret
I got a 'No suitable driver' error using 'jdbc:sqlserver:'. I had to use the following connection string (note the dbc:macromedia:sqlserver):
driverManager.getConnection("jdbc:macromedia:sqlserver://myServer;DatabaseName=myDb;User=user;Password=pass");
Errors were encountered when compiling report expressions class file: C:\CFusionMX7\runtime\bin\test_01.java:126: inconvertible types found : int required: java.lang.Integer value = (java.lang.Integer)(1);
No idea what is going on but its very disappointing considering the reports compiled in ireport just fine.