| XLCubed is now FluenceXL. The new wiki can be found here: https://help.fluencexl.com/ |
QueryEx
QueryEx or #queryex! is an error status that can be returned from XLCubed Formulae.
The message means that an error occured when running the query to retrieve the data requested by the formulae. The error is normally generated by the database/cube when it encounters an error in the query.
Contents
[hide]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 not access the requested members
- Formatting errors - converting a date to text and assuming the date format will be okay. e.g. 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
XLCubed 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 behaviour 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. The cell that generated the error may be on a hidden sheet, so when calculating cells individually each works fine, but when in a batch with a problem cell the error is triggered.
Diagnosing
In Excel or on the Web running a SQL Profiler trace will allow you to capture the query that causes the issue. If you run this in SQL Management Studio the error message will be 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 is 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.
Fixing
If the member names are incorrect they can be fixed, old "left over" formulae can be removed if they were used during report development.
If you put an invalid member name into a, XL3Member formula it will validate the name and return #mem! if it is invalid. You can safely reference the XL3Member formula from an XL3Lookup even if it returns #mem! in order to validate members. This is especially useful if you do not know which members will be valid for a user while developing the report.
