The Power of Data View Web Parts

I recently had an opportunity to mirror some Access reports in SharePoint.  It reminded me how powerful Data View Web Parts in SharePoint really are.  Here’s what I did…

The Access front end was using SQL Server as its back end data server, so I knew that I could get at the data from SharePoint.  The first step was to create a blank site in SharePoint.  Then, in Frontpage (I used Frontpage because this was a SharePoint 2003 site — better safe than sorry), I went To Data/Insert Data View.  Under Database Connections, I created a connection to the SQL Server database and specified that I would use a custom query.  I dropped the SQL query from Access into the custom query dialog, closed the window, took a look at the Data View and whoops — it didn’t work.  I kept getting the error below every time I tried to look at the Data View.

It turns out that I was getting caught by the friendliness of Access.  Access is fairly forgiving about query syntax, where SQL Server is not.  So, for instance, when I did a tablename.field = No in a WHERE clause, SQL Server didn’t like it.  It turns out that it wanted me to say tablename.field = 0, because that is the actual value stored in the field.  There were a few other little peculiarities like this, but after some fiddling, I was able to get the query to run.

Once it was running, I inserted the Data View into the site.  When you do this, you get a very basic table containing the results of you query.  Not pretty, but the data is there.  However, there is virtually total freedom in formatting the table.  Since I was mimicking an Access report, I wanted the Data View to look just like the printed report.  After about 30 minutes of fiddling with grouping, sorting, and formatting, I was able to go from this:

to this:

and with full control of the formatting (and had the client wanted it), it could have looked even nicer!