This article will start out creating a Summary report to show
all the blogs on a portal. In this report we will set up to filter by the
portal, then display the blogs that are available for that portal. To create
that summary let us first start with our SQL statement. As in the forum tables,
the blog tables are clearly named, prefixed with blog_...
Our SQL statement creates a view to base the report off. See
Listing 1 to view the Tsql. We utilize the tables Blog_Blogs, Blog_Entries, and
Blog_Comments. The only other blog table is the blog_settings table, which can
be utilized for some admin type reports.
For this summary report we also want to use the Portal table
so we can filter by different portals. This gives flexibility if you have
multiple sites to monitor. Along with the Portal table, we add the User table so
we can identify the blogger. For display purposes, this query concatenates the
first and last name. This could also be handled by including those fields
separately and then concatenated in Crystal.
Listing 1
create view vwblogs_blogSummarry
as
select bl.Title
,bl.ShowFullName
, prt.PortalName
, ble.Title as 'Entry'
, ble.Published
, ble.AddedDate
, (select count(*)
from dbo.Blog_Comments bc
where bc.EntryID = ble.EntryID)
as 'NumberComments'
, us.FirstName + ' ' + us.LastName as 'Blogger'
from dbo.Blog_Blogs bl
inner join dbo.Portals prt on (bl.PortalID = prt.PortalID)
left outer join dbo.Blog_Entries ble on (ble.BlogID = bl.BlogID)
inner join dbo.Users us on (us.UserID = bl.UserID)