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.
