Pivot tables with Railo - CFPIVOTTABLE

November 9, 2011 · By Gert Franz · 6 Comments

So let's take the following example. I have a resultset that contains the following fields:

ID
week_n
value_n
campaign_id
category

In this table we have several recurring values which look like this:

ID	WEEK_N	VALUE_N	CAMPAIGN_ID	CATEGORY_ID	CATEGORY
1004	35	225152	10	2	Addresses
1023	35	0	10	5	Not reached
2425	43	6500	10	5	Not reached
3492	35	0	10	6	Responses
3542	35	0	10	6	Responses
3594	35	0	10	6	Responses
3649	35	0	10	6	Responses
3696	36	0	10	6	Responses
3751	36	1	10	6	Responses
3798	36	9	10	6	Responses
... more data ...

What I would like to have is a table that contains the sum of all the values (value_n) grouped by week, campaign and category. This is quite a common task and we use it pretty regularly. So I came up with the idea of creating a tag called <CFPIVOTTABLE> that does that exactly for me. The syntax for it is quite simple and here is the description for it so far:

Description:

This tag regroups a passed query into a pivot table depending on the field values. Please check the following example for usage details:

<cfpivottable 
   query="#qry#"
   columns="location"
   rows="date"
   values="#[{field:"visitors",operation:"sum"}]#"
   totals="#true#">

The query can be any query you receive from a <cfquery> tag. The columns refer to the column that should be used as a group column and the rows contain the values of that field for the row grouping. So the above call would return how many visitors have visited any location for any date.
In addition the tag adds a total column and a total row to the returning query. If you want to have group sums by row, just add the groupTotals attribute to the call of the tag.

Attributes:

Attribute	Required	Type	Default	Description
query	true	query		Query that should be converted into a pivot table.
columns	true	string		A list containing the columns to be grouped column wise.
rows	true	string		A list containing the columns to be grouped row wise.
values	true	array of structs		Array containing structs with fieldnames and operation for it. The struct contains a key field with the column name of the field and a key operation. Valid operations are: sum,avg and count.
columnsort	false	string	ASC	In which order should the columns be sorted.
groupTotals	false	boolean	false	Adds a sum row per grouped field.
totals	false	boolean	false	Adds a totals column for every row and column.
result	false	string	result	Name of the variable that contains the result.

So we you use the CFPIVOTTABLE tag with the query above like this:

<cfpivottable
      query="#qry#"
      columns="week_n"
      rows="campaign_id,category"
      values="#[
            {field:'value_n',operation:'sum'}
         ]#"
      grouptotals="true"
      totals="true">

We will get the following result:

campaign_id	category	data	35	36	37	38	39	40	41	42	43	44	45	Total
10	Risk checks	sum : value_n	0	0	0	0	10	2	10	3	5	1	0	31
10	Responses email	sum : value_n		20	18	2	2			2	90	62		196
10	Responses	sum : value_n	0	64	27	10	14	7	7	0	321	270	28	748
10	Offers	sum : value_n	0	0	0	513	108	46	37	48	104	689	45	1590
10	Not reached	sum : value_n	0								6500			6500
10	New entry requests	sum : value_n	0	0	0									0
10	Addresses	sum : value_n	225152											225152
Sum for campaign_id			225152	84	45	525	134	55	54	53	7020	1022	73	234217
Grand Total			225152	84	45	525	134	55	54	53	7020	1022	73	234217

I have the sample code as a ZIP file and the tag is called as a CFC based custom tag. You only need to copy the tag into the corresponding WEB-INF/railo/context/library/tag or serverconfig/context/library/tag directory and you can use it as an internal tag. I would appreciate any testing comments and suggestions.

Known issues:
At the moment, if you are selecting an additional value to group as a row and you have a total attribute selected, averages and counts are summed up to the total number, which of course falsifies the result. It was a little too complex for the short time I had to respect that as well. I will try to fix it ASAP.

If the feedback is good, I will convert it into an extension soon.

View count: 16738

Tags: CFML · Extension · Features · Open Source · Railo 3.3

6 responses so far ↓

1 Thilo // Nov 9, 2011 at 11:02 AM

Sounds quite useful for us, going to test it, thanks!
Please correct the download link (duplicate blog.getrailo.com)
2 Fadi Kouri // Nov 10, 2011 at 3:52 PM

The download is not available. Thanks for the contribution to the community.
3 hairiemx // Nov 17, 2011 at 7:23 AM

How about using cfpivottablevalue tag for values?

I would like to suggest additional attribute to make Sum for, Total and Grand Total text changeable so that this tag can support other language as well.
4 Gert // Nov 17, 2011 at 8:47 AM

@Fadi: Sorry about that. Fixed the link so you should be able of using it now...
5 Gert // Nov 17, 2011 at 8:56 AM

@hairiemix: That's definitely a good idea for the texts. Can you elaborate about the CFPIVOTTABLEVALUE? Are you thinking of an inner tag that defines the values you want to use? One cfpivottablevalue with a key pair that contains the fieldname for the value and the operation?

Gert
6 hairiemx // Nov 17, 2011 at 10:29 AM

@Gert: That's exactly what I mean. I think, it would be great if the tag can suppot custom operation. For example using udf as operation. If that possible, to introduce mean, min, max, variance or even custom analysis would be piece of cake.

Default