SharePoint 2010 Apps for n00bs – Reports

This part assumes you have done the “data” part of this workshop – here. If you haven’t, you can do similar with your own lists of course.

This workshop & the one on data is also in video form - https://youtu.be/iyQmam_fvdg

Reports in Excel

So it just so happens, SharePoint supports hosting an online version of Office inside a web-browser. That’s pretty handy because Excel was built to visualise large amounts of data, and actually can pull data out of a variety of external sources – SharePoint lists included. So the idea is simple – get Excel to generate reports based on external SharePoint data (our lists), make it all nice & pretty, but then put the Excel sheet inside our SharePoint site so we can see it all together. I bet you didn’t see that one coming!

Reading List Data in Excel

For any given list, we can work with the list in Excel if we want. As it happens, Excel’s got some pretty handy data visualisation abilities so I figured why reinvent the wheel, let’s do our reporting there. Click “Export to Excel” and SharePoint will export an IQY file (Excel Web Query File) that tells Excel to open a data-source from a certain location (our site/list) – much like SQL.

Open the web-query file; Excel should know what to do with it. A web-query file is just meta-data for Excel to know what data to grab and from where – SharePoint generates it automatically.

We now have a blank Excel spread-sheet with 1 data-connection to our SharePoint list, waiting for customisation & saving.

Generate Nice-looking Sales Report

Just seeing list data is a bit rubbish so now I’m going to knock-up a quick report to show what product has sold best. Given we’re in Excel-land this isn’t exactly rocket-science, but let’s run through it anyway…

Insert a new PivotTable:

Now we’re going to use the SharePoint data-source to base the table on so select is as below:

…and say OK. Now we need to say which fields we’re interested in; in this case, what was sold & how many. Thus giving us this:

Yay! Stats and stuff! Now for the finishing touch…

No report is complete without a 3d pie-graph thrown in for good measure:

Now one last things; let’s sell another cat-food bag and see the report update itself (although this time I’m doing it from the standard view – just because I can).

And now in our report when we refresh the data-connections we can see the cat-food count rise and our graph update itself. Magic!

Save Report in Application

Now we need to put the report in the site so we can open it from anywhere. To do this we want a “Document Library” to store it, so in the browser click “Site Actions” and then select “New Document library”. Fill it out to be like so (click to see more options if necessary):

Then in the list, let’s add a new item – our saved Excel spread-sheet.

Once it’s done your list should look like this:

Now it is possible to open the spread-sheet directly in the browser, in SharePoint but that’s for another day – for now we just want to be able to save it centrally.

We’re done!

So that’s about it for now; here’s some customisation I’ve done to the homepage from the web-browser, just because I can:

Here’s a customised homepage thanks to some web-parts I threw onto the page & a picture of Tristan the cat. Play & tweak this to your heart’s content.

Wrap-Up

Excel is a great way of presenting data in general, and SharePoint allows Excel to be embedded directly in your site. How marvellously convenient!