Print
Add To Favorites
Email To Friend
Rate This Article
|
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):
30733/
52
|
|
|
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
|
|