You can define two types of Bound List parameters.
-
An SQL-based query to leverage data in the OneStream application or framework database or in an external data source.
-
A method-based query to use object-specific variables to refine and customize the member data to return.
To finish defining a method-based parameter:
-
In Default Value, enter the actual value used by default in the parameter, if no other value is specified. The default value must be part of the parameter’s definition.
You can either type the default value if known, use object lookup to create the default value, or use a cube view to select the appropriate formatting and copy the result to the Default Value field.
The Default Value property is the member you would set before defining a value using the Data Source properties specific to the selected parameter type.
-
Use Result Format String Type to determine if the default value or a custom value is used.
-
Default: Use the formatting defined in the Default Value property.
-
Custom: Use a format string that you enter in Result Custom Format String as the display text for the parameter.
Selecting Custom enables the Result Custom Format String property, where you can design the custom string.
-
-
In Result Custom Format String, optionally enter a string to use as the display text. Use String.Format syntax in .NET. For example, if a value is Blue and the format string is The color is {0}., the display text is The color is blue.
-
In Command Type choose one of the following:
-
Method to use a specified method type and query to define the command to be used in the parameter.
-
SQL Query to determine the database to use and define the SQL query.
Both selections enable additional properties to further define the command.
For a Method Command Type: Use these properties to define the database location if using a Method command type.
- Method Type: Select the object-specific variables to define how members are evaluated, so the most useful members are returned. See Data Adapters for more information on using the method type in a parameter definition.
-
Method Query: Customize variables to define which members to return and display in prompts. You can either type the default value if known, or use object lookup to create the default value. Use this syntax:
{Dimension type}{Dimension name}{Member Filter}{where clause}
For a SQL Command Type: Use these properties to define the database location if using a SQL command type.
-
Database Location: Determines the location of the database that includes the SQL command type. Select one of the following:
-
Application: Use the current OneStream Application database containing stage and financial cube data.
-
Framework Use the connected OneStream Framework database containing security and log data.
-
External Indicates that a database outside of OneStream is to be used.
If selecting External for the database, use the enabled External Database Connection property to select from the list of external databases. External database connections are defined in OneStream using the Server Configuration Tool. Select the external database connection you want to use from the list.
See Configuring Application Servers in the Installation Guide for information on configured application servers.
-
-
SQL Query: This is the SQL statement to run for the parameter. Enter a query that evaluates objects (such as members) to return a particular set. For example:
NOTE: If you do not know the method query syntax, leave this field blank and click
. This identifies the syntax location, runs the parameter, and provides a sample result set.
-
-
In Results Table Name, enter the name of the table generated when the data adapter runs. The default name is table.
-
In Display Member, enter one of the following:
-
Name to display the member by name.
-
Description to display the member's description.
-
Name and Description to display both the member name and the member description.
-
-
In Value Member, enter the name of the members, as used in the application, to correspond to each member displayed.
-
Click Save to save your property edits.
Sample Method-Based Parameter
The following image shows the definition of a parameter that retrieves Time members for an Application Analysis report.
Sample SQL-Based Parameter
The following image shows the definition of a parameter that generates a list of FX Rate Types for an FX Rates Audit report.
