ColdFusion Report Builder Intro

Here is the info on the site:
Introduction to Report Builder
Posted On : 09/06/2005 at 12:39 PM | Posted By : Remote Synthesis
 Related Categories: ColdFusion

 This is a draft walk-through covering the basics of the ColdFusion report builder. It was written as an article for CFDJ originally, and the topic was suggested to me. As it appears to have gotten lost in the shuffle (I wrote it back in May/June), I figured I might as well publish it here as it does no good sitting on my PC.I will preface it by saying that I did find the report builder somewhat tedious and frustrating. I don't have to build alot of reports of this nature in my day-to-day work, but I left this article feeling that I could accomplish much the same functionality without the hassle by wrapping an report generated in HTML in cfdocument tags. Do any of you who have tried the report generator agree or am I missing something? Perhaps as the feature matures... Anyway, the text of the article is as follows (please note that new resources have become available since I wrote this but the text has not been updated):



When I think of my least favorite things to do as a developer, building reports is up there right behind building forms. Fortunately for ColdFusion developers, Macromedia has supplied tools built into ColdFusion MX 7 to handle both tasks. However, while the new cfform capabilities have gotten a lot of attention since the release, the greatly enhanced reporting abilities built into CFMX 7 have not. In this article, I hope to give a basic overview of the ColdFusion Report Builder and changes to cfreport.


ColdFusion Report Builder?


Ok...when this topic was suggested, my first reaction was something along the lines of, "I think I remember hearing something about that.” In fact, unless you were doing Crystal Reports integration, you may never have touched cfreport in prior editions of CF, and those of you who have don't often speak well of the experience (http://weblogs.macromedia.com/cantrell/archives/2003/04/crystal_reports.cfm). In addition, currently there is not an abundance of helpful information available on the topic (a quick search of MXNA for cfreport and report builder turned up 5 related entries of which 1 was a Tech Note).


So what is the ColdFusion Report Builder? It is an independent tool included with ColdFusion that you can install to create custom report templates (.cfr). The software is currently available for Windows only but the reports it builds can be run on any platform (you can find the installation file in your CFIDE folder under installer). In order to work properly, you will also need to have RDS enabled.


Generally speaking, anyone familiar with tools such as the SQL Query Analyzer or even Access will find the Report Builder comfortable. In fact, knowledge of ColdFusion is helpful when using the software, but certainly not a requirement.


Building a Simple Report


In order to become familiar with the ColdFusion Report Builder, we will walk-through creating a simple report to create a basic blog statistics report for Raymond Camden's blogCFC (available at http://ray.camdenfamily.com/blog.zip). BlogCFC does include a stats page already which, for the sake of learning a thing or two, ours is based upon but is different.


Once you have the report builder installed, open it and select the "Using Report Wizard” option and leave "Report Creation Wizard” selected; click ok. When the next screen opens, click the "Query Builder” button (note: this may take a few moments to open).


When the query builder opens, leave the "SQL Query Builder” option in the top left corner selected ("Advanced” mode allows you to write ColdFusion code directly that will return a query to be used by the Report Builder). If you need to, select the correct RDS server from the pull-down menu and expand the data source (mine is just blog) and tables for blogCFC.


Double-click tblblogentries, tblblogcategories and tblblogentriescategories. Now, as you might expect, you can drag fields to establish the relationships between the tables, so drag tblblogentriescategories.entryidfk to tblblogentries.id. Drag tblblogentriescategories.categoryidfk to tblblogcategories.categoryid and right click it, selecting the "Select All Rows from tblblogcategories” option.


Now that we have established the tables and relationships, let's select the fields by double clicking them:
from tblblogcategories select categoryid, categoryname and blog
from tblblogentries select id.


First, pull down the Condition menu for tblblogcategories.id and select "Group By.” Next, pull down sort order for categoryname and select "1.” For the "blog” field, uncheck the output option and pull-down Criteria and select ='#CFvariable#' (more on that later). For the tblblogentries.id field, put "totalEntries” in the Alias column and pull down "Count” under Condition. Ah, finally, we have finished our first query!


You can test your query, but a note of caution; I found that when I would do something illegal in the query code, it would often cause Report Builder to shut down completely negating all my hard work. In my tests I found it always useful to develop and test my queries in a cfm template and simply copy and paste the query code into the lower text area (hmph...now you tell me). Click the "Save” button to continue; this will close the window and then click "Next” to continue.


On the following screen, simply click next as you do not need to denote any group by fields (this will cause the report to hide repeated values for the selected columns and to help determine the order in which items are displayed).


On the "Report Layout” screen, select "grid.” This format is useful for displaying longer lists of data in a table format. The "free form” format is better suited for item details with fewer records. You may leave "Portrait” selected under Orientation.


On the "Report Style” and "Report Theme” screens, you may leave the default options (note: this article will not focus on formatting options; for a good review of report builder formatting options please read http://www.macromedia.com/devnet/mx/coldfusion/articles/reporting.html or create Avery Labels using report builder http://www.macromedia.com/devnet/mx/coldfusion/articles/averylabels.html). On the "Report Name” page, simply give your report any name you wish (I chose "Blog Stats” for dramatic effect).


Congratulations, you've created your first non-functioning report! Why? Well, you may remember way back we set the blog field equal to '#CFvariable#'. Now, we need to define the CFvariable. In the bottom right hand corner of the screen you should see the "Fields and Parameters” dialog box. This is where we can define new query fields, calculated fields and input parameters, and also where we can drag these fields into our report. In this scenario we are adding an input parameter.


Input parameters are a very powerful tool in the Report Builder. It allows us to pass information from a ColdFusion template into our report template using the cfreport tag...more on that later. For now, simply double click the input parameters option, and place:

Name = blog
Default text label = blog
Data type = string
Default value = "default” (this is the default blog name in blogCFC).


(For an excellent Breezo of input parameters by Ben Forta go to http://macromedia.breezecentral.com/p99229136/)


You will now need to click the "Report Query” button on the top menu and change #CFvariable# to #param.blog# (all input parameters use the param scope). You are probably wondering why we did not change that text before. Well, you could have, but in all likelihood it would revert back to #Cfvariable#...not sure why, but if you choose to rerun the report wizard for this report (under the report menu), you will find the same odd behavior.


Save your report and now you really are done. Click the preview button on the menu above (or press F12). You can play with formatting all you like and also note the properties menu on the upper right hand side of the screen that allows you to access many of the advanced properties available for each item. This is where the power of the Report Builder really becomes apparent (for example you can modify any field using the "Expression Builder”...but alas that will have to wait).


(For a review of using the "print when” property by Ben Forta go to http://www.forta.com/blog/index.cfm?mode=e&entry=1531)


Adding a Subreport


At this point we have a really simple report that will open in FlashPaper, PDF or Excel formats, but seems to be lacking a little Je ne sais quoi which we will achieve by including a subreport.


A subreport is usually related to the data in the detail band of your report, but does not need to be. You can also pass values to your subreport including parameters and query values.


You may need to expand the detail band to make room for your subreport as it going to expand on the category details for each record. Now select the subreport option from your tools menu on the left and draw a subreport box in your detail band. This will open another wizard where you will select "As a New Report” option.


To speed up the process, I am including the query we will use for the subreport (but by all means, if you are up for it, knock yourself out):

SELECT tblblogentries.title, tblblogentries.posted, COUNT(tblblogcomments.id) AS commentCount
 FROM tblblogentriescategories, tblblogcomments RIGHT OUTER JOIN tblblogentries ON tblblogcomments.entryidfk = tblblogentries.id
 WHERE tblblogentriescategories.entryidfk = tblblogentries.id
 AND (tblblogentriescategories.categoryidfk = '#param.categoryID#')
 GROUP BY tblblogentries.id
 ORDER BY tblblogentries.posted DESC


You can leave all the default settings in place throughout the wizard, except the following:

On "Report Style” choose "Only Detail Band”
Name your report (I chose "Category Report”)
Onthe "Subreport Binding” page, click the "Add” button and set
Parameter name = categoryID
Pull down Value and select #query.categoryID#
Leave data type as string
Name your file (I chose categoryReport)


Save both your subreport template and your report template and press F12 (preview). Now your report includes a report on the entries within a categories and totals on the number of comments for each entry in much the same way as a group within an output.


Using cfreport


I will not go into great detail on the updated cfreport syntax, but I would like to note a couple of items here.


First, though I will not cover it here, you can override the query that we created within the template by passing it in the query attribute thereby allowing you to use CFCs or any other ColdFusion code to create/modify your query (provided the necessary fields are provided to the report). In fact the Report Builder will create and customize this code snippet for you by selecting File > Code Snippet.


You specify the template we just created in the template attribute and you can override the template's default display format by using the format attribute (valid options are FlashPaper, PDF or Excel).


Lastly, you can pass parameters to cfreport by using the cfreportparam tag within your cfreport tag. In our case, we will pass the name of the blog from blogCFC's application scope so that this template will work regardless of your blog name (remember we specified a default of "default”).


The code is as follows:

<cfparam name="URL.format" default="FlashPaper">
<cfreport template="../../blogStats.cfr" format="#URL.format#">
<cfreportparam name="blog" value="#application.blog.getProperty('name')#">
</cfreport>


That is easy enough, right? (note that I included the use of a url value to make it easy for you to try out other formats on the fly).


In Conclusion


Report Builder and the new features of cfreport can simplify the creation of complicated and tedious report templates while offering a wide range of customization options. I hope that this review has given you an understanding of the power of the Report Builder and the cfreport tag. As you may have noticed, we have only scratched the surface of the tool's possibilities and features.