If you need an upgrade from Efficion Consulting's free (but aging) "Advanced DataGrid" (ADG) module, DNNStuff's free SQL module may be the answer.  "SQLView" does what ADG does and does a few things better.  The drawback?  SQLView is not open-sourced like ADG.  If you think "FREE is FREE" and you don't care about having access to the source code, read on...

DNNStuff SQLView
Ok, so what does SQLView do for us that the Advanced DataGrid can't?  There are a number of very notable items, actually:

• Specify a different data provider
  You can pull data from any SQL source, not just the default DNN database.
• Support for Querystring values
  You can include tokens in your SQL based on querystring values.  Very useful.
• Robust column sorting
  The Advanced DataGrid has a fragile implementation for column sorting, often resulting in SQL errors when attempting to use the "click a column to sort" feature.  SQLView is more intelligent:  it separates your query's "SORT BY" clause (if any) from the main query resulting in a robust "click-to-sort" feature (I haven't broken it, yet).
• XSL Transformations
  SQLView provides an option to have the returned data transformed by your own XSL code, either a local XSL file or a remote file accessed as a URL.
• Well-exposed CSS styling
  SQLView provides the ability to direclty link CSS classes to the returned table (grid), table header, row, alternating row and pager.

Sold, yet?  Remember -- SQLView is a FREE module.  You can get the module here:  http://www.dnnstuff.com

SQLView example:  Sortable List of Events
Let's jump right into a real-world example.  A blog reader asked me about generating a sortable list of events from the DNN Events module.  This user wants to sort not just by event name and date, but also by location:  state, county and city.  Let's use SQLView to do a little Events list magic:

(Assuming you already have SQLView installed and have added it to a test page.)

(1) First, go into the SQLView module's OPTIONS (not SETTINGS, but OPTIONS).
As you'd expect from a SQL module, the main entry field you see is the "Query" field.  Copy-and-paste the following query into the "Query" field:

SELECT     
  EventName AS [Event Name],  
  EventTimeBegin AS [Start Time],

  CASE WHEN ev.Location > 1 THEN
    substring
     (evl.LocationName, 0, charindex(',',evl.LocationName))
    ELSE 'n/a'
    END as [City],

  CASE WHEN ev.Location > 1 THEN
    substring
     (evl.LocationName, charindex(',',evl.LocationName)+1,
      len(evl.LocationName) - 3 -
      len(substring(evl.LocationName, 0,
          charindex(',',evl.LocationName))) -
      len(reverse(substring(reverse(evl.LocationName), 0,
          charindex(',',reverse(evl.LocationName)))))+1)
    ELSE 'n/a'
 END as [County],

  CASE WHEN ev.Location > 1 THEN
    reverse(substring(reverse(evl.LocationName), 0,
            charindex(',',reverse(evl.LocationName))))
  ELSE 'n/a'
 END as [State]

FROM Events AS ev left join
  eventslocation evl on evl.location = ev.location


The only other setting you need to worry about is the "Allow Sorting" option -- make sure this is checked.  That's it.


The SQL Query
Remember that this is just a quick-and-dirty example so you can try out the module with minimal effort.  In practice you would add a custom function to your database to provide a robust string tokenizer to be called upon by your query to extract city/county/state info from the "location" string value.  For this example, though, we're going to do it all in a single, messy query.  Because of this, the code to display CITY, COUNTY and STATE will look a bit strange since we're doing simple string parsing on the fly rather than tokenizing as we'd prefer to do.

Here's what the query does:  The query returns the event name, start time and the (if specified) city, county and state as individual columns.  The EVENTS module does not provide specific fields for CITY, COUNTY or STATE.  Instead, you are allowed to provide a "location" string.  For our simple example here, we are using the "location" to define CITY, COUNTY and STATE by entering the location information in the form of "(city), (county), (state)".  Note that for our example to work it is vitial that the location (if specified) contain TWO commas as delimiters.  If an event does not specify a location the city, county and state fields will show "n/a".
 

What's Missing
This is as basic as it gets -- merely a proof-of-concept to provide an event list and introduce the free SQLView module.  This example does not take into account the possiblity of multiple event calendars, event dates that have passed, events with incomplete (or mal-formed) location information, etc.  Hopefully it's enough, though, to give you a start on making your own (robust) solution to whatever problem you're tackling.  Cheers!