Creating Custom Reports for DotNetNuke Using Crystal Reports - Part 1
 
Published: 25 Jul 2007
Abstract
In this series of articles Eric shows how to create custom Crystal Reports for DotNetNuke web sites.
by Eric Landes
Feedback
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 36417/ 107

Introduction

To create an interactive web site there are many different ways to start.  Many people will use a Content Management System (CMS) that allows users without technical expertise to edit content.  There are different types of CMS applications out there, both commercial and open source.

With the popularity of many different frameworks to create your internet applications in for .NET, that means there are new schemas to learn to report on.  DotNetNuke, an open source option for your CMS app, has grown into one of the most popular open source frameworks for Portals in the .NET space.

In this series of articles we will go over how to create a few different custom reports.  We will discuss how to use DNN as your portal.  After a brief overview of DNN, we will go over how to get information not easily available in DNN onto reports.   

System Requirements

·         Visual Studio 2005

·         Crystal Reports XI

·         DotNetNuke 4.5.4

·         SQL Server 2005

Current Version of DotNetNuke

You can download the current version of DotNetNuke from this link. I became familiar with DotNetNuke when it first was developed from the IbuySpy portal. The early versions gave you a quick way to setup a portal on your web using the new .NET Framework 1.0.  The benefits were a quick way to setup your web site, with the ability to register users baked in; no programming was necessary. Also, there were announcements and the ability to allow end users to edit content, again without programming.

Developers could modify the source code of DotNetNuke to customize it how they wanted.  While it is programmed in VB.NET, C# modules can be created. Developers could create custom modules containing the look and feel of the portal. Changing that look and feel was not an easy change but it could be done site wide. The main power of the original DotNetNuke, I believe was the ability to create separate portals within one DotNetNuke application with different content.  The original IBuySpy would not easily let you do this without modifications to the code.

Originally, DNN supported SQL Server as the backend. As new versions have been created, DNN has created more ways to connect with other data stores. Later versions also make customizing the look and feel of a portal an easier task.

The current DotNetNuke has taken these concepts much further, and in a slicker package.  Besides more modules to choose from in the default package, the design in DNN has progressed also. For developers, they are using a Provider Pattern for data access.  If you need details on that feel free to look at their documentation found here.

The DataBase Schema

For this series of articles, we are assuming a SQL Server backend for your DNN installation.  For this article, let us assume you are using SQL Management Studio to view the tables, views, etc.  With the installed version of DNN, we will look through the different tables.  We will touch on the views and other out of the box SQL objects that DNN automatically installs. First, we want to familiarize ourselves with tables so we can create the SQL Statements for our reports. 

DNN uses an easy, simple to discover naming convention for SQL tables. The tables all begin with the names of their applications. For instance, if you want to report on statistics for your blogs, look in the tables with the prefix Blog_ . Similarly, to report on forums, Forums_ is the prefix for those tables. Keep this in mind for your custom modules that need custom tables. Prefix them in a similar way. Figure 1 shows what these tables look like in SQL Management Studio.

Figure 1

Besides module specific tables, report developers need access to system type tables. For instance, user tables can be necessary for most reports. So if you want the user's full name and other details in your report, you need to join with the user tables.  In DNN there are 2 sets of tables. The Users table contains the UserID, Username, FirstName, LastName, IsSuperUser, AffiliateId, Email, DisplayName, and UpdatePassword. There are other User prefixed tables with loads of information. For instance, the Portals each user belongs to can be found in the UserPortals table.

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.

Downloads

Summary

In this article we have gone through some background on DotNetNuke. Then we explained the database schemas and constructed some SQL statements based on those schemas.  This gives us a good basis to begin creating our reports in the next part of the series.



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-19 4:33:04 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search