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:
and with full control of the formatting (and had the client wanted it), it could have looked even nicer!