DECLARE-CONNECTION

Function

Defines data source logon parameters prior to logon. Can be used to override default connection logon parameters.

Note:

DECLARE-CONNECTION is specific to Production Reporting DDO ports only.

Syntax

In the SETUP section:

DECLARE-CONNECTION connection_name
DSN={uq_txt_lit}
[USER={uq_txt_lit}]
[PASSWORD={uq_txt_lit}]
[PARAMETERS=keyword_str=attr_str;[keyword_str=attr_str;...]]
[NO-DUPLICATE=TRUE|FALSE] 
SET-GENERATIONS=({dimension1, hierarchy1}[,dimensioni, hierarchyi] ...)
SET-LEVELS=({dimension1, level1} [,dimensioni, leveli] ...)
SET-MEMBERS=({dimension1, level1} [,dimensioni, leveli] ...)
END-DECLARE

Arguments

connection_name

User-defined name for describing a datasource connection.

DSN

Logical datasource name recorded in the DDO Registry (Registry.properties file).

USER, PASSWORD

Traditional logon semantics.

PARAMETERS=keyword_str=attr_str;

List of keyword-attribute pairs required by a datasource driver for logon. There is no syntax restriction on these entries apart from the delimiting semi-colons (;) and equal signs (=). The keywords must match the logon property names listed for a datasource.

NO-DUPLICATE=TRUE|FALSE (default is FALSE)

(Optional) Prevents Production Reporting from automatically creating additional logins to datasources that are busy handling a previous query. Creating a new login in such cases is the default behavior for Production Reporting, which allows a single CONNECTION declaration to use in a subquery. This behavior, while allowing dynamic logins as-needed, causes difficulties when doing both DDL (BEGIN-SQL) and DML (BEGIN-SELECT) against temporary tables in certain vendors datasources. In such cases, you must fetch from the temporary table using the same login in which it was created. Here, you should code the CONNECTION as NO-DUPLICATE=TRUE, and then use that connection in both the table creation logic of BEGIN-SQL and the row fetching logic of BEGIN‑SELECT.

SET-GENERATIONS

Dimension hierarchy for the previously-declared dimension. The dimension and hierarchy defined with SET-GENERATIONS can be a literal value only. Consider the following example:

set-generations=('product',5,'time',1 )

In this example, SET-GENERATIONS:

Returns the set of members in the ‘product’ dimension that are at the 5th generation in the dimension’s hierarchy.
For example, returns all ‘Brand Name’ members (Generation Level 5) under the product hierarchy of ‘all products.drink.alcoholic beverages.beer and wine’. This would increase the result set to a list of beers and wines.
Returns the set of members in the ‘time’ dimension that are at the 1st generation deep into the dimension.
For example, returns all ‘Year’ members (Generation Level 1) under the time hierarchy of ‘1997.Q.2.’ This reduces result set to ‘1997’.

See “Set Generations” in Volume 3 of the Hyperion SQR Production Reporting Developer's Guide for detailed examples of SET-GENERATIONS.

SET-LEVELS

Extends the dimension hierarchy for the previously-declared dimension. The dimension and hierarchy defined with SET-LEVELS can be a literal value only. Consider the following example:

set-levels=('product',2 )

In this example:

SET-LEVELS used with only the previous SET-MEMBERS returns all members under the product hierarchy and the next two generations (Product SubCategory and Brand Name) for the product hierarchy of ‘all products.drink.alcoholic beverages.beer and wine’.
SET-LEVELS used with the previous SET-MEMBERS and SET‑GENERATIONS returns all members for generation levels 5 through 7 under the product hierarchy of ‘all products.drink.alcoholic beverages.beer and wine.’

See “Set Levels” in Volume 3 of theHyperion SQR Production Reporting Developer's Guide detailed examples of SET-LEVELS.

SET-MEMBERS

Returns the set of members in a dimension, level, or hierarchy whose name is specified by a string. The dimension and hierarchy defined with SET‑MEMBERS can be a literal value only. Consider the following example:

set-members=('product','all products.drink.alcoholic beverages.beer and wine','time','1997.Q1.2' )

In this example, SET-MEMBERS:

Returns the set of members in the dimension ‘product’ at the specific hierarchy of ‘all products’, at a specific level of ‘drink’, at a specific level of ‘alcoholic beverages’, at a specific level of ‘beer and wine’.
Returns the set of members in the dimension ‘time’ at the specific hierarchy of ‘1997’, at the specific level of ‘Q1’, at the specific level of ‘2’.

See “Set Members” in Volume 3 of the Hyperion SQR Production Reporting Developer's Guide for detailed examples of SET-MEMBERS.

Examples

declare-connection SAPR3-1
  dsn=SAPR3
  username=guest
  password=guest
end-declare