Monthly Reporting in a Team Site

Monthly reporting thumb

I thought it was time to show you another real-life example of facilitation of a small process.

What was the situation?

All units had to deliver a certain report at the end of the reporting period, before consolidation in their business. It turned out that the units reported different things and delivered their reports at different times.
Of course this was not the way to go, so someone made an overview of the different scenarios that were in use, and from that (and with many discussions with everyone involved) they created the desired process that all units would have to follow for consistent reporting and consolidation.
The process described all actions that had to be completed at certain days around the end of the period. Every unit had to mark the action as completed on the day specified.

What was the solution?

The next step was to think of a place where every unit could monitor their progress. We expected to be many items (x actions * y units), and the items would be edited regularly by many different persons, which might lead to messing up each other’s data, so we first thought we would create a list per unit. That would also make it easy for people to find their own dataset.
However, that would make it harder to see if everyone was on the right track, and it would mean we would have to make changes to many lists if there ever would be a change in the setup.
We decided to go for one central list.  A custom list, of course! I would add some safety measures to prevents accidental deletion or editing of other people’s items. (Few Datasheet views, a custom set of permissions and targeting – see also Dangers of the Datasheet)

Every unit had their own identical set of actions. Next to the actions, there were fields for unit, the responsible, the number of days before or after the end of the period that every step had to be taken (from -3 to +15), a free text box for comments and of course a Yes/No box to note if the action had been completed.

All units have the same set of actions to complete.
All units have the same set of actions to complete.

I then created one Datasheet view (Update View) with as few columns as possible to make it easy for all participants to update the list. I created a number of Standard Views  for the process owners to keep an eye on general progress.

Before the start of reporting
This is the set of identical actions that every unit had to report. Before the start of the cycle, it would look like this.
Progress view to track if everyone is on time.
Progress View at day 0. It is immediately clear which unit is behind with their reporting.

I also created a SharePoint group for each unit. Every group got a set of custom permissions: “Contribute without Delete”. This was one of the measures to avoid accidents that may occur when a big list is frequently edited by many people.

Targeting option in the web part.
Targeting option in the web part.

For each unit I created a page, and added the list webpart on it with the Update View. I filtered for the unit, sorted by day, and then targeted the webpart to the SharePoint user group of that unit. That way we made it easier for people to see and update their own actions only – if they accidentally clicked on the wrong page, they would see a blank page.

All actions that have to be completed
This page shows all actions that have to be completed for this unit.

After each period had been reported in the correct fashion, the site owner made an export of the list into an Excel file for archiving, set the Completed fields to “No”, and the system was ready for a new reporting cycle. When “action time” neared, she would add an announcement on the Homepage that the new cycle was ready to start.

What was the result?

After a trial run in a few units, it turned out that this worked well. The pages per unit and the Excel-type update made it easy for people to find and update their own items. It was also easy for management to track progress – the Progress View (showing only items where “Completed is not equal to Yes”)  showed if every unit was on track.

From now on, reporting was done in a uniform fashion and consolidation of the reports was a lot easier than it had been. After some months, another business asked me for a copy of the setup. So I guess it was a success  :-).

Image courtesy of Stuart Miles at

7 Tips for copying from Excel into a Datasheet View

ExcelDatasheet-LogoSome time ago I was working with an Excel-loving crowd who had to share their data in a SharePoint list.
I wanted to make the transition as smoothly as possible, so I created a special “Data Import View” (a Datasheet View) to make it as easy as possible to copy their Excel data into the list.

There were too many people involved with too many different PC configurations, to trust a Data Connection. So a one-time copy-paste action it had to be!

After some tests I gave them the following instructions:

1. Sort the columns in your Excel sheet in exactly the same order (left-to-right) as the Data Import View (I listed all columns in the correct order)

2. Use the exact same words, spelling and capitalization as the values in the dropdown fields in the SharePoint list.
I listed all the words – we used “color”  and not “colour”, for instance.
If you use different capitalization in a Choice column (e.g. “Preferred Supplier” instead of “Preferred supplier”) the Choice column will accept your value without error message, but when you edit the item, the value will change to the first of the Choice values.

3. Check your cells have exactly the same formatting as the SharePoint list.
Sometimes text fields gave errors while copying – copying the text from the dropdown field and paste that into the Excel file often solved that.

4. Use the correct date-and-time format. (I had to test a few different notations to know what worked and what not in our multinational organization)

5. Select the first (utter left) cell before you start pasting. (The Data Import View contained many columns, and needed horizontal scrolling to see all columns, and sometimes people forgot to scroll back).

Error message
This is the error message if you do not start on the correct cell

6. Start pasting only when the first cell is ready. It shows a bold line around it.

Bold line around cell
This is what the bold line looks like
If your fist cell looks like this, please click it, otherwise you get the below error message when you paste.
If your first cell looks like this, please click it, otherwise you get the below error message when you paste.
Error message if you paste into a cell without a bold line around it
Error message if you paste into a cell without a bold line around it

7. Do a test-run with one line. Adjust your data and check again when you encounter issues.

Next time your users say that “they can not copy -paste their Excel data into a Datasheet View”, these tips may point you towards a solution.

I could have made things slightly easier by using a single-line-of-text for each column. Single-line-of-text is like bloodtype AB:  it can receive anything :-). But because I had originally designed the list for adding new items via “New Item” in the Standard View we had to create these instructions. And single-line-of-text columns lead to more deviations in spelling.

David Young has also found that you need to refresh your screen after deleting data and trying again.

Do you have any other tips or experienced other errors? Please share!

You may also like:

Dangers of the Datasheet

Design Fault, Scope Creep, Moving Insight or Unexpected Success? (The project that inspired this post)

10 tips for Choice or Lookup Columns

Dangers of the Datasheet

DangersDatasheetThe Datasheet view is a favourite with Excel fan boys. I have seen many a controller or purchaser create one Datasheet view after another after they had learned about this fabulous functionality. And I have subsequently seen lots of accidents with Datasheets where I had to come to the rescue…

Because Datasheet Views can make it too easy to add, edit and especially delete content. Besides, they have some annoying habits…or should I call that “specific functionality”? 🙂

Let me show you the differences. This is a Standard View of a list:

Standard View
A list in Standard View

And this is the same list in Datasheet View. You will see some differences immediately, but there are invisible differences as well.

Datasheet View of the same list
Datasheet View of the same list

It is important to design your Datasheet Views to minimize accidents and irritation. Think about purpose, process, audience, update frequency, backups and more.

Datasheet Design Do’s and Don’ts:

1. Make sure your column names are easily understood.
The Datasheet view does not show the description in New or Edit mode, so the column name is all information your users will get.

2. Do not use a Yes/No checkbox in a datasheet view.
Without a proper description it can be very unclear what a checked box means.  A “Yes/No” or “Open/Completed” choice field may be a better option.

To jog your memory, this is what the New or Edit Item looks like in Standard View:

New Item - look at the description field and the mandatory columns
New Item – look at the description field and the mandatory columns

3. Show empty required fields in your view.
This is especially important when people are expected to add new items.
Required fields that are not completed will not allow you to save your item. Besides, you can not move to the next line item before you have completed all information in the current line item. This makes quick “vertical copy” (as in Excel) impossible. See also item 4.

You have not completed the mandatory field.
You have not completed the mandatory field.

4. Add an * to the column name if the column is required.
If people only ever see the Datasheet View they will not know a column needs to be filled – it does not show as in the Standard View.

5. Avoid content approval and multiple-lines-of text fields in “enhanced rich text” format.
These can make your Datasheet View read-only. This post sums it up nicely.

This is the format to avoid:

Enhanced Rich Text format
Do not use this format for your multiple-lines-of-text column

6. Never make a Datasheet View your default view.
It is too easy to accidentally overwrite or delete items. It shows only a limited number of items on a page and you do not see any context such as hyperlinks and the item menu.
Instead, use a Standard View and inform your audience where they can find the Datasheet View.

7. It is not possible to group in a Datasheet View.
If you want to show your content in groups, add the grouping column to the view, and sort first by this column. This way you have already used one sorting option.

You have to add the column that you group on
You have to add the column that you group on

8. If you need to bulk-edit on a regular basis, create one Standard View for editing purposes.
Use only those columns that need to be edited, so you can quickly turn it into the correct Datasheet View.
You can also create a personal view for this purpose.

9. Think 4 times about a Datasheet View if it is going to be edited by a large and unknown audience.
A large audience of unknown people is impossible to properly train or influence, so you will have to take extra preventative measures.
– Use a “Created By”, “Modified By”, or “Assigned to” =  [Me]  filter for the view.
– Create a page for each audience, filter by audience, and target the list webpart on the page, to the audience.
– Limit the public Datasheet Views in your list and hide Edit options from Standard Views.
– Remove the “delete” permissions from the Contribute role.
If those measures can not be taken, use a Standard View and ask people to edit their items individually.

10. Export your data to spreadsheet on a regular basis.
This will provide you with backup data and allows you to analyze the results and create graphs.

And do not forget training!

The Datasheet View may look like Excel, but it behaves differently. So provide training for your users to manage expectations and prevent accidents,

Training topics could be:

  • Column description, purpose and peculiarities
  • Editing frequency
  • How to import or copy data from Excel
  • Basic troubleshooting

Share your thoughts.

What are your experiences with the Datasheet View? Have I missed something? Please share!

Design Fault, Scope Creep, Moving Insight or Unexpected Success?

The design.
Once upon a time, I created a Team Site to facilitate a global project. It was based on a custom list where global and local people could enter project data, such as business unit, product, and a code from a choice column. The code represented a number. I suggested to the project owner that we add a column with the number corresponding with each code, to enable an automated calculation of the value of the item. *1)
The process owner did not think that was necessary. He wanted to keep things simple and would do the aggregated calculations via an Export to Excel.

Testing and feedback.
When the lists and specific views had been created, I asked the process owner to test and discuss this with a selected number of the 50 designated users. He came back with some good feedback and change requests. We ended up with 4 custom lists, each with the same structure, but with different values to select from.

So far, so good. We launched the site.

At first…
Shortly after introduction the site started to fill with entries. The only negative comment was when one person was flooded with Alert emails when someone  bulk-uploaded 500 entries. I showed him how to change the default “immediate” Alert into a “daily summary” and we happily agreed this was a sign of success.


But then…
After some weeks, I noticed that Excel files with the required information were being uploaded to the site and updated online. It turned out that many business units had been doing this same exercise already on the business unit level, and thought they would share and update their work (which had been done in Excel) in our site. I would have preferred them to add their data directly into the lists, but it was a good sign that the business units wanted to share.

You can imagine what followed: “How can we upload the content in those Excel files into the lists?” It was not too difficult to create an extra Datasheet View with all mandatory columns, export it into a Template, and write a short manual on how to copy the content from Excel into the lists. *2)

And then…
Meanwhile, requests for access kept coming in from across the world. There were now about 200 people who wanted to share their data instead of 50. Great!
The site also got senior management’s attention. They wanted to show the real-time project results on the site’s Homepage so everyone would be stimulated to add to the numbers. Yes, I could understand that wish, but it meant that I had to add that extra numerical column in each of the lists after all (that the process owner had decided to leave out in the design phase) as well as a calculated field, and change all the views and templates. That was not too bad, but someone had to update all existing 1500 entries with the corresponding number.
A volunteer was appointed to do just that. 🙂

But wait…there’s more!
Just when I thought the whole setup was stable, the process owner approached me and asked me if we could capture two other values in the list while we were collecting data.
For me this was 30 minutes work, but for him it would mean that he had to communicate a change, re-educate everyone, and revise the input template and the manual. And for all business people it meant they would have to find out those values, revisit their entries (by now we had about 4000 entries) and update them. The “volunteer” could not do it this time, because the values were not as straightforward as the code.
He decided not to do it.

What do we call this?
It all worked out well in the end, but I have wondered what this is.

  • Is it Design Failure because we did not envisage properly how the site and project would develop? Should I have been more insistent on adding that numerical column from the start since I knew that would come up? Still, I could never have foreseen the wish to collect those two extra values.
  • Is it Scope Creep because the process owner had not set enough boundaries for what he wanted to achieve?
  • Is it Moving Insight because the organization learned what you can do with SharePoint as they went along and it was only natural that they wanted to make the most of it?
  • Is this an Unexpected Success because it worked well and we should be happy that it sparked so many new ideas?
  • Or is this just The Way These Projects Go?

This is something that I have experienced more often and I never know whether I should be happy about it (because it shows people learn about the possibilities of SharePoint) or sad (because it shows what we are not so good at project definition).

This is the reply when I asked the question on Twitter:

What is your experience and how have you dealt with this?

*1) The drop-down had too many items to enable an IF, THEN formula to calculate the number. Next to that, there was a “specify your own value” field if the project had a non-standard code.
*2) I thought connecting the Excel to the list would be too tricky for most users, and I also did not know how SharePoint or our system would react to so many simultaneous connections

Image courtesy of Ambro /