When you are refreshing or submitting data in a Oracle Hyperion Smart View spreadsheet, you may receive the error:
- Essbase Error (1020021): Member [xxxxx] is out of place
This error occurs when Smart View finds a row/column/page header that is out of place in a Smart View Function or Ad Hoc Analysis grid. There are a few common causes and resolution paths that we will walk through below.
First, an ‘Active Connection’ may be inadvertently set on a Smart View Function-based tab. Setting up an ‘Active Connection’ on a Smart View Function-based tab is unnecessary (since the connection information is embedded in the function). The above error message results since Smart View will try to interpret the spreadsheet as an Ad Hoc Analysis grid.
- To remove the connection:
- Go to Smart View > Sheet Info > Delete.
- Select ‘Delete Worksheet Metadata’.
Second, whether this is a Smart View Function or Ad Hoc Analysis grid, there may be a member in an incorrect position in the row or columns headers, or a dimension may be duplicated. There are a large variety of issues that can cause headers to be misaligned. Here are just a few common items for your reference, but this list is not necessarily comprehensive:
- For Planning Ad Hoc Analysis sheets, you may receive errors for any comments you add into the grid. Planning Ad Hoc can be very sensitive and misinterpret your comments as headers.
- It is generally recommended to use Essbase Ad Hoc, which is more flexible for comments.
- For any Ad Hoc Analysis sheets, if you enter comments into the grid, but the comment cell exactly matches a member name, it will be misinterpreted as a header.
- We recommend appending a space to the end of the member name so that it will not be treated as a header.
- For any Ad Hoc Analysis sheets, if you enter a member header (free form) and type it incorrectly, Smart View may add the related dimension into the POV Bar, which can cause the error message after you correct the typo.
- For Smart View Function sheets, if you inadvertently forget to apply an anchor to a cell reference, you may receive the error message as well.
While these are just a few examples, there is a common path to troubleshooting:
- Unhide all rows and columns in the sheet. (If this is an Essbase Ad Hoc Analysis grid, go to the Essbase Ad Hoc tab and toggle off the POV Bar.)
- Review the worksheet to identify whether there are headers out of place.
- If the issue is not apparent, perform these steps to narrow down the issue:
- Save a copy of your file before proceeding.
- Delete rows and columns in the worksheet a little at a time.
- Attempt to refresh the spreadsheet to see if the error still occurs.
- Repeat until the error no longer occurs.
- This will isolate which rows/columns have the issue.
- Analyze the remaining row/column headers.
Hopefully these steps are helpful. Do you also experience slow performance in Smart View? If so, review the following resolutions:
- Speed up Smart View by over 2x using the Smart View Toolkit Excel Add-In
- Resolve common root causes for Smart View performance issues
Please contact our team for further support.