Excel pivot charts dashboard in SharePoint

We’re in the process of getting some pilot projects built in PowerBI, upgrading SQL, and about 1000 other things here, and starting to make some headway on moving into a more visualization based reporting style.  I don’t want to lose any momentum with our internal users starting to look at charts we’ve built in Excel, so we came up with a solution that would allow us to project these charts into SharePoint without much bother.

At the time of this writing, we’re using Excel 2013 and SharePoint 2013.

Essentially it comes down to a few pieces:

  • naming the charts in Excel
  • creating Print Areas in Excel and naming the Print Areas
  • referencing those chart or print area names in SharePoint using the Excel web part.

 

Excel:

I set the file up so that one sheet has all of my pivot tables and one sheet has all of the charts on it.  On the table sheet, I’m pulling data from our SSAS Cube into tables side by side, one pivot table for each chart I want to set up.  In the file I’m currently working with, we have a flag in our cube that allows us to just look at the current closed week, so I set up each pivot table with that flag set to just the one week, and set up a slicer in excel to allow me to change that flag on all of them at the same time in case I need to look at things in the file itself.

Once I have the tables set up, I create the chart and get it looking exactly the way I want to while I’m on the table sheet – saves you from changing sheets everytime you have to test/change something.  Once it’s good to go, I move the chart to the chart sheet.  I do that for the rest of the tables and move the charts to the chart sheet once I’m done, positioning them so it’s easy to view them all, but leaving some space between them.

Naming of Charts and Print Areas:

I name the charts accordingly – is use a c_ naming convention so I can reference them later.  If all you want to do is show the chart, this is enough.  In some cases, I had some extra graphics set up on my chart sheet so that I can give a bit more info than I was capable of in the chart.  In this case, I used the Set Print Area function in Excel, highlighted the whole area I wanted to reference and gave it a name as well using a pa_ naming convention.

Sharepoint:

Uploading the file to a library in SP allowed me to version the file when I made changes.  (Also allowed me to restore an older version when I totally botched something).  I asked our SP team to create a page for me and using a page layout to give me several areas to play with, inserted an Excel web part into each area, pointing to the Workbook in Sharepoint I’d uploaded and either the Print Area or the Chart name in the Named Item section of Workbook Display.  Playing around with some of the other settings in the web part properties allowed me to ditch dropdowns and anything else I didn’t need, allowing me to set things up so it looked fairly neat and tidy (if I do say so myself).

I can put up some screenshots if anyone would like to see things, but figured I’d put this out there in case people were looking for an option for Sharepoint Dashboarding.

Leave a Reply

%d bloggers like this: