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.
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.