In Part 1 of this series, we went over an introduction to DotNetNuke (DNN). We also delved into table structure, then some basic SQL Statements. As mentioned in the first part, many people are using DotNetNuke (DNN) as their option as a CMS application. DNN has grown into one of the most popular open source frameworks for Portals in the .NET space.

In this series of articles we will go over how to create a few different custom reports using DNN.  In this article we will focus on creating reports for forums and access reports. For a brief overview of DNN, see article 1 and the DotNetNuke site.   

·         Visual Studio 2005

·         Crystal Reports XI

·         DotNetNuke 4.5.4

·         SQL Server 2005

We' wil begin by creating a Summary report for the Forums.

Listing 1

create view vwforums_ForumSummarry
as
select distinct fp.[Subject]
      , substring(fp.[Body],1,4000) as 'Body'
      , ff.[Name] as 'Forum'
      , ft.replies + 1 as 'TotalThreads'
      , ft.replies
      , us.Username
      , us.FirstName
      , us.LastName
      , ft.threadid
      , fp.postid
      , (select fp1.subject
            from Forum_Posts fp1
            where fp1.postid = (select min(fp2.postid)
                                                from Forum_Posts fp2
                                                where fp2.threadid = fp.threadid)
            ) as 'FirstSubject' 
from dbo.Forum_Posts fp
 inner join dbo.Forum_Threads ft on (ft.threadid = fp.ThreadID)
 inner join dbo.Forum_Forums ff on (ff.ForumID = ft.ForumID)
 inner join dbo.Users us on (us.UserID = fp.UserID)

We have changed this query from the original in Part 1.  We have added the threadID and the postID to allow us to do appropriate ordering and grouping for the report. We also now have a subquery that returns the first subject from the table. This way, in our report we will have the first subject without the RE: in the name.

We also made this into a View. That way, when we need to add any fields or tables, we can change the view. In Crystal, using the field explorer window and selecting the database object, you can use Verify Database to automatically update the fields. This makes changing management for the data source much easier to handle than using updating the data source.

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.

[Download Sample]

In this article we have used some of the initial SQL from Part 1 and created a report on forums using that. This included making modifications to the original SQL statement to get the correct data from our tables. After that, we designed a report to display some summary information from the forums.