Connecting
Relevant to the Analyst or Submitter roles.
Analyst - This user is able to analyse the data of all expenses in Microsoft Excel or Bower BI.
Submitter - this user is able to analyse the data of only their own expenses in Microsoft Excel or Bower BI.
Refer to Connecting to the reporting feed for more details.
|
The Zetadocs Expenses Reporting feed uses the OData standard, which enables analysis of expense data using a range of popular reporting tools, including Microsoft Excel and Power BI.
Creating a reporting user
We recommend one of the following two options depending on your specific reporting requirements;-
•Use your own existing account for reporting - this is best if reports will only be run by one user (or by a small number of users who enter their own credentials).
1.If this is an existing user, ensure the Authentication Method is set to Microsoft or Zetadocs Account. Otherwise, create a new user with this option set.
2.Add the Analyst role (if you want to include expenses for all submitters - if this role is not set, the user will only have reporting access to their own expense data.).
3.If this is a new user, send a Welcome email and set the password.
•Use a separate shared account for reporting - this is best for a finance team who already use a shared account with a password for the finance Processor role and want to share the reporting spreadsheet and reporting responsibility more easily.
1.Create a new user just for reporting - the user's Authentication Method must be set to Microsoft or Zetadocs Account,
2.Set the Analyst role only,
3.Send a Welcome email and set the password.
Please refer to the Users help topic to learn more about setting up users.
Connect to the Reporting feed with these simple steps;-
Microsoft Power BI (Desktop only)
1.Open Power BI Desktop
2.Home ribbon > Get Data button > OData Feed
3.Enter the URL: https://expenses-odata.zetadocs.com/odatav4/v1
4.The OData feed options will now be shown – choose Basic
5.Enter the username and password of the designated reporting user account
6.Choose Connect to establish a connection to the Reporting feed
7.Select the Expenses table
8.Choose Load to return ALL the expense data or choose Transform Data to filter the data (e.g. only return this month's expenses) before loading it into PowerBI
Tip: Filtering the data to retrieve fewer records will reduce the time it takes to load the dataset.
|
Choosing the Transform Data option launches Microsoft's Power Query Editor.
From within here you can filter the data before loading it into PowerBI.
You can apply a simple filter, or several different filters - this gives you the power to be very specific and only load the relevant records you need.
Note: Only the Expense Date and Approval Time columns support date filtering.
|
Below are two simple ways you could filter your data prior to loading it into PowerBI.
Load Last Month's Expenses
1.Tap the down pointing arrow to the right of the Expense Date column - Sorting and Filtering options will be shown for this column. 2.Tap Date Filters > Month > Last Month 3.The grid will now be filtered to only show a list of last month's expenses 4.Click Close & Save
Load Expenses in the previous x days
1.Tap the down pointing arrow to the right of the Expense Date column - Sorting and Filtering options will be shown for this column. 2.Tap Date Filters > In the Previous - a Filter Rows window is shown 3.Enter the number of days you want to filter by 4.Click OK 5.The grid will now be filtered 6.Click Close & Save |
9.The data feed will now load into PowerBI
Note: Microsoft Power BI retains the user credentials you've used for each your data sources. This removes the need for you to re-enter them each time you can to access your data.
Click here to learn how to change the user credentials stored against your data source.
1.Navigate to File > Options and Settings > Data Sources Settings 2.Choose the data source you would like to change 3.Choose Edit Permissions... button 4.Choose the Edit... button 5.The OData feed options will now be shown – choose Basic 6.Enter the username1 and password you use to connect to Zetadocs Expenses 7.Choose Connect to establish a connection to the Reporting feed 8.Your Data Source will now use these credentials in the future |
|
Microsoft Excel
1.Open Excel
2.File > New
3.Data ribbon > Get Data button > From Other Data Sources > From OData feed
4.Enter the URL: https://expenses-odata.zetadocs.com/odatav4/v1
5.The OData feed options will now be shown – choose Basic
6.Enter the username and password of the designated reporting user account
7.Choose Connect to establish a connection to the Reporting feed
8.Choose Load
9.Select the Expenses table
10.Choose Load to return ALL the expense data or choose Transform Data to filter the data (e.g. only return this month's expenses) before loading it into Excel
Tip: Filtering the data to retrieve fewer records will reduce the time it takes to load the dataset.
|
Choosing the Transform Data option launches Microsoft's Power Query Editor.
From within here you can filter the data before loading it into Excel.
You can apply a simple filter, or several different filters - this gives you the power to be very specific and only load the relevant records you need.
Note: Only the Expense Date and Approval Time columns support date filtering.
|
Below are two simple ways you could filter your data prior to loading it into Excel.
Load Last Month's Expenses
1.Tap the down pointing arrow to the right of the Expense Date column - Sorting and Filtering options will be shown for this column. 2.Tap Date Filters > Month > Last Month 3.The grid will now be filtered to only show a list of last month's expenses 4.Click Close & Save
Load Expenses in the previous x days
1.Tap the down pointing arrow to the right of the Expense Date column - Sorting and Filtering options will be shown for this column. 2.Tap Date Filters > In the Previous - a Filter Rows window is shown 3.Enter the number of days you want to filter by 4.Click OK 5.The grid will now be filtered 6.Click Close & Save |
11.The data feed will now load into Excel
Note: Microsoft Excel retains the user credentials you've used for each your data sources. This removes the need for you to re-enter them each time you can to access your data.
Click here to learn how to change the user credentials stored against your data source.
1.Navigate to Get Data button > Data Sources Settings 2.Choose the data source you would like to change 3.Choose Edit Permissions... button 4.Choose the Edit... button 5.The OData feed options will now be shown – choose Basic 6.Enter the username1 and password you use to connect to Zetadocs Expenses 7.Choose Connect to establish a connection to the Reporting feed 8.Your Data Source will now use these credentials in the future |
|
Blog Articles
https://www.equisys.com/blog/seeing-the-wood-for-the-trees