Skip navigation
fs22
fs22
Currently Being Moderated

Report Looping

Feb 25, 2008 1:39 PM

Greetings,

I am trying to loop over a query to provide all of the information retreived from a query in an excel format. All I am receiving back is the last record. I would appreciate any thoughts. Here is my code...

<cfreport format="excel" query="PCR" template="PCRReport.cfr">
<cfloop query="PCR">
<cfreportparam name="CustomerNumber" value="#PCR.CustomerNumber#">
<cfreportparam name="BusinessType" value="#PCR.BusinessType#">
<cfreportparam name="SoftwareLevel" value="#PCR.SoftwareLevel#">
<cfreportparam name="SoftwareProduct" value="#PCR.SoftwareProduct#">
<cfreportparam name="ProgramName" value="#PCR.ProgramName#">
<cfreportparam name="Affiliation" value="#PCR.Affiliation#">
</cfloop>
</cfreport>

Thoughts?
1082 Views   4 Replies   Latest reply: sherrimc99, Oct 7, 2009 5:48 AM
 
Replies
  • fs22
    Currently Being Moderated
    1. fs22,
    Feb 26, 2008 3:17 PM   in reply to fs22
    Report
    Since there is no response...am I to assume that there is not a good way to loop through a query for an excel report output?
     
    |
    Mark as:
  • YogeshM
    Currently Being Moderated
    2. YogeshM,
    Feb 26, 2008 10:38 PM   in reply to fs22
    Report
    Hi,

    One question, do you need to open EACH of the excel files generated by each loop in the browser?

    Alternatively, what you can do is:

    1. Set a path where you can save the report
    E.g. <cfset tmp_path = "C:\Documents and Settings\YourName\Desktop\Reports\">

    <!--- Check if directory exists, else create it (Verify first that you have the appropriate permissions for that) --->
    <cfif not directoryExists(variables.tmp_path)>
    <cfdirectory action="create" directory="#variables.tmp_path#">
    </cfif>

    2. Loop through your recordset
    <cfloop index="x" from="1" to="#PCR.recordcount#">

    3. Set a filename for each of the excel files that will be generated.
    E.g. <cfset output_file = variables.tmp_path & "PCR_Excel_" & variables.x & ".xls">

    4. Save each of your files
    <cfreport format="excel" template="PCRReport.cfr" filename="#variables.output_file#" overwrite="yes">
    <cfreportparam name="CustomerNumber" value="#PCR.CustomerNumber[x]#">
    <cfreportparam name="BusinessType" value="#PCR.BusinessType[x]#">
    <cfreportparam name="SoftwareLevel" value="#PCR.SoftwareLevel[x]#">
    <cfreportparam name="SoftwareProduct" value="#PCR.SoftwareProduct[x]#">
    <cfreportparam name="ProgramName" value="#PCR.ProgramName[x]#">
    <cfreportparam name="Affiliation" value="#PCR.Affiliation[x]#">
    </cfreport>

    5. End your loop
    </cfloop>

    6. Then, all you need to do is to go to your Reports folder and get your files.

    Hope this helps.

    Regards,
    Yogesh
     
    |
    Mark as:
  • fs22
    Currently Being Moderated
    3. fs22,
    Feb 28, 2008 3:10 PM   in reply to fs22
    Report
    I am only trying to generate one Excel file with multiple records outputted to it.
     
    |
    Mark as:
  • sherrimc99
    Currently Being Moderated
    4. sherrimc99,
    Oct 7, 2009 5:48 AM   in reply to fs22
    Report

    It is my understanding that CFReport will accpet only one value for a given named parameter.  In your loop over query results you are essentially resetting the values for your named paramters so it stands to reason that only the last values would show up.  Why not write your query inside CFReport?  Then you can name the returned fields as query parameters, place them in the detail section, and they should all be displayed there.

     

    Maybe I am oversimplifying the situation.  If so, I apologize.

     
    |
    Mark as:
Actions

More Like This

  • Retrieving data ...

Bookmarked By (0)

Answers + Points = Status

  • 10 points awarded for Correct Answers
  • 5 points awarded for Helpful Answers
  • 10,000+ points
  • 1,001-10,000 points
  • 501-1,000 points
  • 5-500 points