Creating Custom Reports for DotNetNuke Using Crystal Reports - Part 1
page 5 of 7
by Eric Landes
Feedback
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 36372/ 104

Creating a SQL query for Forums

We will end this article by creating a query to report on forum users. For this report, we will create something that allows us to view how many posts someone has and drill down to the forums this would be on.

To create that query, we will need to use the following tables: Forum_Posts, Forum_Threads and Forum_Forums. To discover which users, we will need to add the Users table. 

Listing 1

select fp.[Subject]
      , substring(fp.[Body],1,4000) as 'Body'
      , ff.[Name] as 'Forum'
      , (select count(*) from dbo.Forum_Threads ft2
            where ft2.threadid = fp.threadid) as 'TotalThreads'
      , ft.replies
      , us.Username
      , us.FirstName
      , us.LastName
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)
 inner join dbo.UserRoles ur on (us.UserID = ur.UserID)
 inner join dbo.aspnet_Roles aspr on (ur.RoleId = aspr.RoleId)

This query should show us information on the forums, such as how many replies there are for each post.  Also, we include the basic information on the person who posted. Things like email and even the role of the user can be included.  The last part would need another table added to your code.  See listing 2 for an example of how this would look.

Listing 2

select fp.[Subject]
      , substring(fp.[Body],1,4000) as 'Body'
      , ff.[Name] as 'Forum'
      , (select count(*) from dbo.Forum_Threads ft2
            where ft2.threadid = fp.threadid) as 'TotalThreads'
      , ft.replies
      , us.Username
      , us.FirstName
      , us.LastName
      , aspr.RoleName
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)
 inner join dbo.UserRoles ur on (us.UserID = ur.UserID)
 inner join dbo.aspnet_Roles aspr on (ur.RoleId = aspr.RoleId)

In the next part of this series we will take these queries and put them into a Crystal report.


View Entire Article

User Comments

No comments posted yet.

Product Spotlight
Product Spotlight 



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


©Copyright 1998-2019 ASPAlliance.com  |  Page Processed at 2019-04-23 10:35:10 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search