Although I lose the dynamic properties of the first report by using static date values in the values within my "WHERE" clause it becomes much easier for a user to change this report to fit their needs. And, since users can modify this report, it still won't need to be changed by a developer when the next year comes around.

Now that I have my report query written I'll open the Report Builder and build my report. The first step in building my report is to create a new blank report. After I have my blank report I need to manually add my columns to the report. To do this I'll run a query analyzer application side by side with the report builder to make sure that the columns I am manually adding match the Query exactly (see Figure 1).

After I add all the columns in my query I'll use the Report Wizard to build the report layout. This can be found under the Report -> Report Wizard for this report menu and can be run anytime. Doing this will overwrite any layout you are currently working on. After the report is built using the wizard, I create any calculated fields for the report. In this case I'll create two calculated fields, one for the total sales for a sales person, and another for the grand total of sales. I'll also create an input parameter for the name of the report with the default value of "Year To Date Sales by Salesperson." This will allow users to specify a new name for their customized report which will be passed in at runtime. The next step is to replace the report title the wizard generated with the input parameter. The pre-defined styles come in quite handy here. Instead of choosing a font, font-size, and style, I'll set the "Report Title" pre-defined style which does all three steps in one.

After the Year To Date Sales report is finished, I'll create another baseline report: Total Sales by Product. For this report I'd like to see a summary count of all the sales for each product. In addition, I'd like to see how many of each product each salesperson is selling. In order to do this I'll have to create two queries. A red flag should have just gone off in your head. The Report Builder does not let us pass in two queries but it does let us create subreports.

Subreports are just what the name implies: they are a report within a report. A subreport is an entirely separate ColdFusion report file that is embedded within another report and is the best way to create a report that will need more than one query.

Before I create my subreport I still have to manually add the columns to the main report using the SQL statement that's used to get the data for the report just like I did for my Year-To-Date Sales report. Since I'm going to use a subreport, I'll need to add the product id field to the column list. This column won't be displayed on the report, but its value will be passed to the subreport.

To add a subreport go to the Insert -> Subreport menu. Here you have the option to use an existing subreport, or to create a new one using the report wizard.

For this example I'll create a new report for the subreport. Since this is a subreport and I'm not going to let users modify this I'll use the Query Builder to create the query for this report. Taking the same approach I did before I'll write my SQL Query in a SQL Editing IDE and paste the SQL into the Query Builder.

SELECT
  P.firstName + ' ' + P.lastName AS SalesPerson
 ,SUM(J.quantity) AS Total
FROM
	Salesperson P
INNER JOIN
	Sale S ON S.salespersonId = P.id
INNER JOIN
	JoinSaleProduct J ON J.saleId = S.id
WHERE
	P.ProductId = 1
GROUP BY
P.firstName + ' ' + P.lastName

SELECT
  P.firstName + ' ' + P.lastName AS SalesPerson
 ,SUM(J.quantity) AS Total
FROM
	Salesperson P
INNER JOIN
	Sale S ON S.salespersonId = P.id
INNER JOIN
	JoinSaleProduct J ON J.saleId = S.id
WHERE
	P.ProductId = #param.id#
GROUP BY
P.firstName + ' ' + P.lastName

• Figure 2
• Figure 6
• Figure 7