The "Advanced DataGrid" module by Efficion Consulting has been around for years.  It allows you to make direct SQL queries into the DNN database and return the results in a basic HTML table.  This is by no means the only module to offer such functionality but the Advanced Datagrid module (and source code) are FREE.  The module is no longer being actively developed but it continues to be useful to those seeking to extend DNN functionality without spending money.

Download
The module and source code continue to be offered as free downloads from Efficion:
http://www.efficionconsulting.com/tabid/217/Default.aspx

The latest version is 3.1.3.  It was developed for DNN 3.x but we use it in production on DNN 4.6.2.  (I have not yet tested it on DNN versions above 4.6.2.)

Examples
I'll cover one example in this "Part 1" post and I'll follow up with a second example in a "Part 2" post.  For "Part 1", let's use the ADG module to create a list showing the ten most recent signups on our DNN portal.  This list will include the user ID, date of account creation, username, firstname, lastname, displayname, email and registration status.  NOTE:  Since this list will contain user names and emails, you'll probably want to configure the module to be viewed by administrators only.

Once you've got the ADG module installed and added to a test page, open the module Settings.  Under the "Advanced DataGrid Settings" section, add (copy-pand-paste) the following SQL statement:

SELECT DISTINCT TOP (10)
  u.UserID, aspm.CreateDate, u.Username, u.DisplayName, 
  u.FirstName, u.LastName, u.Email, up.Authorised
FROM 
  UserRoles AS ur INNER JOIN
  Users AS u ON u.UserID = ur.UserID INNER JOIN
  Roles AS r ON r.RoleID = ur.RoleID INNER JOIN
  aspnet_Users AS aspu ON aspu.UserName = u.Username INNER JOIN
  aspnet_Membership AS aspm ON aspm.UserId = aspu.UserId INNER JOIN
  UserPortals AS up ON up.UserId = u.UserID
WHERE (r.PortalID = [dnn:PortalID])
ORDER BY u.UserID DESC

Now click "Update" and you're done.  You should now have a very basic table with the expected "most recent user signups" information.

Suggestions
Now that you've got a working list, you can extend its usefulness with different data.  How about a query that returns a list of users with matching email addresses (to help identify users with multiple accounts).  Or maybe a list of users based on the most recent log in date?  Or the top ten users with the largest number of failed login attempts?

Calling Stored Procedures
The most  notable restriction is the size of the query you can execute -- white space and carriage returns count so use then sparringly on longer queries.  Unfortunately you won't know that your query is too long until you've saved it and then gone back into Settings to see that it has been truncated.  If your query is THAT big you should probably be seriously thinking about converting it to a stored procedure anyway.

To call a stored procedure with the ADG module simply use the "exec" command.  For example, to execute the DNN stored procedure "GetDatabaseVersion" you would use:

  exec GetDatabaseVersion


That's it for this introduction to the Advanced DataGrid module.  Cheers to Efficion for continuing to make the module and source code available.  In "Part 2" we'll look at a simple example using ADG to create a "dashboard" report for the DNN Events calendar.
 
Part 2 is now available here:  http://www.eguanasolutions.com/DNN_Blog/EntryID/5.aspx