Exposing Excel contents to make reporting easy in SharePoint
This article will show how simple it can be to use the out of the box features of SharePoint and Office documents to provide powerful reporting on you documents. This example will look at the common scenario where a SharePoint (this will work with standard WSS) document library is used to store invoices.
Problem:
In order to forecast the projected income I need to maintain a separate summary spreadsheet which often gets out of step with the actual invoices.
Solution:
Out of the box SharePoint is able to interrogate custom properties from an Office document, using these features and the ability for Office documents to link these properties to document contents we are able to surface valuable information from the Excel Invoice into document library properties and use these to create powerful reports that will always be up to date.
Create Custom Document Library
Create a new document library and add the following custom columns
- Order Number – Text Field
- Invoice Amount – Number Field
- Invoice Date – Date Time
- Payment Date – Calculated Date Time [Invoice Date]+ 30
In this example I have used a default payment term of 30days. It would be possible to extend the solution to extract the payment terms from the spreadsheet and base the calculated column on the [Invoice Date] + [Payment Terms] if you had a customer specific requirements.
.jpg)
The example is based on the Excel 2007 Online template; your template can be based on your own Invoice format. If you want to use the template when selecting New from the menu in SharePoint it will need to be based on Office 2007.
Excel makes it very easy for you to expose named cells as custom properties so we will use this feature to name the parts of the invoice spreadsheet we want to surface.
For each of the following cells within the spreadsheet select it and add a name by typing the name in the Name Box.
- OrderNumber
- Amount
The one area that we will need to do some formatting is to ensure that the Invoice Date is formatted in a way that SharePoint understands as a date. In the picture above we have created a formula in a cell that will do this for us.
=TEXT(Invoice,”yyyy-mm-ddThh:mm:ssZ”)
This format is based on the ISO8601 format; more details can be found here CreateISO8601DateTimeFromSystemDateTime
Name this new cell InvoiceDate; you can also make this cell hidden.
Now we have all of the contents of the document named we can promote these to custom properties, this is probably the easiest bit! From the ‘Office Pearl’ or what ever you want to call it – choose Prepare > Properties
.jpg)
This will present you with the standard document properties form, select the Advanced Properties option to display the traditional properties dialog.
Add the following Properties by typing the Name (it must match the SharePoint column name above exactly), tick the Link to content box and select the correct field from the dropdown. If you don’t see a field it is because the field name was not saved, check as above and repeat.
Order Number – select OrderNumber
Amount - select Amount
Invoice Date – select Invoice Date
You should see something like the screenshot below. .jpg)
Do not worry that the Invoice Date field is marked as a Text type; we have formatted the text so that SharePoint will be able to add this to its Date field.
Save the document.
Uploading to SharePoint
before we add the template to the document library we will test the upload works.
Fill in a sample invoice with Date, some sample lines and an Order Number. Save the document and upload the document to the SharePoint document library. This should present you with a screen similar to this
.jpg)
All of the fields should be filled in automatically with the values you entered in SharePoint!
The document library should then look something like this
.jpg)
Give users easy access to your template
SharePoint make it easy for you to make this template the default on used when people click the New button. Clean up the test contents from your spreadsheet, add protection and hide cells as required, and save the file as template.xlsx.
In your document library click Actions > Open in Windows Explorer
This will present you with a window like this
.jpg)
Copy your template.xlsx file into the forms folder; this will replace the out of the box template for this library.
Create some reports
I will not walk you through the types of reports you could create but using standard list features and the fact you now have the information surfaced it will be easy to interrogate the information as required.
Possible options
1. Forecast showing sum of income based on invoice due dates
2. Payments overdue – show all payments overdue, add another calculated column to show ranges to help chase late payments
That’s it!
It works better on file upload then through Office Integration!
There is an issue where if you use Office Integration the user will need to manually update the values in the form presented in Excel. This form takes precedence over the calculated values in the custom properties.
Next task, how to get the custom properties form to update dynamically with the correct values?
