WWW@UNT.EDU

WWW@UNT.EDU

Beating the <CFOUTPUT>:

or Using Loops to Wrap?

By Shannon Eric Peevey, UNT Central Web Support

Hello, Everyone! It seems that my articles are becoming more and more sporadic, but never fear, you are all close to my heart, and I am thinking about you :)

This month...

I decided to write about something that is close to my heart at this time. It has to do with creating text reports in ColdFusion. I am currently working on a project that calls for a plethora of reports to be created dynamically, based on information that is held in a database. When starting the project, I blew-off the reporting features of the application. I figured that would be the easy part. I mean, come on, I needed to create a multi-tier security structure... What could be harder than that?! Reports... Come on! Little did I know, that the security structure, though difficult to create, fits nicely into the abilities of ColdFusion. Guess what?! Reporting does not. In the past, I have relied solely on the <CFOUTPUT> tag to output the necessary data to the screen. (Even my security structure was created using <CFOUTPUT> to output the correct application functionality, based on the security level that the user using the application was allowed to see.) I did not realize that there might be a situation that would call for something more...

The situation...

I needed to create on-line reports based on reports that the group employing had been using for some time. Here is an example:

[ HEADING ]

_________________________________________________________

FACILITY: <value pulled from db>

TEAM NAME TYPE CATEGORY

<value> <value> <value>

TEAM ROLE TEAM MEMBER

<value> <value>

<value> <value>

<value> <value>

To explain. We have the heading on the page. Then we allow a user to choose a facility, based on it's relationship to a company, which is also chosen by the user. This information is pulled from the database. (All information from this point is under the umbrella of this facility.) For example, we have a tree:

Company -> Facility 1 -> Team 1 -> Member 1

Member 2

Team 2 -> Member 1

Member 2

Facility 2 -> Team 1 -> Member 1

Member 2

Team 2 -> Member 1

Member 2

As you can see, there is one company, and then the possibility of one or more facilities underneath it. One or more teams under a facility, and one or more members under each team. (Members may be members of multiple teams, and can have multiple levels of security access, as well.) Therefore, after the company is chosen, the user must choose a facility, and then all teams and users related with those teams will be output to the screen in this “team membership report”. The report must output the name of the company, and then a line describing a team will output. Under this line, we want to output all of the team members and their security levels in relation to that team. (security level is labeled, “TEAM ROLE”.) How do we do this?

My First Instinct...

was to use <CFOUTPUT> to create this report. But first of all, what is one of the characteristics of the <CFOUTPUT> tag? That it loops through all of the records in the recordset, unless otherwise specified using the “startRow”, and “maxRows” attributes. To do this, I would need to create a variable that I could increment each time through the loop, and pass to the <CFOUTPUT> tag to increment the record number up one. Then I would need to nest a second <CFOUTPUT> that will display different information, the team members and roles, and then break out of the second loop, and move back to the first <CFOUTPUT>, increment the variable by one, and repeat the process.

Needless to say, I was able to use <CFOUTPUT> for some of the easier reports, but found that I needed something more powerful to allow me to loop through the data in a more efficient and accurate way.

Enter Query of queries and <CFLOOP>...

Because of the limitations of the nested <CFOUTPUT> tags, I was only able to access one query, and then group by multiple columns as I moved through the nest. I needed a way around this limitation, and I found it in “Query of queries”, (which, I believe has only been implemented in the latest release of ColdFusion). “Query of queries” is the ability to query an existing query resultset, instead of the database. This allows you to pull all of the data that is needed from the database at the beginning of your code, and then pull customizable resultsets from that query. For example:

<CFQUERY DATASOURCE=”test” NAME=”main_query”>

SELECT * FROM test_table

</CFQUERY>

This is our main query. Now let's pull more specific information from that query.

<CFQUERY DBTYPE=”query” NAME=”secondary_query”>

SELECT * FROM main_query

WHERE something=”a_value”

</CFQUERY>

The differences between the two queries, are:

DBTYPE=”query” -> This tells the ColdFusion server that we are accessing another query, in memory, instead of the ODBC connection.
We replace the name of the table with the name of the query.

The “query of queries” allows us to “drill-down” into our data, increases the speed of the database transactions, and, as I found out, allows more flexibility in report design.

Now, wouldn't it be nice to be able to access these queries from inside a nested <CFOUTPUT> structure? The answer is yes, and the way is <CFLOOP>. In their benevolence, Allaire, (or could it be Macromedia?), had the foresight to see that Shannon Eric Peevey, crazy as he is, might need to access multiple queries inside of a nested “output” structure, and created an attribute for the <CFLOOP> tag that allows us to output data using a <CFLOOP> instead of <CFOUTPUT>. Bravo!! This attribute is: QUERY=”query_name”.

<CFLOOP QUERY=”query_name”>

<CFOUTPUT>

... Output something here ...

</CFOUTPUT>

</CFLOOP>

is the syntax.

Notice that we still use <CFOUTPUT>, but it is only used to alert the ColdFusion server that the enclosed variables need to be interpreted as variables, and not text. Here is the code that was used to recreate the report at the beginning of this article.

SELECT DISTINCT example_security_level.*, security_level.security_level_name,

CONCAT(example_information.example_lastname,', ', example_information.example_firstname) AS full_name,

example_information.example_employee_id, team.team_name, team.date_established AS team_established,

team.date_disbanded, team_type.team_type_name, team_category.team_category_name,

facility.facility_name, company.company_name

FROM example_security_level, security_level, example_information, team, team_type, team_category,

facility, company

WHERE team.facility_id = facility.facility_id

AND team.team_type_id = team_type.team_type_id

AND team.team_category_id = team_category.team_category_id

AND example_security_level.example_id = example_information.example_id

AND example_security_level.team_id = team.team_id

AND example_security_level.security_level_id = security_level.security_level_id

AND facility.company_id = company.company_id

AND company.company_id = '#Url.company_id#'

ORDER BY facility.facility_name ASC

</CFQUERY>

SELECT DISTINCT facility_name

FROM team_membership

ORDER BY facility_name ASC

</CFQUERY>

#facility_name# </td>

</tr>

</CFOUTPUT>

SELECT DISTINCT team_name, team_type_name, team_category_name, team_established, date_disbanded

FROM team_membership

WHERE facility_name = '#facility_name#'

ORDER BY team_name ASC

</CFQUERY>

<tr>

</tr>

<tr>

</tr>

<tr>

Team Name  </td>

Type  </td>

Category  </td>

Established  </td>

Disbanded  </td>

</tr>

<tr>

#team_name# </td>

#team_type_name# </td>

#team_category_name# </td>

#DateFormat(team_established, "mm/dd/yyyy")#  </td>

#DateFormat(date_disbanded, "mm/dd/yyyy")#  </td>

</tr>

</CFOUTPUT>

<tr>

</tr>

<tr>

</CFOUTPUT>

Team Member </td>

</CFOUTPUT>

<a href="report.cfm">Employee ID </td>

</CFOUTPUT>

Date Assigned to Team </td>

</CFOUTPUT>

</td>

</tr>

SELECT DISTINCT security_level_name, full_name, example_employee_id, date_established

FROM team_membership

WHERE team_name = '#team_name#'

</CFQUERY>

<tr>

#security_level_name# </td>

#full_name# </td>

#example_employee_id# </td>

#DateFormat(date_established, "mm/dd/yyyy")#  </td>

</tr>

</CFOUTPUT>

</CFLOOP>

Now, some of the functionality has been removed, and the database information has been changed to protect the innocent ;) Besides that, this code is used to recreate a “team membership report” that output the facility name, the individual team data, and the individual team members on each team.

To explain...

The query at the beginning of the code grabs all of the necessary information from multiple tables in the database. (It is more complex than that, but the joins, aggregate functions, etc., are not pertinent to this article. Next we enter the body of the page and run a query on the original query, to pull the facility name from the original query. This is then output to the browser using the <CFLOOP> tag as follows:

SELECT DISTINCT facility_name

FROM team_membership

ORDER BY facility_name ASC

</CFQUERY>

#facility_name# </td>

</tr>

</CFOUTPUT>

At this point, the <CFLOOP> could have been closed. (This is because there is only one value in the query that is associated with “facility”.) But, in developing the application, it became apparent that the administrative group would want the ability to view team membership for multiple companies. Therefore, this <CFLOOP> tag was not closed until the end of the code.

Next, we need to nest two groups of output. The first, by team, and the second, by membership in said team. To do this, we need to query the “originating” query to get a list of teams that are associated with the chosen facility.

SELECT DISTINCT team_name, team_type_name, team_category_name, team_established, date_disbanded

FROM team_membership

WHERE facility_name = '#facility_name#'

ORDER BY team_name ASC

</CFQUERY>

The variable #facility_name# is populated by the values returned from the “originating” query. Next, we output the first “team_name” value from the recordset that this secondary query has created from the “originating” query.

<tr>

</tr>

<tr>

</tr>

<tr>

Team Name  </td>

Type  </td>

Category  </td>

Established  </td>

Disbanded  </td>

</tr>

<tr>

#team_name# </td>

#team_type_name# </td>

#team_category_name# </td>

#DateFormat(team_established, "mm/dd/yyyy")#  </td>

#DateFormat(date_disbanded, "mm/dd/yyyy")#  </td>

</tr>

</CFOUTPUT>

(In essence, this is using the values in the recordset as an array. We begin the loop with the first value in the array, (recordset), and then perform a group of actions, then return to the array to grab the second value, perform a group of actions, and keep returning to the array, (recordset), until there are no more values. The flow of control will then break from the loop, and continue with the rest of the code on the page. Cool, huh?!) Let's say the first value associated with “team_name” is, “Team 1”. The code then takes that value and queries the “originating” query again:

SELECT DISTINCT security_level_name, full_name, example_employee_id, date_established

FROM team_membership

WHERE team_name = '#team_name#'

</CFQUERY>

The value, “Team 1”, is passed to the variable, “#team_name#, and the secondary recordset is populated with the team members associated with “Team 1”, under facility, “x”, and company, “y”. This is then output to the screen.

<tr>

#security_level_name# </td>

#full_name# </td>

#example_employee_id# </td>

#DateFormat(date_established, "mm/dd/yyyy")#

</td>

</tr>

</CFOUTPUT>

</CFLOOP>

Finally, the most important part of the code, closing the loops at the correct place. (In this case, at the end of the last output code.) The code then repeats itself until we come to the end of the recordset, or array. :)

Let's do it again!

In this article, we discussed the usage of <CFLOOP>, as a tag that can be used for reporting flexibility. We have seen that it is possible to query an existing recordset to “drill-down” into the data, and it is possible to use this “query of queries” ability, plus <CFLOOP> to nest multiple output groups from multiple queries. I hope that you hung in there, and that you find that this helps you in the report creation on your Web apps :) Take care!