For Admin portal 1.31 or higher.
For Excel 2016 or later
Open a new excel sheet
- Go to Data tab
- Select Get Data (depending on the Excel version you are using the name may vary).
- Select From Other Sources
- Select From OData Feed
Choose Basic and enter the Url of the portal you are using. In this example, we are using the test environment.
Note! The url is case sensitive and its very important that the url is exactly right!
Select Basic Authentication and enter your credentials.
Please note that the User name is the portal's short name, backslash, and username for the portal (shortname\APUser). The user you use needs to be available at the Admin portal with enough permission to do this connection. It's recommended to use an Admin account.
If the connection works a new window will appear. Click Load to continue the import of data.
All data is now loaded into the excel sheet and can be changed with a pivot table or by hiding columns you don't need at the moment.
To refresh the excel sheet and get the latest data, use Refresh under the Data Tab.
Historical reporting data are not taken from real data, so they are generated every 2 hours (or you can speed it up by starting the task Generate data for raw transactions report at the AP).
If you enter the wrong username/password or if you need to connect to a different site, the cache in Excel might stop you from doing that. You solve it easily by going to Data Tab > Get Data > Data Source Settings > Clear Permissions.
Select the url for your site and click Clear Permissions, then try again!