SYMPTOM:
In Strategy 9.x, a passthrough function, such as ApplySimple, cannot be used on a prompt in a report based off of an Intelligent Cube. If users attempt to apply a prompt with a passthrough function in the report filter, an error message will appear in the prompt screen, as shown below.
Unkown functionFunction not available in DBMS. Invalid logical functions used in filter. Filter functions can only be Equal, And, Or, and Not.
CAUSE:
This is working as designed. Passthrough functions are used to write custom SQL (or other query languages) that is processed in the database. When an element prompt is on an Intelligent Cube report the prompt evaluates itself just against the in memory Intelligent Cube. This is both to improve performance and to ensure that attribute elements are only shown if they are present in the Intelligent Cube. As the prompt is processed in the Analytical Engine there is no way for the filter to be evaluated as Strategy cannot process custom SQL.
Note: If using the same prompt object across multiple cube reports in a document, the prompt will resolve against the database and therefore passthrough functions will work. However, the underlying reports will display the error message when run individually.
ACTION:
Change the filter in the prompt to not use a passthrough function. The filter must be built in such a way that it can be evaluated in the Strategy Analytical Engine. Any function supported in the Analytical Engine can still be used in the filter qualification. In the situation where a function is needed that is only present in the database a workaround must be used. A field (attribute or metric) must be created which contains the necessary processed output of the database only function and syntax. This field can then be used with regular Strategy functions in the filter of the prompt. This attribute/metric can be hidden from regular users on the Intelligent Cube through use of ACLs.
