The next screen to pay attention to is the Subreport Binding screen (see Figure 5). This screen is where I am going to bind the #param.id# variable from our SQL query to the main report. In the Parameter Name field I enter the value "id." The value drop down will gives me a list of columns defined in the main report. Since the I am looking for is the product id, I'll select that from the dropdown list as well. The next screen is the Subreport filename screen. Since this is a subreport of the Total Sales by Product report I'll call this totalSalesByProduct_subreport.cfr. The next and final screen is the Finish subreport setup screen, click "Finish" to add the subreport to the report. Since this subreport is just a report, it is possible to make this user configurable as well, however for this article I'll stick to configuring the main reports only.

That's all the work I'll have to do in the Report Builder for this report. This report file will be the same file used for each report that is created by customizing a base report. I'll save this report to a Reports folder in my web root folder and make sure that I have the name of the file written down somewhere (I'll need it to populate the database later).

Now that I have my first base report done, I want to provide my users the means to modify the output of the report. To accomplish this I need to do a few things:

1. Give the user a list of reports that they can modify.
2. Give the user friendly names for the columns used in the report.
3. Build the WHERE clause dynamically.
4. Give the user ability to apply AND/OR operators within the WHERE clause
5. Give the user the ability to add multiple WHERE clauses to a single column.
6. Make it easy for users to create and save their own reports.

To accomplish this it's clear that I'll need to create a database to store and manage the data for the reports. A database driven approach will give me the flexibility I need to create user modifiable reports. Based on the criteria I'll need to create a Report table to store unique information for each report, a Report Column table to store the column names and a Report Column Clause table to store the data that will be used to build the dynamic WHERE clause.

Creating the Database
The Report table needs to store the name of the report, a description for each report (I like to enter a question in this field to help users identify the report), the SQL statement used to get data from the report, the location of the report template file, and whether or not this report is a baseline report. Remember, the baseline reports can only be cloned and not updated or deleted.

Another key point to make here is that when entering the SQL statement into this table the entire WHERE clause should be replaced with a place holder string. I'll demonstrate this a little later when I populate the tables.

The Report Column table stores the column name exactly as it was entered in the SELECT statement of the SQL Query, a friendly name for the column, and a boolean value that specifies whether data type for this column is a string or not. This field determines whether to add single quotes to the data when populating the ReportColumnClause table.

The ReportColumnClause table holds the operator for the clause, the data for the clause itself, and whether the clause is an AND/OR statement. Be careful when entering data in this table because the first clause entered for each report must have a blank AND/OR field in order to parse correctly.

Creating the Report Component
After the database is done I need to create a component to control the report. I'll need to store the report id, name, description, SQL string, template file location, a list of columns, a list of clauses, a boolean value specifying if this is a baseline report, and the DSN for this report in private component level variables. I'll create and initialize them in my pseudo constructor: the Init() method (see Figure 4).

The init will have two arguments: id and DSN. The id is not required and is defaulted to 0. If we are creating an instance of an existing report an id will be passed in, if we are creating an empty instance of this component no id, or an id of 0 is passed in. If the id is not 0 I know this is an instance of a report and I'll call the GetReportInformation() function to retrieve the report data from the database.

This component needs public functions to create a new report, save the current report to the database, get a list of columns available to the report from the database, get all the clauses used to build the report, add a new column to the columns table (used to generate a new column id by cloning an existing column), add a new clause to the database, get a list of all available reports, and return the dynamic query that will be passed into the database.

The columns available to the report are stored in a structure that emulates the database structure. The clauses used to build the WHERE statement are stored the same way (see Figure 5).

The last step in building the component is to expose the private component level variables by writing getter and setter methods. Since the template file, SQL string, column array, clause array, and baseline variables are all read only, only getter methods should be written for them. The column and clause arrays are read only since they will be modified one element at a time using the AddColumn and AddClause functions.

• Figure 2
• Figure 6
• Figure 7