Putting It All Together
Now that I have the entire framework complete it's time to start putting it all together. Initially, I need to manually populate the database with my base report information. The first table I'll populate is the Report table. When I insert the SQL string into the database, I'll replace the entire WHERE clause with the ~CLAUSE placeholder (Figure 8). After I enter the report, I'll need to write the id of the newly created report down.

SELECT
  C.name AS Customer
 ,P.name AS Product 
 ,E.firstName + ' ' + E.lastName AS SalesPerson
 ,J.quantity
 ,S.saleDate
 ,P.price
 ,P.price * J.quantity AS TotalCost
 ,S.id
FROM
	Customer C
INNER JOIN
	Sale S ON S.customerId = C.id
INNER JOIN
	JoinSaleProduct J ON J.saleId = S.id
INNER JOIN
	Product P ON P.id = J.productId
INNER JOIN
	SalesPerson E ON E.id = S.salespersonId
~CLAUSE
ORDER BY  S.id

Next, I'll enter the columns for the report in the ReportColumn table keeping in mind that they must be entered exactly as they appear in the SELECT statement of my query. This is where I'll use the id I wrote down when inserting data into the Report table. For fields that are concatenated such as firstName + ' ' + lastName AS Name the entire statement must be entered. The column friendly name can be anything that will be easy for the end users to understand.

To build the dynamic WHERE clause I'll enter each part of the WHERE clause into the ReportColumnClause table with the corresponding ID value from the ReportColumn table. The order the records are entered into this table is essential. The first record entered will be the first WHERE clause and so forth. This is especially important when entering OR operators. If they are not entered in order you will not get the results you expect from the query.

The main report page is simply a listing of all the reports in the Report table with a link to customize each. It should be split up into two sections, baseline reports and customized reports. Since baseline reports can only be cloned I'll take away the edit and delete icons from their menu entirely.

When a user clicks on the clone report screen a few things need to happen behind the scenes before they are shown the customize report screen. First a report object must be created and initialized to represent the report being cloned. Next two arrays must be created, one for the columns in the report and another for the clauses of the report.

The customize report page should give the user the name of the report they are cloning, a field for the name of the new report, and a field for the report description. As I mentioned previously I like to give the user a question to describe the report so I labeled the field "Hint Question." Next I loop through the clause array and create fields for the user to modify the properties of the original report. Since these are the criteria that define the original report I'll only give the user the ability to change the values of the field. After the fields that define the original report are populated I'll give users a few more fields to customize themselves.

To add the new report to the database, two components must be created: a Report component that represents the original report and an empty Report component to store the modified report. Once that's done, since the SQL string itself is not being edited I copy the SQL string from the original report to the new report. Next, I loop through the columns of the original report and call the AddColumn() method of the new Report object to add the column to the new report. The AddColumn() method returns the id of the new column that is associated with the new report. I'll pair the new column id to the id of the column from the original report in a two dimensional array. This is important because we have not inserted the clauses for the new report yet. The clauses submitted in the form are still attached to the column id's of the original report. When I add the new clauses I want to use the column id's of the new report.

The last step is to loop through the clauses submitted and add the clauses to the database. First I need to loop through the columnIdArray to match the column id to the column submitted from the form and attach the new id to that column. Next I call the AddClause() method on the new report object making sure to insert the newly created column ids.

In Conclusion
Now every report created can be modified by each user to meet their needs. Of course a security model should be wrapped around this functionality to make sure only approved users can modify, edit, and delete reports. This can be taken a step further by implementing a role-based security model to define report data readers, report editors, or to limit report access to specific users.

After the initial time investment of teaching users to clone, edit, and delete reports, support calls for the creation of custom reports should be greatly reduced, with the added benefit that your reports will look great on screen and in print. To see this application in action visit www.innovacreative.com/cfdj/. The code for this application can be downloaded at www.innovacreative.com/cfdj/cfdj.zip.

• Figure 2
• Figure 6
• Figure 7