AspAlliance.com LogoASPAlliance: Articles, reviews, and samples for .NET Developers
URL:
http://aspalliance.com/articleViewer.aspx?aId=1409&pId=-1
Creating Custom Reports for DotNetNuke using Crystal Reports - Part 3
page
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
Product Spotlight 

©Copyright 1998-2024 ASPAlliance.com  |  Page Processed at 2024-03-29 11:10:30 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search