How to Build Office365 Reports and Dashboards in Excel using REST
Someone asked me the other day how can I keep an eye on my users mailbox sizes. Well there are a lot of ways but I’ve been wanting to mess around with Excel and the Office365 reporting web service to create a nice dashboard. The dashboard will eventually contain lots of useful information an Office365 administrator can use to ensure the smooth running of their tenant. First I’ll add in a mailbox size report and then expand it to report dormant user accounts, recently deleted accounts and anything else I find useful along the way. The beauty of using Excel is that anyone can put something together and best of all you don’t need any programming experience at all just point and click!
1. Lets jump in and ill explain things along the way but first launch Excel.
2. Select the Data menu then Click From Other Sources then From OData Feed.
3. In the Link for File text box enter the following URI which points to the Office365 reporting service: https://reports.office365.com/ecp/reportingwebservice/reporting.svc/ then enter the credentials of a tenant administrator account and press Next.
4. The wizard will query the service for all objects that we can work with. Scroll down the list and tick MailboxUsageDetail and press Next.
5. Here you can change the name of the data connection file but here we just leave them with the defaults. Click Always attempt to use this file to refresh data.
6. Here we want to display the data as a graph so select a PivotChart and press Properties.
7. Here we can choose the data refresh interval and choose to refresh the data when the file is opened. So every time the Excel file is opened it will use the connection file to collect the latest data from your Office365 tenant. Press OK.
8. A Chart will be added to the workbook but we need to make some changes to get the data displaying properly. In the right hand menu you can select the fields we want to display on the chart so here we select
9. Next move Username field to the Legend box, Date to the Axis.
10. Now the data is displaying in the correct areas, right click the chart and select Change Chart Type.
11. Lets choose a simple line chart.
12. Now right click each of the axis names and select Value Field Settings then rename them as desired.
13. Looking pretty nice! It’s a really nice and clear chart showing the size of each users mailbox usage over the last 30 days.
That’s all there is to creating a really useful chart in Excel showing user mailbox size trend in Excel using the Office365 reporting service. This is just the start so explore the reporting objects available and see what other charts you can build. If you create something useful then please share with me and ill add your charts to my own Office365 dashboard and share it for others to use.