QueryEx or #queryex! is an error status that can be returned from Anaplan XL Reporting formulas.

The message means that an error occured when running the query to retrieve the data requested by the formula. The error is normally generated by the database/cube when it encounters an error in the query.

Sources

The most common source of this issue is having invalid members being referenced by the query. Common sources are:

  • Incorrect member names: Data entry errors.
  • Invalid member names: Security restrictions can mean that some users can't access the requested members.
  • Formatting errors: Converting a date to text and assuming the date format will be okay. For example develop a report in US-English and it errors when deployed to Germany.
  • Referencing empty cells: Results in members called [Dim].[Hier].[] or [Dim].[Hier].[0].

Multiple cells display the error

Anaplan XL will put multiple cells into batches to improve performance. If the query has an error, then all cells in the batch will return #queryex!.

Different behavior between Excel and Web

This strategy for batch calculations can differ between Excel and the Web, so you may see more cells with the error on the web. A cell may have caused the error on a hidden sheet. When the cells are calculated individually, they function properly, but when grouped together with a problematic cell, the error occurs.

Diagnose issues

In Excel or on the Web running an SQL Profiler trace will enable you to capture the query that caused the issue. If you run this in SQL Management Studio, the message is returned and you can use this to find the issue in the workbook.

In Excel extra information can be found in Extras > Trace formula errors. On the Web it's logged to the error.log file. In each case, the query text will be available, and any information that can be found about the cell that caused the problem.

Fix incorrect names

If the member names are incorrect, You can fix them. You can remove old formulas if they were used during report development.

If you put an invalid member name into an XL3Member formula, it'll validate the name and return #mem! if it's invalid. You can safely reference the XL3Member formula from an XL3Lookup even if it returns #mem! to validate members. This is especially useful if you don't know which members will be valid for a user while developing the report.