Creating excel report by manipulating the Query result set

There was a situation where in I had to generate a report of team members of different teams, where in a single record should be there for each team with team member names in the same row. But then the database has 4 records for each team.

It was a bit of a challenge for me. Usually in these situations I would use the group attribute to loop through, but I used this query of queries to solve this.

<cfset filename = expandPath("./teamsReport.xls")>
  
 
  <cfset s = spreadsheetNew()>
  <cfset spreadsheetAddRow(s, "Team Id, Member 1,  Member 2,  Member 3 ,  Member 4 ")>
   
   <!--- Loop through the Query result set--->
  <!--- -Create a query header-->
    <cfset qryListTeam = QueryNew("Team_Id,Member_1,Member_2, Member_3,Member_4","Integer,VarChar, VarChar, VarChar,VarChar") />
    <cfset i=0 />
    <cfset j=1 />

Make a Query Of Queries call which would fetch the  4 records of the team.

      <cfoutput query="teamList" group="TEAM_ID">
      
            <cfset queryAddRow(qryListTeam,1) />
            <cfset querySetCell(qryListTeam,"Team_Id",#TEAM_ID#) />
                <cfinvoke component="tag_report" method="listTeamMembers" returnvariable="teamMemberList">
                      <cfinvokeargument name="team_id" value="#TEAM_ID#" >
                      <cfinvokeargument name="teamList" value="#teamList#">
             </cfinvoke>
      
 Build the  qryListTeam which we had already declared.

        <cfset i = 1 />
      
  <cfloop query="#teamMemberList#" startrow="1" endRow="#teamMemberList.RecordCount#">
                <cfif i eq 1>
                    <cfset querySetCell(qryListTeam,"Member_1",#MEMBER_NAME#) />
                    </cfif>
                <cfif i eq 2>
                    <cfset querySetCell(qryListTeam,"Member_2",#MEMBER_NAME#) /> 
                 </cfif>
                <cfif i eq 3>
                    <cfset querySetCell(qryListTeam,"Member_3",#MEMBER_NAME#) />
                 </cfif>
                <cfif i eq 4>
                    <cfset querySetCell(qryListTeam,"Member_4",#TA_MEMBER_NAME#) />
                    </cfif>
                <cfset i++ />
        </cfloop>
    </cfoutput>

    <!--- format header --->   
     <cfset spreadsheetFormatRow(s,
            {
                bold=true,
                fgcolor="lemon_chiffon",
                fontsize=14
            },
            1)>

Now using the custom built Query result set is ready to be used in our spreadsheet.

  <cfset spreadsheetAddRows(s, qryListTeam)>
  <cfset spreadsheetWrite(s, filename, true)>
    <cfheader name="content-disposition" value="attachment; filename=TeamsReport.xls">
   <cfcontent type="application/msexcel" variable="#spreadsheetReadBinary(s)#" reset="true">