New, Improved way of delivering Excel reports: Part 1

Everyone is creating and delivering Excel reports periodically – usually by mail. If you use SharePoint instead, you get some unbelievable benefits and unparalleled safety. Try this new method.

Estimated reading time 8 min

Pre-requisites

You need a SharePoint 2013 team site or report library or OneDrive for Business or MySite Document library for this to work. Some features will work on SharePoint 2010 as well. Enterprise edition is required. Alternatively you can also use Office 365 with SharePoint included.

There is no need!

Usually I start many articles by explaining the need and then showing the solution. In this case, there does not seem to be any need at all. Reports are being generated by us for decades. Delivering the reports by printing has largely stopped.

Many reports are generated from automated systems like  ERP, CRM or home grown applications along with specialized BI tools.

However, one common method continues to be used – Excel based reports.

The process goes like this…

We usually don’t send the original file because it has raw data as well – making it difficult to attach to mails.

Do you face these problems?

Even though this approach is working for years, there are many problems associated with it. I am sure you are suffering from one or more of these issues.

• The process is time consuming and repetitive
• You need to make copy of final reports and charts – interactivity is lost
• Finding the latest copy is not easy
• Too many mails need to be sent repetitively
• Searching for older reports in mailbox is cumbersome
• Excel is required to view the reports
• Each report has to be saved with different file name to preserve past versions

 

Why do we use this method in spite of the problems?

The answer is simple. We had no other way to do it in early days.

Now, fortunately, we have an elegant method available which eliminates all the above issues.

Not only that, the new method gives you new capabilities which you could not even imagine with the older approach.

Solution is SharePoint, but wait! There is more.

Store the Excel file on SharePoint and send the link. Ya ya… got that. You have heard that before. But it is not just that. There is much more which is usually not known to users like you and me.

Excel opens in browser

We want others to VIEW the report. Therefore, when someone clicks on the link to the Excel file, it opens in browser. Which is good, because irrespective of how large the file is, it opens quickly.

Problem – Raw data is also shown

But there is a problem… by default, it will show all the worksheets. Some raw data and interim calculations will also be shown. This is unnecessary. People are interested in the final report – not the raw data.

How to show only the report – not the raw data?

This is where the greatness of Excel and SharePoint combination comes up. Otherwise you could have stored the Excel file in any cloud storage platform like Google Drive, Dropbox, etc.

Excel has special options to choose what you want to show when the file is posted on SharePoint. Ideally you choose only the Pivot Tables and Charts or Power View sheets.

Here is how the process looks

Of course, this is only to be done for the first time you publish the Excel file.

For subsequent updates, you just open the file, add more data, refresh the report and save it back. There is no need to send the link again because the link never changes!

How will others know that you have refreshed the report?

They must go to the file in SharePoint and create alerts. Then onwards SharePoint will inform them when the file is changed.

What about reports of earlier months?

No worries. SharePoint has versioning. Once you enable it, SharePoint will store a copy of the older file whenever you make changes. The filename does not change.

People can then view the version history and see the backdated reports.

Benefits of this approach

1. Making and distributing reports is now very simple
2. Interactivity is maintained – leading to better analysis
3. Raw data can be safely hidden from the viewers
4. Makers of the report don’t have to send updates repeatedly
5. Decision makers don’t have to wait for updates
6. There is a single, well known place for each report. No confusion.
7. Duplication of data is completely eliminated
8. Report is available as soon as the file is saved
9. Reports are visible on any device with a browser
10. Past versions of report are automatically stored
11. Mail size constraints are no longer relevant
12. It saves lots of bandwidth
13. Report rendering is faster

There is only ONE disadvantage

With so many advantages, there must be some disadvantage – is it not?

The disadvantage is lack of security trimming. Reports cannot be filtered based upon who is viewing it. Thus, if you have a global report and you want managers from specific region to see only their part of the report, it is not possible. For this purpose, you must a formal, server based BI tool.

What Next

This is Part 1 of the article.

Part 1 covers the concept.
Part 2 shows the step by step process.
Part 3 covers Frequently Asked Questions.

***