SharePoint Lists reinvented: data management and reporting
by Yaroslav Pentsarskyy
If you're familiar with tools like SQL Reporting Services, Crystal Reports, etc., you or your end users are probably already taking advantage manipulating your data and exporting it into document-like files of various formats.
These days, in typical organizations SharePoint has taken over many roles with a variety of templates available out of the box - you may be thinking of migrating your workflows and day to day reporting scenarios to this framework.
With tools like KPI, Dashboards, and various filters SharePoint for reporting seems like the way to go; but are your users completely ready for this switch?
If you look at SharePoint in terms of data manipulation and storage terms: we have a SharePoint List - which closely resembles database table and we have Document Library which is a List with enhanced capabilities to manage documents and files.
SharePoint lists are frequently used to analyze and filter data within you portal. Various services and components (third party or out of the box) can help you with graphical representation of your data and basic aggregation.
However, out of the box, there is no functionality that allows an end user to make an analysis on the data within their SharePoint list and then produce a document-like, template driven report.
For one example, consider an MS Word or PDF document generated on demand pulling desired data from the list. This simple and common feature we're all used to when working with data management solutions is quite significant missing piece to the picture.
In one of the most recent scenarios I worked with, a client requested functionality to store their complex order information in a SharePoint list for further analysis. After the system was designed and used, it was announced that there would be a significant increase in efficiency if the data from those list items could be transferred into a desired template to produce on the fly MS Word reports.
There are many approaches to accomplish this, but with a roll-out of the Open XML Document format - generating documents has been a significant improvement comparing to older version formats.
Since every document is a package with XML definitions - generating one is similar to extracting the template and working with XML specific definition relevant to the type of manipulation we're after.
In case described above, the solution was implemented as a SharePoint feature adding an option to a List item context menu user interface. This option calls a function that reads all of the fields of this particular list item. Since one of the properties identifies the document template to be used, this particular template is manipulated to match every keyword within a document with the appropriate value from the list.
As an end result, we substitute each keyword in the XML definition within the template with relevant values. Finally, we package our modified XML definition back into the Open Document and offer it as a download.
From the business user prospective, our end user will click on the List item context menu option and get an MS Word document in return as a download. This on the first hand basic functionality is definitely a great candidate to bridge a gap between tools we're all familiar to use and relatively new ground like SharePoint.
If you're a developer like me, you probably can't wait to get your hands on trying this out. Well, I have a starter kit for you to generate your own documents available in script library.
If you're a business user you'll probably like to try out the SharePoint solution that was used in this scenario, which can be found here:
http://store.sharemuch.com/products/generate-word-documents-from-sharepoint-list
Here is the version that allows you to generate multiple records in the list to a Word document:
http://store.sharemuch.com/products/bulk-generate-word-documents-from-sharepoint-list
Please be sure to leave your vote so I know whether you liked this article or not.
