New, Improved way of delivering Excel reports: Part 2

image In this article we will see the actual steps of how to create a report in Excel, Publish it in SharePoint and Share it with others.Please read the previous article before reading this article.Estimated reading time 10 min

Pre-requisites

Excel 2013, SharePoint 2013 (Enterprise edition) or Office 365 with SharePoint. Some of the functionality will work with SharePoint 2010 as well.

The process

Here is what we want to do … same diagram from the previous article.

image

Get the data and Create the reports

I am not going to spend time on how to create reports. For the time being, let us assume that you have already created the summarized reports in the form of Power View Sheets, Pivot Tables and Charts. You may also have some manually created custom reports on specific sheets.

Decide what you want to display as a report

Ideally we don’t want to show all the contents. Most of the content will be raw data, intermediate calculation or supporting worksheets. Usually only the final report, which you usually copy paste as values into another file – is what we want to show.

This is done using File – Info – Browser View Options.

image

By default all the sheets are shown. Choose the required option and save to SharePoint site.

image

However, you can choose the sheets you want to display.

image

Showing entire sheets is a good idea for custom reports which are manually created. It is also the only option available for Power View reports.

Now only the selected sheets will be shown on the browser on SharePoint site.

This approach has one disadvantage. The  report components like pivots and charts may be scattered across the selected sheet. They may not be visible without scrolling. Therefore, it is a bit cumbersome to discover all components of a report – especially if these are lying on multiple sheets.

Therefore, the third option was provided. This is the most powerful option.

Items in the workbook

Every pivot table, table or chart can have a name. Default names are like Table33, PivotTable2, Chart19 and so on. But you can change the names.

Change Table name from Table tools – Design menu, Pivot table and Chart names from Analyze menu.

image

Choose the items you want and then save the file to SharePoint or OneDrive for Business.

image

Save to SharePoint

With Office 2013, this is very easy because your OneDrive and SharePoint locations are available in File – Save As menu itself. In Office 2010, you have to go to File – Save and Send – SharePoint option.

Share the report

Once the file is in SharePoint you have to share it with the intended recipients of the report. Click on the option button (…) next to the file and choose Share – type names of people. Choose whether they can Edit or View and click Ok.

image

A preview of the Excel content is also shown in this dialog for your convenience. It also shows the views on the file!

SNAGHTML148533f2

How do the recipients get the report?

Once you send a sharing invitation, they will receive the link to your  report by mail. The mail will be sent by SharePoint.

They just have to click the link. The items you chose to display will now be shown on the browser – in a nice scrolling menu.

image

On the right side – under the View area – small thumbnails of all selected items are shown. Click on the item to view it on the main area of the page.

If you have used Slicers – which is basically a filter, you can see the slicer on browser as well.

image

New way to publish reports

This is, of course, a new approach. It will need time for you and your organization to implement it. But why don’t you publish a few reports right away and try it out?

Over the next few articles we will cover more practical topics like difference between view and edit permissions, multiple slicers, versioning, notifications, combining multiple reports into one dashboard and auditing.

Related Articles

Part 1 covers the concept.
Part 2  (this article) shows the step by step process.
Part 3 covers Frequently Asked Questions.

***

3 thoughts on “New, Improved way of delivering Excel reports: Part 2

  1. Pingback: New way of filtering reports: Slicers – Part 2 (Filtering Filters) | Efficiency 365

  2. Pingback: Power View – no more PowerPoint for presentations! | Efficiency 365

  3. Pingback: The big and scary File menu | Efficiency 365

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s