In this article, learn how to resolve common issues using OData for Excel. To know more about setting up OData, see the following:

OData Connection Setup 

OData with Excel 

OData with Excel Advanced Report Building Tips 

Also, see the following videos:

OData Connector setup and User Profile setup (23.1) - AIQ Academy

Generate OData User Credentials (23.2) - AIQ Academy

Use OData Connector with PowerBI (23.3) - AIQ Academy

Using OData connector with Excel (23.4) - AIQ Academy

I am trying to load a report. Why do I get the error “Access to the resource is forbidden”?

Check that you have the required permissions set in Maintain Menu Profiles > OData for the relevant entity. 

How do I revoke user access to OData?

As an Administrator:

• Revoke access for all users to a particular company, by going to Setup > Company Details & Settings > Integration and clicking Disable the Power BI, Excel OData, and endpoint configuration.
• Revoke access to specific datasets within an entity by selecting Maintain Menu Profiles for a specific entity, selecting the OData tab on the resulting screen, and de-selecting the relevant datasets.

As a User:

• Revoke your own access (for example if you are worried that your login credentials have been compromised) by logging into the company, going to Analysis > Data Connection Setup, and clicking Revoke for the relevant password.

I have forgotten my login credentials or password

Access your OData Connection URL and Username at any time in the Data Connection Setup screen.

If you did not record your password when you generated it at setup, you will need to do so again. For more details, see OData Connection. You will be prompted to enter the new password when refreshing any existing reports after disabling the old password. For security reasons we recommend also revoking the old password as described above. 

I’ve successfully run OData but I cannot see any of the ‘F(x)’ Formula reports, only the Static Data reports like Suppliers listing, and General Ledger Reports.

Not all Excel versions/subscriptions support all OData functions. The following may not support the remainder reports:

• Excel for Mac
• Tableau
• Python Fetch commands using OData

Power BI might be an alternative in some cases.

To check whether it is a problem with your Microsoft software, connect to this public test URL via Excel:

You should see the following sample list of function datasets if your software version / subscription supports them:

If the data for the selected dataset does not appear when you select it in the Navigator, check that you have selected all the required parameters. In the case of CategoryTypeYearTransactionLines and CategoryYearTransactionLines, ensure you enter a CategoryType (even though the field says it is optional).

I’ve set up my connection table. Query looks like it is loading or is stuck on loading, but nothing is loading in the Queries and Connections screen.

In the Queries and Connections screen, right-click on the selected table. Select the Load To option and re-select the options you want to add to your Data Model. This should restart the process, and the table should now load.

Can I have multiple Power Pivot Tables per Excel Worksheet? 

Yes, though you will need to be careful where to position an additional table, as it cannot interfere with the existing pivot table layout. For instance, it can be put beside an existing table to create two Pivot Tables, one for P&L MM and YTD and another showing All Periods. If it is added below an existing Pivot Table, the system will warn if there is a danger of overlapping an existing table via filters added etc.