When using any of the Apply statements in conjunction with prompts used to select attribute element values, it is necessary that a value be returned as an argument into the function of the statement.
The following sections explain how each type of prompt may be used within an Apply statement:
Level prompts:
As level prompts are only designed to define the level at which a metric is calculated, they cannot be used within Apply statements.
Object prompts:
Object prompts may be used to select attributes. They can be used in Apply statements to select which attribute(s) to filter on, but they cannot be used to return attribute element values.
Filter prompts:
Filter prompts provide users with the option to select from an attribute element list, but they cannot be used because a single value is not returned from the prompt. The value returned from the prompt will always be in the form of "<attribute> in (<list of selected attribute elements>)."
For example, a filter prompt is used on an attribute element list within the report filter within the following report definition:
The prompt answer is then defined as follows:
The following SQL is returned:
select a11. AS ITEM_ID,
max(a12.) AS ITEM_NAME,
sum(a11.) AS WJXBFS1
from a11,
a12
where a11. = a12.
and a11. between a11. in (200306) and 200312
group by a11.
Because Apply statements are run directly against the warehouse database, an error will be returned when the report is executed. The database will not know how to interpret the value returned from the filter prompt, nor can the prompt answer be parsed prior to it being executed by the database.
Value prompts:
Currently, the only prompt that will return a single value is a Value prompt, and, therefore, is the only prompt that should be used within an Apply statement.
If the above report is redefined to use a value prompt in place of the filter prompt as follows:
and the prompt answer is defined as follows:
the following SQL will be returned:
select a11. AS ITEM_ID,
max(a12.) AS ITEM_NAME,
sum(a11.) AS WJXBFS1
from a11,
a12
where a11. = a12.
and a11. between 200306 and 200312
group by a11.
As this prompt returns the value entered by the user directly, the correct SQL will be generated and the report should execute properly.
