1 Reply Latest reply on Nov 20, 2012 10:34 AM by charlie@carehart.org

    ColdFusion Report Builder 9 Using MySQL query's


      Hey Everyone


      I have been pulling my hair out for several days with this, I have all of the Adobe ColdFusion 9 dev books, and Google returns a cluster of information that doesnt seem to work. I was hoping someone could help me out here.


      So what I did was open query builder in Adobe ColdFusion 9 Report Builder, I clicked the icon at the top that says report query and placed the following mySQL information, which works fine.



      select name.soc_sec, name.last_name, name.first_name, nmcrs.course,nmcrs.crs_txt AS Course_Title ,practicl.day_cod,CAST(practicl.start_hr AS VARCHAR(2)) + ':' + CAST(practicl.start_min AS VARCHAR(2)) AS Start_Time,
        CAST(practicl.end_hr AS VARCHAR(2)) + ':' + CAST(practicl.end_min AS VARCHAR(2)) AS End_Time, nmcrs.section, nmcrs.sch_yr, nmcrs.sm_desc, nmattend.attend_txt,address.phone,address.cell_phone,

      From name
        --INNER JOIN  nmprg ON name.soc_sec = nmprg.soc_sec  
              INNER JOIN  nmcrs ON name.soc_sec = nmcrs.soc_sec
              Left Join crssect ON name.soc_sec = crssect.crs_id
        Inner Join practicl On practicl.sid = nmcrs.sid
              inner join nmattend on name.soc_sec = nmattend.soc_sec
              Inner join  address On name.soc_sec = address.soc_sec
      WHERE (name.camp_cod = 'California') AND (nmcrs.sch_yr = '201213')and (nmcrs.semester = '1') and (nmattend.attend_txt = 'SP') and
        (nmcrs.section not like'w%')

      group by name.soc_sec, name.last_name, name.first_name,nmcrs.course,nmcrs.crs_txt,practicl.day_cod,practicl.start_hr,
         practicl.start_min, practicl.end_hr,practicl.end_min,nmcrs.section, nmcrs.sch_yr,nmcrs.sm_desc, nmcrs.grade,
         nmattend.attend_txt, address.phone,address.cell_phone,address.e_mail
      ORDER BY nmcrs.section, name.soc_sec



      Now I place my fields where I want them in the desgner, I double click the field to open the expression builder and all I have to type is something like query.Start_Time

      When I run the report it lists all of the start times that match my criteria.




      So my trouble is, I would like to use an if statement to make something happen based on the start time and cant get it to work. I have tried using <cfscript> <cfif> <cfquery> if, IIF, nothing works, mainly because once I use one of those tags it no longer recognizes the query.Start_Time.


      I was wondering if someone could give me an example on how to do this like


      <cfif (query.Start_Time) IS '12:0'>

      query.Start_Time = '12.00'






      if(query.Start_Time == '12:0')


           query.Start_Time = '12:00';




      I mean I know thats wrong and Its probably not that simple but all I need is a simple example to work off of and one would be greatly appreciated. Or even an example in the mySQL info I provided would work too. Please Help




        • 1. Re: ColdFusion Report Builder 9 Using MySQL query's
          charlie@carehart.org Adobe Community Professional & MVP

          Shawn, forgive me not focusing on the direct question, but I want to clarify first that this is not the right forum for that question, about the CF Report Builder. This forum is for CF Builder, the relatively new CF IDE (integrated development environment) for CF, based on Eclipse.


          Your question about the CF Report Builder should be directed to its own forum, the CF Reporting forum:




          I do understand the easy confusion (the name CF Builder is awfully close to the CF Report Builder). Also, the forum name “CF Reporting” may not have jumped out as a place for RB questions. (For any readers who may be interested, the list of all CF-related forums is at http://forums.adobe.com/community/coldfusion)


          That forum is not as busy, no, but then the Report Builder is quite a bit older, having come out with CF 7, and not really having been updated much since then.  Still, there are folks there who have replied to questions asked, including Adobe staff.


          Since I don’t read that forum myself, and won’t see your question asked/answered there, let me point out something else that may help (in case others there would not).  You say “I have all of the Adobe ColdFusion 9 dev books”, and that may mean various books like the CF Web App Construction Kit books that Ben, Ray, myself, and others have done.


          But did you know that there is an entire chapter on the CF Report Builder in the free online CF9 manual called “Developing ColdFusion 9 Applications” (a 1300-page manual which many miss entirely , as happened with the versions for CF8 and 7 as well). Many people never even know there is any such “documentation” from Adobe for working with the CF Report Builder. I’ll note also that we (writers of books) may not always get into all the detail that’s in those docs because, well, we don’t want to duplicate their effort. But I know even some writers/bloggers/forum respondents never even know of the manuals, as they are rarely referred to.


          In your case, with the CFRB, it’s especially important to be aware of its substantial chapter:


          http://help.adobe.com/en_US/ColdFusion/9.0/Developing/WSc3ff6d0ea77859461172e0811cbec22c24 -6fc2.html


          For instance, you may already know about this but there you’ll learn that there is a QUERY attribute for the CFREPORT, whereby you can build the query dynamically (as any CFQUERY can be) and then pass that resultset to the report. That may suffice to solve the challenge for you, but if it does not, then you may want to at least take a moment to peruse that chapter before proceeding to ask the question in that other forum.


          And if the book doesn’t help (and I grant it may not) them one more suggestion before you may re-post the question on the other forum: it really may be best for you not to make reference to MySQL or CF9 in your subject, as you did here. There’s doesn’t really seem anything about your challenge that’s specific to them, but saying it in your subject may limit who would read/reply (if they don’t use with MySQL or are not yet on CF9).


          Hope something there’s helpful.




          PS If you may feel that this answers the question, please do mark it as such in the forum web interface, if you would please. Thanks.