Now we want to create a report based off of this query. These reports are available for download, but we will also walk through creating the reports. 

We first step through the wizard and make a connection using SQL Native client to the database on the server. When presented with the objects of your database in the explorer, navigate to the view we just created and select that.

In the wizard, select the following fields to display: Forum, threadid, FirstName, LastName, TotalThreads, and Body. When prompted for the grouping, select the Forum field as the first grouping. Then select the threadid as the second grouping.  Do not select any of the sum fields for this report.

Do not select the filter fields for now. Finally, select the teal report form. Once you have selected the report format, the wizard deposits you on the design area. The report should look similar to Figure 1.

Figure 1

To clean up this report we need to make a few changes. First, in grouping 1 we move the Group name over, and add a text box. In the Textbox add the text "Forum."  Now expand the "Group #1 Name" field so that it covers the width of the report. Also expand the height of the group 1 group so that there is some room on top to differentiate it from the other forum groups. Feel free to add color and a different font size the different fields. Add a line at the bottom of this grouping.

For grouping 1, we add a textbox saying "Thread." The field next to it (Group #2) needs to be deleted. Here is where we use the FirstSubject field. Simply drag the FirstSubject field from under the "Database Fields" in your field explorer beside the "Thread" textbox.  Also, add another textbox to this grouping after the FirstSubject field. Name this "Count."  Next to this drag the field from field explorer "TotalThreads." 

Now expand the height of this grouping. Add a line to this grouping. Below the line, put a textbox with the text "Name" over the First and LastName fields. Add another textbox with the text "Response Snippet" in it over the body field. Get rid of the FirstName, LastName and Body textboxes at the top of the report.

Also at the top of the report, add the name of the report in a textbox. We have imaginatively named our report "Forum Report Summary." You may want to stick with that flashy name or come up with a more suitable one on your own. Try to format this in a way that works for you. See Figure 2 for an idea of how the downloadable report looks.

Figure 2

Some other things that could be done with this report include adding a grouping by the First and LastName. To do this, simply add a new formula field that has both the Last Name and First Name concatenated together. Then simply add this grouping to the top of all groups. You can then add some running totals to view how many contributions each person has.