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.
