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.