Creating Custom Reports for DotNetNuke using Crystal Reports - Part 3
page 3 of 8
by Eric Landes
Feedback
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 32188/ 67

Creating a Summary Report for Your Portals Blogs

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)

View Entire Article

User Comments

No comments posted yet.

Product Spotlight
Product Spotlight 



Community Advice: ASP | SQL | XML | Regular Expressions | Windows


©Copyright 1998-2024 ASPAlliance.com  |  Page Processed at 2024-03-28 2:07:19 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search