Open Question: ColdFusion and Reports?

This post is more than 2 years old.

Alex has the following question he would like to share with my readers:

What are people using for reports these days (that's the short question)?

We have a ton of very intensive reports we currently serve up using Coldfusion and Crystal Enterprise 10. There is usually a master stored procedure and based on the results of that one, there may be several subreports that the master report passes parameters to. So far, ColdFusion reports has come up short.

Our current environment does what we need, except now there is a request for dynamic images to also be involved. Crystal handles this fine just not when published on the web. The new version of Crystal works okay, but we had to create an ASP.net page to display the reports and the text spacing is wildly different from the report developer version.

Anyway, sorry for the long e-mail. I know we aren't the only one's having this issue by some google searches. I guess my first question is what is everyone using? I did a search here for reports, but didn't see much in the way of this.

Unfortunately I have very little experience with cfreport and ColdFusion. I haven't run the Report Builder in years. I typically get by with a set of HTML tables and charts. So I'll open this to the group here - what are folks using?

Raymond Camden's Picture

About Raymond Camden

Raymond is a senior developer evangelist for Adobe. He focuses on document services, JavaScript, and enterprise cat demos. If you like this article, please consider visiting my Amazon Wishlist or donating via PayPal to show your support. You can even buy me a coffee!

Lafayette, LA https://www.raymondcamden.com

Archived Comments

Comment 1 by Kevin Schmidt posted on 2/9/2009 at 7:56 PM

Hey Ray, check out BIRT. It's java based and has a nice eclipse plugin. I've used it before and it works quite well.

Comment 2 by Nathanael Wait posted on 2/9/2009 at 7:58 PM

Try apache FOP. We are using that and java inside CF to generate reports.

Comment 3 by Douglas Knudsen posted on 2/9/2009 at 7:59 PM

I've been involved with dozens of reporting apps based around CF. I've never used the report builder tool, sucked in my opinion and well, hard to implement all the goofy 'must haves' from business requirements. I've always built them completely in CF/HTML. Of course now you could use Flex/Ajax depending on requirements. Its not difficult to code drill down/ups in CF either. Just my $0.02.

I've implemented OpenReports before too, nice tool I thought, though drill down stuff was not so easy, that may have changed with more recent releases. Its free and open and Java based. Should play well in JRun and all too.

Comment 4 by Raymond Camden posted on 2/9/2009 at 8:01 PM

Good comments guys - but can you include some URLs for folks too lazy to Google? :)

Comment 5 by Douglas Knudsen posted on 2/9/2009 at 8:06 PM

geesh, Ray, so demanding! :)

http://oreports.com/ BTW, its Birt friendly as well as Jasper, etc...

The Birt link for KS http://www.eclipse.org/birt...

Comment 6 by Paul Kukiel posted on 2/9/2009 at 8:12 PM

We use report builder quite a bit from simple data reports upto the final result of a quoting system. Reportbuilder can be clunky but its often faster to produce a report then drawing it up in HTML. At the Melbourne CFUG we had a presentation of Reportbuilder and how easy it was to get going and most people were impressed as they had never used it. Perhaps we will see more adaption if/when it's an eclipse plugin ( or part of Bolt )

Comment 7 by Dave Ferguson posted on 2/9/2009 at 8:12 PM

For me, I have never been a fan of the "canned" reporting tools. I always felt that they were to restrictive or could not handle dynamics very well. I tend to create reports using ColdFusion then output to PDF, XLS, or what ever the client needs.

my 2 cents.

--Dave

Comment 8 by David posted on 2/9/2009 at 8:18 PM

Reporting is a big part of what we do, and CF Reporting plays a bit role in that.

Typically, we use .cfr's for rather simple pie and bar charts. In our "standard" reporting package for our product we use Flex charting, which allows dynamic drilldown's, etc. We can't achieve that (natively, at least) with .cfr's. We also use good old <cfchart> on occasion.

We also struggle with the data load, although, we find that the biggest problem is on the DB side (of course, CF timeout limits also affect functionality, but we can override that on the reporting pages).

I think we're right on the brink of needing to go from reporting to warehousing, which will bring a whole new set of challenges.

Hope we can help some.

Cheers,

Davo

Comment 9 by Tom Chiverton posted on 2/9/2009 at 8:35 PM

Either HTML, cfsavecontent and cfpdf or Apache POI via. our own wrapper CFC and JavaLoader if it's more complex.
Report Builder doesn't run on our Linux workstations, and even if it did it seems people say it's very limited.

Comment 10 by Evagoras Charalambous posted on 2/9/2009 at 9:05 PM

These guys have a great product line but it's not free:
www.activepdf.com

Comment 11 by Joel Cox posted on 2/9/2009 at 9:14 PM

I use ColdFusion ReportBuilder for formal reports. Having used Crystal Reports in the past, the interface and concepts seem much the same. It's a little clunky if you want to feed data in at runtime instead of building the query inside the report, but it's sure better than trying to do it in HTML.

Comment 12 by Todd Rafferty posted on 2/9/2009 at 10:41 PM

Seems I'm stuck with using Apache POI to build a spreadsheet section in every spec I get.

Comment 13 by Dutch Rapley posted on 2/9/2009 at 11:50 PM

For excel, I typically style a "template" spreadsheet the way I want, then save it as an xml file. I then use CF to populate the template. You even have the ability to create new tabs/sheets on the fly if you want to group data by categories. You can then pass the spreadsheet to the user with cfcontent.

Comment 14 by Alex G. posted on 2/10/2009 at 12:17 AM

Thanks for all the great comments. We use HTML to PDF for our more basic reports, but a report builder becomes a must at a certain point. All our reports are on the fly and contain up to date information with many moving parts.

Crystal works great for this until you add dynamic images. My employer is an extreme stickler to have things exactly as they want them to look. I've heard about Active Reports in the past and might give that a try.

I agree with the other commenter that once you move from the built in query, CF Report builder starts to lose you. We also rely almost exclusively on stored procedures and passing parameters to subreports from the results of the stored procedure.

Right now I'm running reports manually in Crystal so they can have their dynamic images until I find a better way. Thanks again!!!

Comment 15 by thinman posted on 2/10/2009 at 12:21 AM

It took a few months to successfully get complex CF reports built with CF Report Builder. Lots of trial and error. Lots of nested reports where the nested reports needed params passed from the parent(s). Formatting, as always, was the most difficult challenge (besides the moving target of a client arbitrarily reversing themselves on what data elements needed to be in the reports, but that's another story altogether..)

Sounds like your current process of handling most of the data-intensive conditioning in the DB is right on the money, although sometimes we may wish we could do that right in our reporting environment of choice. Having done nearly all of the conditional stuff in SQL views, and then nesting them (where appropriate) within a master view worked well. Performance issues notwithstanding, it's been successful. Having said that, I did need to go back into my views and design a few specifically for processing in CF Report Builder, which may seem sorta counter-intuitive.

If you're sticking with CF Report Builder, here's a couple of tips for formating, dealing with ntext fields from the DB, and dynamic conditional images; please forgive the redundancy if you've already discovered these on your own:

1. Eliminating formatting issues regarding nested reports and great big gaps: after designing the nested report, go back into its parent report, position it exactly where you need it, then collapse it to a single line. Just grab the size handle attached to the bottom of the nested object and drag it up until the object's as flat as possible. You may then need to nudge the collapsed report around minutely with the arrow keys, but I found that once I got the floating versus absolutely positioned elements all sorted out, that the large gaps virtually eliminated.

2. Dealing with NTEXT: Added the fields from the SQL views using Report Builder's visual query builder. Saving query gets an error, and the report bombs. Opened the query back up and clicked into the visual query builder's source editor and changed the CF SQL datatype for ntext to STRING. Saved and ran without error. Caveat: subsequent revions to any part of the master or nested report(s) may require this process to be repeated. Report Builder seems to revert this datatype back to its default if any of the queries are revised for any reason.

3. Dynamic Images: You may find that storing image locations/direct paths in the db works better than as BLOBs. Seems to slow things down a bit, too, but getting the correct images consistently was the goal. I also pre-processed the images so that all images had the same dimensions. Since skewed, shrunken, or mis-proportioned images was definitely not the goal, I just used Fireworks to to matte the images. Opened up the images in Fireworks CS3, created a new layer beneath the original image that was the desired width and height (dictated by the size of the area within the report where the image would appear), and exported as .png with the new layer as the transparent alpha channel. Processed each one manually that way, but if there's a lot, you can batch process them.

Sorry or the long comment on your post, but I hope it helps in some way! Maybe I shoulda just downloaded BlogCFC and created my own blog. Heh.

Comment 16 by Raymond Camden posted on 2/10/2009 at 12:30 AM

@thinman: Your comment isn't too long. I mainly only mind long _code_ postings.

Comment 17 by denny posted on 2/10/2009 at 3:00 AM

JasperServer is what we use, and it's the cat's meow.

http://jasperforge.org/plug...

(Tiny-er: http://tinyurl.com/csw2es )

Really excellent! With JasperReports, iReport, and JasperServer, you've got a complete solution. -- iReport /destroys/ CF's ReportBuilder, and you can set JasperServer up to do all kinds of stuff. JasperReports is what CF uses under the hood.

Had a lot of fun with those packages, personally.

Comment 18 by Dave DuPlantis posted on 2/10/2009 at 8:07 PM

eh ... we used ActivePDF the last place I worked. It was tolerable, but it didn't work as smoothly as we'd liked, and at the time we had to set up an ASP page to use it. YMMV.

We used a custom charting package for graphs. The name of that package escapes me ... it did decent graphs, but it wasn't particularly user-friendly in terms of passing parameters and such. We probably should have wrapped it with a CFC ... oh wait, I remembered! It's NetCharts, from Visual Mining. http://www.visualmining.com/

Comment 19 by Jeff Self posted on 2/10/2009 at 8:22 PM

Have you looked into Jasper Reports? Very powerful stuff.

It's open source, but they have a commercial version that has a lot of GUI stuff.

http://jasperforge.org/

Comment 20 by Alex G. posted on 2/10/2009 at 8:40 PM

JasperReports looks great, and I do like the fact that Adobe is using it as well "under the hood."

This may be off topic, but has anyone used a JDBC driver for SQL Server? If so, which one? I've tried two now and they connect to SQL server fine, but I don't see any tables/stored procedures listed so I'm doing something wrong.

I will post any successes on the reports here! Thank you so much for your help!

Comment 21 by Alex G. posted on 2/10/2009 at 9:17 PM

Actually I take that back, the ODBC bridge works just fine on the PC, but in OSX or PC I can't connect using native drivers. I'll work on it, and let you guys know. Jasper looks really good...

Comment 22 by Henry Ho posted on 2/11/2009 at 3:24 AM

I use use CF Report Builder, but couldn't figure out how to draw a table with border.

Comment 23 by Alan Barlow posted on 2/12/2009 at 12:34 PM

Hi, we're using AmCharts for embedding charts and graphs into reports... you simply use coldfusion to generate the basic xml data files and voila!

It is free to use too!

http://www.amcharts.com

Comment 24 by Alex G. posted on 2/12/2009 at 7:26 PM

That looks like another good product, but the interest in charting is actually very low for us.

Comment 25 by amira posted on 3/11/2009 at 3:12 PM

Did anybody think about using birt to preview xsl-fo:meaning use birt to open and edit reports produced by fop by just an drag and drop. I'want to develop such project, I need your help.

Comment 26 by Dan Roberts posted on 3/18/2009 at 6:07 PM

I am with Henry Hoe on not being able to create tables. I have a report now that needs to have a table that may or may not display some rows conditionally.

I opted for CFDocument but when I was 99% done I noticed i can't prevent page-breaks in the middle of content blocks. This is done in CFReportBuilder using a property on a sub-report. The docs for CFDocument say that the style "page-break-inside" is supported but it doesn't actually work. I'm basically stuck with a solution that will require trial and error every time a report is created :-\

Comment 27 by Alex G. posted on 4/7/2009 at 10:45 PM

This software: http://www.milletsoftware.c... did everything I needed and there is so much more to discover from it. E-mail support responses are almost instant, and I've spoken with the main developer a few times as issues were worked out.

No worries on page breaks, or any of the strange issues had. I just ran cfexecute against the software and produced a PDF.

Thanks for all the suggestions. Jasper Reports looks promising. I'll continue to keep my eye on it.

Comment 28 by Brian O. posted on 4/11/2009 at 5:27 AM

I've done nearly a hundred ReportBuilder Reports with CF7 and 8. I also reported many bugs in the CF8 beta of which most were fixed. Pre-CF8, the ReportBuilder was awful, but I think CF8 now is decent for PDFs, clunky with spreadsheet reports, but doable. Not sure why people are having problems passing in data, that part worked fine for me. I use POI for when I need to manipulate existing spreadsheets, but not usually for creating new ones, except once I think. For creating spreadhseets I like using cfcontent type="application/msexcel" and cfheader. For simple PDFs I prefer using cfdocument, but as one commenter mentioned, it has its limits. I hit the same one related to pagebreaks and had to cluge the formating. From now on if I see the report requirements being complex, requiring page breaks in certain places or special grouping required, I'd use the ReportBuilder, but only because I know it well. If it got super complex, I might try a new solution. The Jasper Reports sounds interesting. Hopefully CF9 ReportBuilder will be improved.

Comment 29 by Antoine posted on 4/15/2009 at 7:22 PM

Well, so far Reports Builder (CF8) is quite easy to use for simple reports.
I do still hit a problem : the XHTML formating text parameter for query fields does not seems to work (MS SQL ntext containing fckeditor edited HTML text).
Found no solution yet ...
Brian, as you seems advanced in its use, may you help ?
Could be a great time-saver for me.

Comment 30 by Brian O posted on 4/15/2009 at 8:40 PM

Would need more details, but I know the attribute you referred to ('XHTML Text Formatting' a boolean field) and have it set to true in some of my reports and false in others (not sure why), but I've only generated PDF, Flash, Excel and maybe an RTF report so far with Report Builder, so I don't think it affected my reports. Are you trying to generate HTML or XML or what? I can only suggest posting a bug at http://www.adobe.com/cfusio... and checking the Adobe Forum at http://forums.adobe.com/com.... Also if you google the problem I see you are not alone (e.g.,
http://www.cfreport.org/ind... and http://www.experts-exchange.... Some of these allude to issues with what you are putting into the field and whether you are using a nonstandard charset.

Comment 31 by Antoine posted on 4/15/2009 at 9:34 PM

Brian,
I did browsed these topics, so far they didn't solve my issue.
But charset encoding may be a good search tip.
My MS SQL database is French (_CI_AS for sorting).
I'll try some cfprocessingdirective and other workarounds and will be back then.
Thanks for your quick response.

Comment 32 by Antoine posted on 4/16/2009 at 1:41 AM

Brian,
I've been searchin' around charset encoding but did'nt find no answer. The cfreport target format is PDF, but your question about format made me test HTML format (within a cfreprort tag). Editing the code, I discovered that the text was "html escaped" (ie: & lt;p& gt; for <p> [blanks after "&" added to be sure they'll be readable in this post]). Looks excactly like an HtmlEditFormat-ed string ...
It seems to confirm that XhtmlText param is bypassed ... or I'm missing something ?

Comment 33 by Antoine posted on 4/16/2009 at 2:42 AM

Brian, I found the problem (by inserting different combinations of HTML instructions).
While a trivial <p>toto</p><i> titi </i> will work, the whole string will be escaped as soon as an unrecognized sequence (as &aacute ;) caracter will be found.
So the problem is identified ... not the solution yet.

Ray, please excuse me for 3 posts, quite unusefull in this topic. Tomorrow i'll do a follow-up in adobe's forum : http://forums.adobe.com/thr... (time to go to bed here, in France).

Comment 34 by denny posted on 4/16/2009 at 2:54 AM

@Antoine: The allowed HTML tags are listed in the jasperreport docs somewhere (under the hood, CF is using jasperreports for all this stuff).

iReport /destroys/ the CF report builder, it's too bad CF wasn't able to leverage it instead.

Eh.

Carry on.

Comment 35 by Antoine posted on 4/16/2009 at 7:26 PM

@ Denny : I didn't find the whole list, but I now understand the limits of the XHTML Text formatting parameters. It is designed ONLY for presentation purposes, excluding all that is not "caracter related".
It means no Divs, Tables, Ifrmaes or so.

Also, html special caracters are NOT supported, we have to use ISO codes instead (with double pounds).

One step after another ... as I said, I'll keep on tracking this on adobe's forum, here: http://forums.adobe.com/thr...

Thanks again to you and Brian; it does help not to feel so loooooonly !

Comment 36 by sirila posted on 5/1/2009 at 12:26 AM

Hi All,
We are trying to integrate coldfusion 8 and crystal reports 2008. i created a report and trying to call it using cfreport tag. all i get is a blank page being displayed. can anyone please... shed thought on this.i tried googling and tried copying the crep.dll file, tried custom installation but no use.please help me out with this

Comment 37 by Dan Roberts posted on 5/1/2009 at 12:53 AM

@sirila

This might help:
http://asreports.riaforge.org/

Comment 38 by sirila posted on 5/1/2009 at 6:09 PM

Dan,
thanks for the link. i downloaded that zip file.sorry it could be my ignorance i didnt find any clear instructions with it.how should i integrate that into mine. there are some cfc which point to version 10 and 11. should i change them to accommodate version 2008. im just lost on this. instead of integrating is there any way of calling crystal reports via business objects.
thanks in advance

Comment 39 by Brian O posted on 6/12/2009 at 11:38 PM

Antoine, I'm doing a cfform that uses cftextarea richtext="yes", storing that in SQL Server and pulling it to a cfreport using the XHTML Text Formatting attribute. I see what you are saying and even used the function you posted in the Adobe Forum linked above to get me further along. My users need to be able to add a page break, but of course the code for that causes the conversion to break. I hate to have to convert every character, especially since I don't know what a user might add, (e.g., an image) and not sure if that would work fully anyway. We must be missing something here. Has anyone found a solution or workaround related to passing html code to Report Builder? I wonder if it is being addressed in CF9.

Comment 40 by Antoine posted on 6/13/2009 at 12:55 AM

Brian, I posted my answer to adobe's forum, here : http://forums.adobe.com/mes...
Hope it helps,
Regards
Antoine

Comment 41 by Steve Caldwell posted on 11/6/2009 at 1:09 AM

See the link below for my fix for displaying dynamic HTML in a CFR

http://www.dreamincode.net/...

Comment 42 by Ken Stephens posted on 1/26/2010 at 2:43 AM

We'd like to convert our charts from <cfchart> to SQL Server Reporting Services 2008. The interface through cfhttp is a little flaky, though. Does anyone have experience integrating SSRS 2008 with CF8?

Comment 43 by Mike Bernstein posted on 6/9/2011 at 11:14 PM

I am running Cold Fusion 9 and and Crystal Reports 2011 and I am trying to use the cfreport tag to run the crystal report. I get a blank page with no error messages when I try to run the report. My cfm file is in the same directly as my crystal report file. here is my code.
<cfreport
report = 'report1.rpt'
>
</cfreport>

I am not sure why this is not working.

Comment 44 by ozzy posted on 6/15/2011 at 10:38 PM

cfchart all the way baby. By the way, what's under hood in CF9 is not JasperReports, it's GreenPoint Software WebCharts3D, which is also in Websphere Dashboard Services and other BI tools. With webcharts and cfchart, you can use shape (shp) files, edit the XML of the chart with the wc50.jar tool, use dynamic data and styles via XML to Webcharts3d, create drilldown charts, create dynamic clickable maps, and output them in PDF, JPG, GIF, PNG, DOC, and other formats. There's no need for 3rd party charting in CF8-9. HOWEVER, I'm trying to find a way to enhance the charting in Railo which really does need 3rd party support. I am working on a a new virtual appliance with Railo and an open source charting engine for release this year. If anybody would like to contribute, see blog.thesitestudio.com

Comment 45 by Matt VanderMeer posted on 10/14/2011 at 6:02 PM

Mike, did you ever solve your problem with Crystal Reports 2011 and CF9. I am experiencing the same thing. We have many reports built in CR2011 and I want to call them through Coldfusion but the cfreport tag does not return any HTML. I am hoping you figured this out because I can't find it anywhere.

Comment 46 by Mike Bernstein posted on 10/14/2011 at 6:15 PM

No, I never got it working.

Comment 47 by Anthony posted on 11/16/2012 at 4:36 PM

Hi Mike - did you ever get your issue with crystal and CF resolved.
I have ran into the exact same problem and would love to know?

Thanks
Anthony