Build a Pivot Table in ColdFusion in four easy steps

Posted At : September 4, 2006 10:38 PM | Posted By : Lucas
Related Categories: ColdFusion

If you are like us, you often have to build a load of reports and when it come to reports, CFQuery just doesn't cut it - no matter how good your sql is. The management folks just want more. They want Excel, they want the holy grail of data. They want a pivot table. They want a recordset ordered and grouped by both rows and columns.

It isn't as hard as it seems. With just two queries you can get the best looking report you have ever seen.

Lets look at a common security table You have a bunch of users and a bunch of roles. Each user can have more than one role and you want to be able to see which user has which role at a glance. You could just dump them all out.

<cfquery name="qGetRoles" datasource="#application.dsn#">
SELECT
a.AdminID, a.FirstName, a.LastName,r.rolename
FROM TabAdmins a
 INNER JOIN TabAdminsXRoles aXr ON a.AdminID = aXr.AdminID
 INNER JOIN TabRoles r ON aXr.roleID = r.ID
WHERE Active = 1
</cfquery>
<cfdump var="#qGetRoles#">

and this is ok, but if we could just turn that last column, the rolename into a something more, a set of column, one per role with simple true/false values if a user is in a specific role.

We grab these roles so we can reuse them in our main query

<cfquery name="qGetRoles" datasource="#application.rbdsn#">
SELECT ID,RoleName
FROM TabRoles
</cfquery>
<cfdump var="#qGetRoles#">

That looks like our list of roles. Now we need to construct our main query.

We start with a part of our first query, we just grab the personal details – firstname, lastname and the like.

<cfquery name="qGetUsersPivot" datasource="#application.rbdsn#">
SELECT
 <cfloop query="qGetRoles">
 sum(case when aXr.RoleID = #qGetRoles.ID# then 1 else 0 end) as c_#toString(qGetRoles.ID)#,
 </cfloop>
a.AdminID, a.FirstName, a.LastName
FROM TabAdmins a
 INNER JOIN TabAdminsXRoles aXr ON a.AdminID = aXr.AdminID
WHERE Active = 1
group by a.adminID,a.firstName,a.lastName
</cfquery>
<cfdump var="#qGetUsersPivot#">

The second part is to create a nested loop, to evaluate if this user is in any of the roles and if they are, add one to the counter for that role and move on. This is the hardest part of the query to understand due to the nested looping. If we turn on ColdFusion debugging, we can have a look at the full query as it was passed to the database.

<cfquery name="qGetUsersPivot" datasource="#application.rbdsn#">
SELECT
 sum(case when aXr.RoleID = 1 then 1 else 0 end) as c_1,
 sum(case when aXr.RoleID = 2 then 1 else 0 end) as c_2,
 sum(case when aXr.RoleID = 3 then 1 else 0 end) as c_3,
 sum(case when aXr.RoleID = 4 then 1 else 0 end) as c_4,
 sum(case when aXr.RoleID = 5 then 1 else 0 end) as c_5,
 sum(case when aXr.RoleID = 6 then 1 else 0 end) as c_6,
 sum(case when aXr.RoleID = 7 then 1 else 0 end) as c_7,
 sum(case when aXr.RoleID = 8 then 1 else 0 end) as c_8,
 sum(case when aXr.RoleID = 9 then 1 else 0 end) as c_9,
 sum(case when aXr.RoleID = 10 then 1 else 0 end) as c_10,
 sum(case when aXr.RoleID = 11 then 1 else 0 end) as c_11,
 sum(case when aXr.RoleID = 12 then 1 else 0 end) as c_12,
 sum(case when aXr.RoleID = 13 then 1 else 0 end) as c_13,
 sum(case when aXr.RoleID = 14 then 1 else 0 end) as c_14,
 sum(case when aXr.RoleID = 15 then 1 else 0 end) as c_15,

a.AdminID, a.FirstName, a.LastName
FROM TabAdmins a
 INNER JOIN TabAdminsXRoles aXr ON a.AdminID = aXr.AdminID
WHERE Active = 1
group by a.adminID,a.firstName,a.lastName
</cfquery>

There is a lot going on but it is just a load of case statements to get things adding up. The other thing that is a little odd is the sum statement. We don't want a row for every single role that a person might be a member of but we want to collapse these down to a single line.

Last thing to do is to get the data out to the screen. here we will just use our datagrid and a quick loop and we are done

Ok, so this is just simple true and false, I know that Rob will be hot on my tail with an example of how we can get this actually adding things up and then graphing them.

Comments (14) |

Print |

del.icio.us |

Linking Blogs | 5386 Views

Related Blog Entries

Open Source Datagrid (August 8, 2006)

Comments

[Add Comment]

New to coldfusion. But yes...mangement does want the ability to Pivot. This looks promising. How do you add the aggregate math functions to this (summing for example)

# Posted By Edwin | 1/10/07 10:39 AM

Build a Pivot Table in ColdFusion in four easy steps

# Posted By warhammer gold | 10/9/08 7:23 PM

<A href="http://www.vipwarhammergold.com/" target=_blank>warhammer gold</A> <A href="http://www.vipwarhammergold.com/news/news.html" target=_blank>buy warhammer gold</A> <A href="http://www.bestwarhammer.com/" target=_blank>warhammer gold</A> <A href="http://www.bestwarhammer.com/news/news.html" target=_blank>buy warhammer gold</A> <A href="http://www.aocsale.com/" target=_blank>aoc gold</A> <A href="http://www.aocsale.com/aoc-news/" target="_blank">http://www.aocsale.com/aoc-news/" target=_blank>age of conan gold</A> <A href="http://www.game4power.com/" target=_blank>wow gold</A> <A href="http://www.game4power.com/news/" target="_blank">http://www.game4power.com/news/" target=_blank>buy wow gold</A> <A href="http://www.wowgoldone.com/" target=_blank>wow gold</A> <A href="http://www.wowgoldone.com/" target=_blank>buy wow gold</A> <A href="http://www.game4power.com/" target=_blank>world of warcraft gold</A> <A href="http://www.wowgoldone.com/" target=_blank>world of warcraft gold</A> <A href="http://www.gamelevelup.com/" target=_blank>wow power leveling</A> <A href="http://itemstores.com/" target=_blank>wow item</A>

# Posted By warhammer gold | 11/20/08 12:54 PM