CFOUTPUT makes grouping nested result sets really easy, just use the GROUP attribute to specify the column to group by, and then nest CFOUTPUT tags as needed (and multiple levels of grouping are supported, too). This example will display dept each time that value changes, and then create an unordered list of users for each:
CFOUTPUT also makes displaying partial result sets really easy, just use STARTROW to specify the row to start from, and MAXROWS to specify the maximum number of rows to display. Here’s a simple example:
Now, what would happen if you combined GROUP with STARTROW and MAXROWS, like this?
You’d probably expect STARTROW and MAXROWS to apply to the entire result set, so that if you had 5 departments with 10 users in each (50 rows total) the first 25 rows would be displayed (all users in the first 2 departments and 5 of the users in the third). But, nope, it does not work that way. STARTROW and MAXROWS do their calculations based on how many times their CFOUTPUT is invoked, and so when used with GROUP, STARTROW and MAXROWS apply to the outer group. In this example, the first 25 groups would be included, and thus all 50 users.
I am not sure that I actually agree with this behavior, but that’s the way it is. In practice, this means that if you do need to use GROUP together with STARTROW and MAXROWS, then you’ll probably want to copy the subset of query data to be used to another query first, and pass that second subset query to CFOUTPUT.
Leave a Reply