Sharepoint Help Desk Reporting

Reports Requirement

In order to move our Help Desk to Sharepoint, one of the requirements is the ability to run monthly reports for some customers. Currently these reports contain a list of all calls that have been opened, all that have been closed and all that are active within a month. The reports go out in PDF format.

SQL Reporting Services

I wondered if SQL Server Reporting Services could generate these reports for us, but after fiddling around I couldn't seem to get these to work properly. I read some articles about it, and it's possible (using the Sharepoint web services stuff) but it would probably have been a lot of hassle and a bit of a maintenance nightmare! It seems that SQL Server Reporting and Sharepoint integration is a relatively new piece of functionality, and it's something I reckon Microsoft needs to work on improving.

Access and Lookup Fields

After giving up on SQL Reporting I tried using Access to generate the reports, and this turned out to be pretty easy. One thing that I did need to do was create a new field for the Support Call list that contains the customer that owns that call. Originally, the list just had the reporter in and you could get the customer by following the link to the reporter's details (each reporter works for one of our customers). But in order to do customer based reports, adding the customer field in to the list was necessary.

I thought that automatically setting (or restricting) the content of one lookup field based on the content of another lookup field might be something that Sharepoint supported within a list definition. This isn't the case, and in the end I had to write a workflow that sets the customer field based on the content of the Reporter field.
This kind of one-removed relationship does seem to be a bit of weakness in Sharepoint. I've seen you can get 3rd party solutions that allow you to do this, but really I reckon this should be a standard feature in Sharepoint.

Anyway, workflows aside, the end result was a simple form that lets you define the month and the customer, click a button and the PDF is generated. This Access generated report looks better than our old one, and it was easy to add extra bits such as a simple pie chart showing the state of all the support calls active within the chosen month.