ColdFusion Report Builder 9 Using MySQL query's

Report · Nov 19, 2012

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.

MySQL

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,
address.e_mail

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.

good

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

query.Start_Time = '12.00'

</cfif>

or

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

{

query.Start_Time = '12:00';

}

</cfscript>

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

Thanks

Shawn

Report · Nov 20, 2012

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:

http://forums.adobe.com/community/coldfusion/coldfusion_reporting

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.

/charlie

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.

/Charlie (troubleshooter, carehart. org)