0

I have a question regarding cfspreadsheet....So I'm using cfspreadshseet to create excel spreadsheets for reporting purposes. My page allows a user to select whatever columns from the database to include in the report. So here is an example:

The spreadsheet could look like this:

First Name---Last Name---Organization---Address---City---State---Zip---Concern

Joe Smith Sample 12 main denver co 80513 concerns go here

My question is this, if Joe has more than 1 concern I get multiple rows with joe's info...is there a way I can loop the concerns and only have 1 row for joe?

Thanks,

Steve

3 Answers3

2

You need a unique row ID to do this safest, the outer group working with lastname, or something, could cause conflict. UserID is a placeholder variable. Make sure to replace it with an accurate ID name. Of course, a few of these variable names are just guessed.

<cfoutput query ="thequery" group="UserID">
  <cfset cList="">
  <cfoutput group="concern">
    <cfset cList=ListAppend(cList,Concern)>
  </cfoutput>
  <cfset temp = spreadsheetAddRow(my_spreadsheet,"'#fn#','#ln#',...,'#cList#'">
</cfoutput>
Regular Jo
  • 5,190
  • 3
  • 25
  • 47
  • +1. Just note that "group" requires the query result be sorted the same way, or it will not work correctly. So if you are grouping by "UserID", be sure to `ORDER BY UserID` in your db query. – Leigh Oct 19 '14 at 17:32
2

Using the "group" feature of cfoutput is perfectly fine for this task. Just to throw out another possibility, you could also generate the list within your database query.

For example, MySQL has the GROUP_CONCAT function. I do not know the structure of your tables, but say you have two tables User and UserConcern, you could use GROUP_CONCAT like so to concatenate the "Concern" values into a single string:

SQLFiddle

SELECT
     u.UserID
     , u.FirstName
     , ... other columns
     , GROUP_CONCAT( uc.Concern ) AS ConcernList
FROM UserTable u INNER JOIN UserConcern uc
         ON uc.UserID = u.UserID
GROUP BY 
     u.UserID
     , u.FirstName
     , ... other columns

For SQL Server, a standard trick is to use XML Path:

SQLFiddle

SELECT
     u.UserID
     , u.FirstName
     , ... other columns
     , STUFF( ( SELECT ',' + uc.Concern
                FROM  UserConcern uc
                WHERE uc.UserID = u.UserID
                ORDER BY uc.Concern
                FOR XML PATH('')
               ) 
              , 1, 1, ''
          ) AS ConcernList
FROM UserTable u
GROUP BY 
     u.UserID
     , u.FirstName
     , ... other columns

Then simply generate your spreadsheet as usual.

Leigh
  • 28,765
  • 10
  • 55
  • 103
  • Leigh, I got this to work, I choose this method because I have about 4 other tables like this that could be included in my report so doing it through the query was a good choice. thanks for the response. – Artisdesigns Oct 20 '14 at 13:27
0

Assuming you want separate lines for each comment, something like this would work:

<cfset current_id = "">
<cfloop query = "my_query">
    <cfset next_id = user_id>
    <!--- or whatever else forms the primary key --->
    <cfif next_id neq current_id>
        <cfset current_id = next_id>
        <cfset SpreadsheetAddRow(my_spreadsheet, "#first_name#,#last_name#,etc, #comment#">
    <cfelse>
        <cfset SpreadsheetAddRow(my_spreadsheet, ",,#comment#">
    </cfif>
</cfloop>

This is based on the information provided. If you have a unique ID the group attribute would work better.

Leigh
  • 28,765
  • 10
  • 55
  • 103
Simon Fermor
  • 116
  • 1
  • 11
  • I'm looking to list the concerns in one cell in an excel spreadsheet...so in the example above, Joe would have one line in the excel spreadsheet but if Joe has more than 1 concern, they would be listed like concern1,concern2,concern3 in one cell – Artisdesigns Oct 19 '14 at 04:36
  • See answer by cfqueryparam for use of listAppend. – Simon Fermor Oct 19 '14 at 13:57