Skip to main content
Inspiring
February 25, 2007
Answered

Report Builder Input Parameters

  • February 25, 2007
  • 1 reply
  • 476 views
I need to create a report using input parameters and a date range. I am calling the report from a form that is using the following query and gets its input from a previous form:

<cfquery name="getVisitors" datasource="zipcodesearch">
SELECT company.CompanyID, company.CompanyName, company.phone1, visitors.visitorName, visitors.visitorComp, visitors.createDate, visitors.phone, visitors.email
FROM company INNER JOIN visitors ON company.CompanyID = visitors.companyID
WHERE visitors.createDate>=#form.startdate# And visitors.createDate<=#form.enddate#
ORDER BY company.CompanyName, company.phone1, visitors.createDate
</cfquery>

At this point, I am just not sure if I should use cfreportparam tags or not. The reason is, I am not using an = operator. I need to specify a date range and I can't find an example ANYWHERE on how to accomplish that with the Report Builder. If I do use the cf report param tags, would they look something like this?:

<cfreport format="PDF" template="visitors.cfr" query="#getVisitors#" overwrite="yes">
<cfreportparam NAME="startdate" VALUE="#form.startdate#">
<cfreportparam NAME="enddate" VALUE="#form.enddate#">
</cfreport>

I guess my next question is, do I then need to specify a query within the report builder and also use input parameters within the report builder environment? the documentation on this type of scenario has been virtually non-existent.

I'm really stuck on this one and any help would be appreciated...
This topic has been closed for replies.
Correct answer ssailer
I figured it out and am replying to my own post in case anyone else runs into the same type of situation....

The input form consists of two fields: StartDate and EndDate

The action page consists of a query as follows:
<CFSET FROMDATE=CreateODBCDate("#form.startdate#")>
<CFSET TODATE=CreateODBCDate("#form.enddate#")>

<cfquery name="getVisitors" datasource="#db#">
SELECT company.CompanyID, company.CompanyName, company.phone1, visitors.visitorName, visitors.visitorComp, visitors.createDate, visitors.phone, visitors.email
FROM company INNER JOIN visitors ON company.CompanyID = visitors.companyID
WHERE visitors.createDate>=#FROMDATE# And visitors.createDate<=#TODATE#
ORDER BY company.CompanyName, company.phone1, visitors.createDate
</cfquery>

...and the report builder code:
<cfif #getVisitors.recordcount# EQ 0>
<P align="center">No records for date(s) chosen.
<P align="center"><a href="javascript:window.close();">Close Window</a>
<cfelse>
<cfreport template="visitorsForDate.cfr" format="pdf" query="#getVisitors#" overwrite="yes">
<cfreportparam NAME="startdate" VALUE="#FROMDATE#">
<cfreportparam NAME="enddate" VALUE="#TODATE#">
</cfreport>
</cfif>

I also removed the underlying query within the report builder program and am only using the query on the ColdFusion page itself. Everything appears to be working correctly, so I am going to put this down as the answer.

1 reply

ssailerAuthorCorrect answer
Inspiring
February 26, 2007
I figured it out and am replying to my own post in case anyone else runs into the same type of situation....

The input form consists of two fields: StartDate and EndDate

The action page consists of a query as follows:
<CFSET FROMDATE=CreateODBCDate("#form.startdate#")>
<CFSET TODATE=CreateODBCDate("#form.enddate#")>

<cfquery name="getVisitors" datasource="#db#">
SELECT company.CompanyID, company.CompanyName, company.phone1, visitors.visitorName, visitors.visitorComp, visitors.createDate, visitors.phone, visitors.email
FROM company INNER JOIN visitors ON company.CompanyID = visitors.companyID
WHERE visitors.createDate>=#FROMDATE# And visitors.createDate<=#TODATE#
ORDER BY company.CompanyName, company.phone1, visitors.createDate
</cfquery>

...and the report builder code:
<cfif #getVisitors.recordcount# EQ 0>
<P align="center">No records for date(s) chosen.
<P align="center"><a href="javascript:window.close();">Close Window</a>
<cfelse>
<cfreport template="visitorsForDate.cfr" format="pdf" query="#getVisitors#" overwrite="yes">
<cfreportparam NAME="startdate" VALUE="#FROMDATE#">
<cfreportparam NAME="enddate" VALUE="#TODATE#">
</cfreport>
</cfif>

I also removed the underlying query within the report builder program and am only using the query on the ColdFusion page itself. Everything appears to be working correctly, so I am going to put this down as the answer.