@Execute is a new function in IDT and can be used to filter the report data based on pre-defined condition and also it can be used to filter the report data based on the user using ‘BOUSER’ function.

@execution function uses to execute the list of values and it can work only in WHERE clause or Filter in IDT.

The syntax of the @Execute function is – @Execute(<List of values>)

< List of values> is a list of values pre-defined in the business layer or data foundation. The list of values definition provides the preliminary query. @Execute function is then included in a filter or WHERE clause to apply the preliminary query to limit the values returned in the main query.

The list of values can be any of the following types which are available in IDT

• List of values based on custom SQL
• Static list of values
• List of values based on a query that includes business layer objects

@Execute functions cannot be used in all the areas of IDT and there are some limitations to use @Execute functions.

• The list of values cannot be based on a custom hierarchy
• The list of values can contain only objects that are active in the business layer (not hidden or deprecated)
• The SQL defining the list of values cannot contain the @Execute function

The @Execute function cannot be used in the definition of a @Prompt function.

@Execute function Example – 1:

List of values created to filter the query results for the Branch which has more than 800 customers on a particular time frame and the list of values named as Cust_Avg.

Once the list of values is created, add the @execute function in WHERE clause and this list values would return more values hence as IN operator.

Bank_Info.Branch_id in (@Execute(Cust_avg))

The above filter works as like Bank_Info.Branch_id in (‘USA321’,’USA873’,’USA548’)

@Execute function Example – 2:

This example inserts a column filter that returns transaction data only for the geographical region of the current user.

Create list of values in the data foundation that returns the authorized Region codes for the current user. The list of values name is Authorized_Region, and the data type is numeric. This example assumes the database administrator has set up a table called Geo_Region in the database that associates authorized region with each user. The following SQL defines the list of values:

Now insert a column filter into the data foundation table Transaction and @Execute function can return multiple values hence use the IN operator in the filter definition.

Transaction_Fact.Region_Code in (@Execute(Authorized_Region))

When a user includes the Transaction table in a query, the @Execute function in the column filter is replaced by the list of authorized region codes for that user.

Note: You can also create the above statement as Filter in Business layer and use in query panel to limit / filter the report data.