|
Creating Custom Reports for DotNetNuke using Crystal Reports - Part 3
|
by Eric Landes
Feedback
|
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days):
32188/
66
|
|
|
Introduction |
In Part 1 of this series, we went over an introduction to
DotNetNuke (DNN). We also delved into table structure and some basic SQL Statements.
As mentioned in the first part, many people are using DotNetNuke (DNN) as their
option as a CMS application. DNN has grown into one of the most popular open
source frameworks for Portals in the .NET space.
In Part
2 of this series we went over how to create a few different custom reports
using DNN focusing on the forums application. For a brief overview of DNN, see Part 1 and the DotNetNuke site.
In this final article we will see how to use Crystal to report on the Blogging application that comes standard with DNN.
|
System Requirements |
·
Visual Studio 2005
·
Crystal Reports XI
·
DotNetNuke 4.5.4
·
SQL Server 2005
|
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)
|
Creating the Report |
In this report we will group by Portal, then by blog. In
each section we show the Entry Name, if it was published, the date entered, and
then a number of comments roll up. Nothing fancy, just adding the bloggers name
after the blog name. See Figure 1 for an idea of what the report looks like in
development.
Figure 1
This report allows us to select which portal to run this on
using the Portal parameter. If you did not have many different sites, or
wanted to compare the different sites, you could run the report to see the data
from all the portals.
|
Creating a Summary Report to View Your Permissions |
Next we tackle the Permissions information contained in the
DNN database. Perhaps you need a quick way to view what roles utilize the
different options on a particular portal. To do this we look at the following
tables:
·
TabPermission
·
Roles
·
Tabs
·
Portals
·
Permission
·
UserRoles
·
Users
To make our SQL statement to use, we need to join all these
tables together. See Listing 2 for the exact code.
Listing 2
Create View vwpermissions_summary
as
select tp.tabpermissionid
, tp.AllowAccess
, tp.PermissionID
, per.PermissionName
, ro.RoleName
, tb.TabName
, po.PortalName
, us.FirstName
, us.LastName
from dbo.TabPermission tp
inner join dbo.Roles ro on (ro.RoleID = tp.roleid)
inner join dbo.Tabs tb on (tb.TabID = tp.TabID)
inner join dbo.Portals po on (po.PortalID = tb.PortalID)
inner join dbo.Permission per on (per.PermissionID = tp.PermissionID)
inner join dbo.UserRoles usr on (ro.RoleID =usr.RoleID)
inner join dbo.Users us on (us.UserID = usr.UserID)
In order to make changing underlying fields easier, we have
created a view. This allows us to add and subtract fields; then by using Verify
Database we can get those fields synched with our report.
We get the Names of users that have specific permissions
from the Users table, linked via UserRoles, then the role table and the tab
table. The portal table is related.also. The table we start from is called
tabpermission. This table includes links back to the tab, to the role and even
to Users if they have specific permissions. But we need to "snake"
our way back via the role table, User Roles, then to Users to get the first and
last name of the actual users.
There is also a permissions table that includes the names of
the permissions (View Tab, Edit Tab). This helps us view what the different
roles can actually do on the different tabs.
|
Creating the Permissions Report |
Now that we have the SQL, let us create a new report that
displays our fields in different ways. This report (see Figure 2) takes the
data from our View and groups it by Portal and then by tab. The detail
displays the Permission group name, and the role name, along with the User's
first and last name. Feel free to add other fields by modifying the view and
then verifying the data source.
Figure 2
|
Downloads |
|
Summary |
We have gone through creating different reports for
DotNetNuke. These included reports for the forums application, the blogging
application and a report that helps display Permissions for each tab in the
portal. The intent behind this series of articles is to introduce the reader to
a great application in DotNetNuke, and how to create custom reports in Crystal Reports.
The DNN database schema is easy to follow and using other tables in the schema
should be a similar experience to using them in these reports. I hope this has
been helpful; happy report writing!
|
|
|
|
Product Spotlight
|
|