Limitations and restrictions

Microsoft Office limitations

General

Office.js API behavior does not fully support working with multiple workbooks. When you, for example, switch between workbooks, you might experience some unexpected side-effects due to event handling. Also, if you use worksheets with names that contain spaces or special characters, some functions might not work as expected.

We recommend that you download Microsoft Office updates from the Monthly Enterprise Channel. If you update from the Semi-Annual Enterprise Channel, then you are unlikely to download the latest version and will not have access to the latest functionality of Excel Integration.

Ad-hoc report

To enable all ad-hoc features to work correctly, you must use an Office version that supports Office.js API 1.12 or later. With older versions of Office, these limitations apply:

  • You can edit an ad-hoc report only if the report's top left cell (spillParent cell) with the INFOR.READ.SLICE function is selected, and if dynamic arrays are available.
  • If dynamic arrays are not available, only a single value is displayed. There is no spill of values over the cells in a report.
  • Expanding and collapsing do not work within a worksheet.

VBA limitations

If you use Office Integration custom functions in Excel VBA, be aware that custom functions are asynchronous and are calculated outside Excel. Therefore, some timing discrepancies can occur.

Some Excel VBA functions work only in Excel-native functions. For example, Application.Evaluate.

This list contains suggestions for using custom formulas in VBA:

  • When programming, ensure that custom functions have finished calculating.
  • Use the Application.CalculateFullRebuild function rather than the Application.Calculate function. Application.CalculateFullRebuild marks all cells that contain formulas as not calculated and triggers the rebuilding and recalculation of all dependencies. The calculation process can be longer than expected because it rebuilds dependencies and then calculates all workbooks.
  • Insert custom formulas into =IF statements in an Excel workbook and reference a cell with a TRUE or FALSE function. This is an alternative to using the Application.CalculateFullRebuild function and enables you to select which custom function to calculate at a specific time. This results in a faster calculation process.

Microsoft Office Online

These limitations apply when you use the Office Integration add-in online:

  • Only Excel online is supported.
  • Mac users can use the Safari browser only if the Prevent cross-site tracking option is cleared on the Privacy tab in the browser settings.
  • Only reports with a small number of custom functions are supported. See https://github.com/OfficeDev/office-js/issues/2786, for example.
  • Slow response, especially to calculations and calculation notifications. To enhance performance, we recommend that you use manual calculation instead of automatic calculation.
  • If you do not set embedding URLs or set them incorrectly, an error is displayed after you click Show Taskpane. To fix this issue, you must set correct URLs, clear cookies for the Excel online page, and reload it. Then sign in to the add-in.
  • If you click Reference a cell for a selected cell and then you click a different cell, the cell reference does not change automatically.
  • In the add-in user interface, the right-side menu with options such as Get Support or Reload is not available.
  • Because of Excel online resource limits and performance optimization, converting a large ad-hoc report to a cell-based report can result in errors. For example, an exceeded request payload size. See https://learn.microsoft.com/en-us/office/dev/add-ins/concepts/resource-limits-and-performance-optimization#excel-add-ins.
  • If you add the add-in from a shared folder, you must upload the manifest file manually each time when you want to use the add-in online.
  • Expired Excel online sessions can cause fatal errors to the add-in or can prevent you from signing out. If a fatal error occurs, reload the browser or upload the add-in again.
  • You cannot perform expand and collapse actions in an ad-hoc report when the Excel zoom is not set to 100%.
  • In the ribbon area Excel online does not show all tooltips and it does not react to all click events.

Office Integration limitations

General

Do not use Excel Integration with another Office add-in installed. Additional Office add-ins can cause unexpected issues.

In the report the calculation progress is visible only when task pane is open.

The Excel ribbon does not support localization.

Keyboard shortcuts work only when the focus is placed over the report cells.

Ad-hoc report

These limitations apply:

  • If you update an ad-hoc report's structure but do not click Update report, then, if you cut and paste the report, your changes are lost.
  • A cell-based ad-hoc report cannot contain more than 1.5 million cells. If this number is reached, you cannot convert the report from a slice to cell-based. You must adjust the report to do the conversion.
  • After you convert a report from a slice to cell-based, the slice formula is removed. You cannot undo this action.
    Caution: 
    If you convert an ad-hoc slice report that shows a #SPILL error, to a cell-based report, then the report's content is overwritten.
  • Element and dynamic selections do not work with Microsoft Analysis Services.
  • Entities are supported only on MS365 Office.

Calculations from Application Studio in Self-Service report definitions

Self-Service report definitions that contain calculations imported from Application Studio are currently not supported in Excel Integration.

Custom Functions

INFOR.WRITE.VALUE does not support Multidimensional Expressions (MDX) except for DefaultMember.

If writeback is disabled and you modify the INFOR.WRITE.VALUE function, a #VALUE! error is displayed.

INFOR.GET.AXIS and INFOR.READ.VALUES functions do not support referencing of entities.

Writeback

These features and operations are not supported:

  • Using multiple-cell copy or paste operations for writeback.
  • Dragging values over cells that contain formulas with the INFOR.GET.VALUE function.
  • Writing back values in multiple non-contiguous cells.
  • Splashing undo.
  • Writeback that uses an MDX expression instead of the element unique name except for DefaultMember.
Note:  Especially when working with larger reports, you may experience small delays. To avoid errors, adjust the speed at which you enter data accordingly.

The JSON string that is used in the WritebackBehavior custom setting to enable or disable writeback mode is limited to 255 characters. You can compress JSON strings that contain more characters with JSON Minifier.

See https://codebeautify.org/jsonminifier.

Contextual perspectives

These limitations apply for the contextual perspectives when you define the report data sources:

  • You cannot specify unique names in the Domain and filter driver dimension fields.
  • When you switch between reports or formulas, the domain and filter drivers that you selected are cleared from the selection.

Calculation limits

A query cannot exceed two million values.

A query can be, for example, an ad-hoc report, all formulas within a cell-based report, or items within an element tree. A value can be, for example, a cell or member property. If, for example, a formula queries five attributes for a single element, it is counted as five values.

The maximum number of elements visible in an element tree depends on the number of attributes defined within a specific hierarchy. Typically, that maximum number can range to tens of thousands. The greater number of attributes within a hierarchy, the lesser maximum number of elements that are displayed in an element tree.

You are notified each time a query limit is reached, for example:

  • If you expand an ad-hoc report and you reach the limit, the report expanding is prevented and a notification is displayed.
  • If a formula fails to calculate because of the limit, a #VALUE! error is displayed. In Office 365, you can pause on a cell that shows the #VALUE! error to view the error details.

All cases of reaching the query limit are logged in the application logs (user logs).

To avoid reaching the query limit, we recommend that you create several workbooks with small amounts of data rather than one workbook with a big amount of data. For ad-hoc reports, we recommend that you limit the selections through levels or static selections combined with dynamic selections, for example, Year 2016 and children of Year 2016.