Let us go over some of the schema information for CS.  First we will go over the naming conventions used in the CommunityServer database.  In CS, the database layout appears to follow the naming convention of prefacing each table with "cs_."  For instance, the table containing posts is named cs_posts.

Figure 1

There are a few tables without the cs_ prefix that begin with the aspnet_ prefix.

The tables our report is concerned with are the table cs_Posts and the table cs_Sections.  If we would need more data about the folks posting, then we would use tables cs_Users and aspnet_users.  Cs_Users provides the foreign key relationship from cs_Posts to user information in aspnet_users using the guid key.  Some other tables that might be useful are cs_postsrating and cs_postsarchive to see what the ranking of the posts are and any history.

The purpose of our report is to show posts that do not have responses.  The scenario for our implementation of CS is a support forum for our product.  This report should help us monitor our support forums and make sure they are providing answers to our customers.  There may be tweaks that can be made to provide more pertinent information to this report, but the purpose of this is to show the tables used in a report created for Community Server as the application.

To create the report, we first create a new stored procedure that will return the posts information, the number of replies for each post.  We also want to make sure that the post we are showing is the original post and not a reply.  To do that, in cs_posts, the threadid field can be joined in a subquery with the postid.  This builds a field that indicates if this is the original post or a reply. See code listing 1 for the stored procedure that is used to create this.

Listing 1

Create Procedure pr_get_post_thread_info
as
select cp.Subject,
      substring(cp.Body,1,250) as 'Text',
      cp.PostDate,
      (select count(*)
            from dbo.cs_Posts cp3
            where cp3.ThreadID = cp.ThreadID) as'NumberReplies',
      cp.ParentID,
      case when cp.postid = (selectmin(cp2.PostID)
                  from dbo.cs_Posts cp2
                  where cp2.ThreadID =cp.ThreadID)
                  then 1
            else 0
            end as 'IsOriginal',
      cp.PostAuthor,
      css.[Name],
      cp.ThreadID,
      cp.SectionID,
      cp.UserID
 
 
from dbo.cs_Posts cp
      inner join dbo.cs_Sections css on(css.SectionID = cp.SectionID)
 
go
grant execute on pr_get_post_thread_info topublic
go

With this stored procedure, we'll create the report to display what posts do not have replies.