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.

clip_image036

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.

clip_image038

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:

clip_image042

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

clip_image044

…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:

clip_image046

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

clip_image048

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

clip_image050

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).

clip_image052

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!

clip_image054

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):

clip_image056

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

clip_image058

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

clip_image060

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:

clip_image062

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!


Comments (0)

Skip to main content