In Generate Office 365 Reports using PowerShell on the Petri IT Knowledgebase, I gave you a quick overview of the reporting capabilities in the Office 365 web portal, along with steps on how to run the same pre-defined reports using Windows PowerShell.
In today’s Ask the Admin, I’ll show you one more option for viewing Office 365 reports using Microsoft Excel, which is a good alternative for organizations that don’t want to write PowerShell code, but need to have a solution that’s a little more flexible than what’s available in the portal.
There are three ways to tap into the Office 365 reporting service: using the Office 365 portal, Windows PowerShell or another language, and Microsoft Excel. Keep in mind that each method has its own shortcomings.
Using the pre-defined reports in the web portal is the quickest and easiest way to get started with Office 365 reporting, but there are some significant limitations:
The most flexible way to generate reports is using PowerShell, and it can be used to create custom reports by connecting to the Office 365 reporting service, allowing administrators to determine the data that’s returned, how it’s grouped and sorted, and also the ability to save reports.
The results of the available pre-defined results can be viewed in Excel, by making a connection to the Office 365 reporting service. Custom reports cannot be accessed this way, and the amount of data returned can’t be customized, grouped, filtered or sorted before the results are returned. But Excel can be used to manipulate the results, giving administrators more flexibility than viewing reports in the Office 365 portal.
To get started, you need Microsoft Excel installed on your local management PC, and an Office 365 subscription. Don’t forget that reporting is not available in all plans, so check out Microsoft’s website to see if reporting is available in your subscription. Additionally, not all reports are available in all plans.
If you are authenticated successfully, you’ll be presented with the Select Tables page, where each table on this page represents a report.
Alternatively, you can change the location of the data connection file, which is saved by default in the My Data Sources folder. It’s also possible to save the password for the data feed in the file, by checking Save password in file.
Now that the data has been imported into Excel, you can manipulate it as required.