Oct 13, 2009 When you are shown the initial bip estimates (B12, B21, Alpha, A12, A21) you will notice that we only give initial bounds and values for B12,B21, and alpha. You could give initial values and bounds of -100 to 100 for the other two bips to let CHEMCAD try a 5 parameter regression. UNIFAC T=275-475K, P=0-4 atm VLE,LLE,Predictive UNIQUAC Highly non-ideal VLE,LLE NRTL Highly non-ideal, VLE,LLE Wilson Highly non-ideal, VLE T. Wilson Highly non-ideal, VLE,LLE Hiranuma(HRNM) Highly non-ideal, VLE,LLE Wilson Salt Non-ideal solutions with salts dissolved Margules Highly non-ideal, VLE,LLE (4 suffix) Van Laar Moderately non-ideal.
|
Abstract
BIP9 introduced a mechanism for using the version bits to signal support forbackwards-compatible changes (soft-forks) using a tally over the previous 2016blocks computed at re-targeting intervals. It provided for a fixed threshold andnon-configurable lock-in interval applicable to all deployments on a chain.
This document describes a generalized signaling scheme which allows eachsignaling bit to have its own configurable threshold, window size (number ofblocks over which it is tallied) and a configurable lock-in period.
It extends the semantics of the signaling bits to cover arbitrary consensuschanges, referred to under the general term 'forks'. The same rangeof version bits is used for signaling.
The states of the BIP9 state machine and its original parameters (name, bit,starttime, timeout) are retained. Some state transition conditions areextended by additional parameters ('threshold', 'windowsize', 'minlockedblocks','minlockedtime') to provide for fine-tuning of threshold and grace period.
Motivation
The Bitcoin protocol requires a flexible scheme for finding consensus onprotocol changes, to ensure that it can adapt to the needs of the market andremain competitive as an electronic payment system.
While BIP9 has served the community well for previous deployments, there aresome shortcomings in its approach:
- it specifically applies only to backward-compatible changes
- its fixed 95% threshold is not flexible enough to allow for a 'spectrum of contentiousness' to be represented
- small minorities can veto proposed changes, which can lead to undesirable stagnation
The proposal should allow more freedom of choice in activation strategieswhile remaining backward compatible with respect to existing BIP9-baseddeployments.
Terms and conventions
The version bits used by this proposal for signaling deployment of forks arereferred to as 'signaling bits' or shortened to 'bits' where unambiguous.
All times in this specification are in seconds since the epoch [1].Durations / time offsets are in seconds.
The term 'MTP' refers to the 'median time past' which is calculated as themedian nTime of a block and its 10 predecessors. It is treated as a monotonicclock defined by a chain, and evaluated on the ancestor of a block, i.e.
MTP := GetMedianTimePast(block.parent)
The key words 'MUST', 'MUST NOT', 'REQUIRED', 'SHALL', 'SHALL NOT','SHOULD', 'SHOULD NOT', 'RECOMMENDED', 'MAY', and 'OPTIONAL' in thisdocument are to be interpreted as described in RFC 2119.
Specification
Backward compatibility
This specification SHALL enable strict backward compatibility with existingBIP9-based deployments through suitable parameter configuration. Any part ofthe specification preventing full backward compatibility SHALL be consideredas erroneous and amended.
As before, a set of configuration parameters SHALL exist for the version bitsfor each chain supported by an implementation. This permits each bit to beconfigured independently for each chain (mainnet, testnet, etc.)
Signaling bits
The signaling bits SHALL comprise the 29 least significant bits of thenVersion block header field. nVersion is a 32-bit field which is treated asa little-endian integer.
Signaling bits SHALL be assigned numbers from 0..28 ranging from the leastsignificant (bit 0) to the most significant (bit 28) in the range.
The top 3 bits of nVersion MUST be set to 001 , yielding a range of possiblenVersion values between [0x20000000...0x3FFFFFFF], inclusive.
If a block's nVersion does not have its top 3 bits set to 001, all its signalingbits MUST be treated as if they are 0 (see also: 'Tallying' section below).
Deployment states
With each block and fork, we associate a deployment state.The possible states are:
- DEFINED is the first state that each fork starts out as. The genesis block for any chain SHALL by definition be in this state for each deployment.
- STARTED for blocks past the starttime.
- LOCKED_IN after STARTED, if at least threshold out of windowsize blocks have the associated bit set in nVersion, measured at next height that is evenly divisible by the windowsize.
- ACTIVE for all blocks after the grace period conditions have been met.
- FAILED if past the timeout time and LOCKED_IN was not reached.
Fork deployment parameters
Each fork deployment is specified by the following per-chain parameters:
- The name specifies a very brief description of the fork, reasonable for use as an identifier. For deployments described in a single BIP, it is recommended to use the name 'bipN' where N is the appropriate BIP number.
- The bit determines which bit in the nVersion field of the block is to be used to signal the fork deployment. It is chosen from the set {0,1,2,...,28}.
- The starttime specifies a minimum median time past (MTP) of a block at which the bit gains its meaning.
- The timeout specifies a time at which the deployment is considered failed. If the MTP of a block >= timeout and the fork has not yet locked in (including this block's bit state), the deployment is considered failed on all descendants of the block.
- The windowsize specifies the number of past blocks (including the block under consideration) to be taken into account for locking in a fork.
- The threshold specifies a number of blocks, in the range of 1..windowsize, which must signal for a fork in order to lock it in. The support is measured when the chain height is evenly divisible by the windowsize. If the windowsize is set to 2016 (as in BIP9) this coincides with the 2016-block re-targeting intervals.
- The minlockedblocks specifies a minimum number of blocks which a fork must remain in locked-in state before it can become active. Both minlockedblocks and minlockedtime (see below) must be satisfied before a fork can become active.
- The minlockedtime specifies a minimum grace time, an earliest time after lock-in at which the fork can become active. If the MTP of a block >= (minlockedtime + median time of the block that locked in the fork), then the fork becomes activated. Both minlockedtime and minlockedblocks (see above) must be satisfied before a fork can become active.
Tallying
If a block's nVersion does not have its top 3 bits set to 001, all its signalingbits MUST be treated as if they are '0'.
A signaling bit value of '1' SHALL indicate support of a fork and SHALL counttowards its tally on a chain.
A signaling bit value of '0' SHALL indicate absence of support of a fork andSHALL NOT count towards its tally on a chain.
The signaling bits SHALL be tallied whenever the head of the active chainchanges (including after reorganizations).
State transitions
The following diagram illustrates the generalized state machine:
NOTES:
The genesis block of any chain SHALL have the state DEFINED for each deployment.
A given deployment SHALL remain in the DEFINED state until it either passes thestarttime (and becomes STARTED) or the timeout time (and becomes FAILED).
Once a deployment has STARTED, the signal for that deployment SHALL be talliedover the the past windowsize blocks whenever a new block is received on thatchain.
A transition from the STARTED state to the LOCKED_IN state SHALL only occurwhen all of these are true:
- the height of the received block is an integer multiple of the window size
- the MTP is below the timeout time
- at least threshold out of windowsize blocks have signaled support
A transition from LOCKED_IN to ACTIVE state SHALL only occur if the heightsynchronization criterion is met and two configurable 'grace period' conditionsare fulfilled:
- current height MUST be at least minlockedblocks above LOCKED_IN height
- MTP must exceed LOCKED_IN time by at least minlockedtime seconds
The ACTIVE and FAILED states are terminal; a deployment stays in these statesonce they are reached.
Deployment states are maintained along block chain branches.They need re-computation when a reorganization happens.
New consensus rules
New consensus rules deployed by a fork SHALL be enforced for each block that hasACTIVE state.
Optional operator notifications
An implementation SHOULD notify the operator when a deployment transitionsto STARTED, LOCKED_IN, ACTIVE or FAILED states.
It is RECOMMENDED that an implementation provide finer-grained notificationsto the operator which allow him/her to track the measured support level fordefined deployments.
An implementation SHOULD warn the operator if the configured (emitted) nVersionhas been overridden to contain bits set to '1' in contravention of the abovenon-signaling recommendations for DEFINED forks.
It is RECOMMENDED that an implementation warn the operator if no signal hasbeen received for a given deployment during a full windowsize period after thedeployment has STARTED. This could indicate that something may be wrong withthe operator's configuration that is causing them not to receive the signalcorrectly.
For undefined signals, it is RECOMMENDED that implementation track these andalert their operators with supportive upgrade notifications, e.g.
- 'warning: signaling started on unknown feature on version bit X'
- 'warning: signaling on unknown feature reached X% (over last N blocks)'
- 'info: signaling ceased on unknown feature (over last M blocks)'
getblocktemplate changes
The getblocktemplate features introduced in BIP9 remain in effect unmodified.
Rationale
The timeout into FAILED state allows eventual reuse of bits if a fork was notsuccessfully activated.
A fallow period at the conclusion of a fork attempt allows some detection ofbuggy clients, and allows time for warnings and software upgrades forsuccessful forks. The duration of a fallow period is not specified by thisproposal, although a conventional fallow period of 3 months is RECOMMENDED.
Due to the constraints set by BIP 34, BIP 66 and BIP 65, there are only0x7FFFFFFB possible nVersion values available. This limits to at most 30independent deployments.By restricting the top 3 bits to 001 we we are left with 29 out of those forthe purposes of this proposal, and support two future upgrades for differentmechanisms (top bits 010 and 011).
Guidelines
Parameter selection guidelines
The following guidelines are suggested for selecting the parameters for a fork:
- name SHOULD be selected such that no two forks, concurrent or otherwise, ever use the same name.
- bit SHOULD be selected such that no two concurrent forks use the same bit. Implementors should make an effort to consult resources such as [2] to establish whether the bit they wish to use can reasonably be assumed to be unclaimed by a concurrent fork, and to announce their use ('claim') of a bit for a fork purpose on various project mailing lists, to reduce chance of collisions.
- starttime SHOULD be set to some date in the future, approximately one month after a software release date which includes the fork signaling. This allows for some release delays, while preventing triggers as a result of parties running pre-release software.
- timeout is RECOMMENDED to be 1 year (31536000 seconds) after starttime.
- windowsize SHOULD be set large enough to allow reception of an adequately precise signal. A good high-resolution value would be 2016 blocks as used in BIP9. It is NOT RECOMMENDED to use a windowsize less than 100 blocks.
- threshold SHOULD be set as high as possible to ensure a smooth activation based on the estimated support and the nature of the proposed changes. It is strongly RECOMMENDED that threshold >= windowsize / 2 (rounded up) to ensure that a proposal is only activated by majority support.
- minlockedblocks is RECOMMENDED to be set >= windowsize, to ensure that a full window passes in LOCKED_IN state. Lower values will be ineffective as the transition from LOCKED_IN to ACTIVE is guarded by a synchronization based on the window size.
- minlockedtime SHOULD only be set > 0 if a minimum LOCKED_IN time period needs be strictly enforced. It is permissible to set minlockedblocks to 0 and only specify minlockedtime, however the synchronization condition means the grace period can only expire once the time has passed AND the chain height is a multiple of the windowsize.
A later deployment using the same bit is possible as long as the starttime isafter the previous fork's timeout or activation, but it is discouraged untilnecessary, and even then recommended to have a pause in between to detectbuggy software.
Signaling guidelines
An implementation SHOULD signal '0' on a bit if one of the following holds true:
- the deployment parameters are not DEFINED (not configured or explicitly undefined)
- the deployment is DEFINED and has not yet reached the STARTED state
- the deployment has succeeded (it has become ACTIVE)
- the deployment has FAILED
An implementation SHOULD warn the operator if the configured (emitted) nVersionhas been overridden to contain bits set to '1' in contravention of the abovenon-signaling recommendations.
A supporting miner SHOULD signal '1' on a bit for which the deploymentis LOCKED_IN state so that uptake is visible. However, this has no effect onconsensus rules.Once LOCKED_IN, a deployment proceeds to ACTIVE solely based on the configuredgrace period parameters (see 'Fork deployment parameters' above).
A miner SHOULD signal '0' on a bit if they wish to suspend signaling of supportfor a fork that is DEFINED in their software.
It is NOT RECOMMENDED to signal '1' for bits where the meaning is undefined(i.e. bits which are unclaimed by proposals).
Settings for BIP9 compatibility
This section lists parameter values which can be used to effect compatibilitywith the existing BIP9 versionbits state machine.
The following table describes mainnet compatibility options (95%, 2016 blocks):
Parameter | BIP9 value | BIP135 value |
---|---|---|
name | some_name | some_name |
bit | b | b |
starttime | T_start | T_start |
timeout | T_timeout | T_timeout |
windowsize | n/a | 2016 |
threshold | n/a | 1916 |
minlockedblocks | n/a | 2016 |
minlockedtime | n/a | 0 |
The following table describes testnet compatibility options (75%, 2016 blocks):
Parameter | BIP9 value | BIP135 value |
---|---|---|
name | some_name | some_name |
bit | b | b |
starttime | T_start | T_start |
timeout | T_timeout | T_timeout |
windowsize | n/a | 2016 |
threshold | n/a | 1512 |
minlockedblocks | n/a | 2016 |
minlockedtime | n/a | 0 |
Deployment
As this BIP is not itself consensus-relevant (Information like BIP9), it canbe rolled out without the use of a BIP9 fork bit.
Backward compatibility through judicious fork configuration parameters shouldensure that it does not interfere with existing known deployments.
By way of design it does not interfere with unknown (undefined) deployments.
Reference implementation
A working reference implementation, including tests, can be found in these Pull Requests:
Existing unit tests and regression tests have been left active to demonstratebackward compatibility of the default settings with BIP9.References
[1] http://pubs.opengroup.org/onlinepubs/9699919799/xrat/V4_xbd_chap04.html#tag_21_04_16
[2] List of existing BIP9 deployment proposals
Copyright
This BIP is dual-licensed under the Creative Commons CC0 1.0 Universal andGNU All-Permissive licenses.
This chapter describes how to add parameters and lists of values to a BI Publisher data model.
This chapter includes the following sections:
4.1About Parameters
Adding parameters to a data model enables users to interact with data when they view reports.
BI Publisher supports the following parameter types:
- Text — the user enters a text string to pass as the parameter.
- Menu — the user makes selections from a list of values. A list of values can contain fixed data that you specify or the list can be created using a SQL query that is executed against any of the defined data sources. This option supports multiple selections, a 'Select All' option, and partial page refresh for cascading parameters.To create a menu type parameter, define the list of values first; then define the parameter and associate it to the list of values. See Section 4.4, 'Adding Lists of Values.'
- Date — the user selects a date as a parameter. Note that the data type must also be 'Date' and the format must be Java date format.
Once you have defined the parameters in the data model, you can further configure how the parameters are displayed in the report as a report-level setting. For more information about the report-level settings, see the section 'Configuring Parameter Settings for the Report' in Oracle Fusion Middleware Report Designer's Guide for Oracle Business Intelligence Publisher.
Support for parameters varies based on the data set type. SQL Query data sets support the full set of available parameter features. Other types of data sets may support all, none, or a subset of these features. Table 4-1 summarizes what is supported for each data set type.
Table 4-1 Parameter Support by Data Set Type
Data Set Type | Parameter Support | Multiple Selection | Can Select All | Refresh Other Parameters on Change |
---|---|---|---|---|
SQL Query | Yes | Yes | Yes | Yes |
MDX Query | No | No | No | No |
Oracle BI Analysis | Inherited from Oracle BI Analysis | Yes (via Oracle BI Dashboards) | Yes (via Oracle BI Dashboards) | Yes (via Oracle BI Dashboards) |
View Object | Yes, provided that the view object supports and is designed for it | No | No | Yes (view object parameters only) |
Web Service | Yes | No | No | No |
LDAP Query | Yes | No | No | No |
XML File | No | No | No | No |
Microsoft Excel File | Yes | No | No | No |
CSV File | No | No | No | No |
HTTP (XML Feed) | Yes | No | No | No |
4.2 Adding a New Parameter
To add a new parameter:
- On the Data Model components pane, click Parameters and then click Create new Parameter, as shown in Figure 4-1.
- Enter a Name for the parameter. The name must match any references to this parameter in the data set.Note:The parameter name you choose must not exceed the maximum length allowed for an identifier by your database. Refer to your database documentation for identifier length limitations.
- Select the Data Type from the list. A Date data type only support a Date Parameter Type. The other data types support a Parameter Type of either Text or Menu:
- String
- IntegerNote:The Integer data type for parameters is a 64-bit sign integer. It has a value range of -9,223,372,036,854,775,808 to a maximum value of 9,223,372,036,854,775,807 (inclusive).
- Boolean
- Date
- Float
- Enter a Default Value for the parameter. This is recommended to prevent long running queries. Default parameter values are also used to preview the report output when you design report layouts using BI Publisher Layout Editor.
- Select the Parameter Type. Supported types are:
- Text — Allows the user to enter a text entry to pass as the parameter. See Section 4.2.1, 'Defining a Text Parameter.'
- Menu — Presents a list of values to the user. See Section 4.2.2, 'Defining a Menu Parameter.'
- Date — Passes a date parameter. The Data Type must also be Date. See Section 4.2.3, 'Defining a Date Parameter.'
Note:BI Publisher supports parameters that are of type text entry or menu (list of values) but not both. That is, you cannot define a 'combination' parameter that enables a user to either enter a text value or choose from a menu list of values. - Row Placement - this setting configures the number of rows for displaying the parameters and in which row to place each parameter. For example, if your report has six parameters, you can assign each parameter to a separate row, 1 - 6, with one being the top row; or, you can assign two parameters each to rows 1, 2, 3. By default, all parameters are assigned to row 1.Row placement can also be configured at the report level. The report definition supports additional display options for parameters. For more information, see 'Configuring Parameter Settings for the Report' in Oracle Fusion Middleware Report Designer's Guide for Oracle Business Intelligence Publisher.
4.2.1 Defining a Text Parameter
The Text type parameter provides a text box to prompt the user to enter a text entry to pass as the parameter to the data source. Figure 4-2 shows a text parameter definition.
To define a Text type parameter:
- Select Text from the Parameter Type list. The lower pane displays the appropriate fields for the selection.
- Enter the Display Label. The display label is the label that displays to users when they view the report. For example: Department.
- Enter the Text Field Size as an integer. This field determines the number of characters that the user can enter into the text box. For example: 25.
- Enable the following Options if required:
- Text field contains comma-separated values — Select this option to enable the user to enter multiple comma-delimited values for this parameter. The parameter in your data source must be defined to support multiple values.
- Refresh other parameters on change — Performs a partial page refresh to refresh any other parameters whose values are dependent on the value of this one.
Figure 4-3 shows how the Department parameter displays to the report consumer.
Figure 4-3 Text Type Parameter as Displayed in the Report
4.2.2 Defining a Menu Parameter
A Menu type parameter presents a list of values to the user. You must define the list of values first. See Section 4.4, 'Adding Lists of Values.' The Menu type parameter supports the data types of String and Integer only.
The Menu parameter definition includes the options:
Figure 4-4 shows the menu parameter definition.
To define a Menu type parameter:
- Select Menu from the Parameter Type list. The lower pane displays the appropriate fields. Choose the Data Type (must be String or Integer).
- Enter the Display Label. The display label is the label that displays to users when they view the report. For example: Department.
- Enter the Number of Values to Display in List. If the number of values in the list exceeds the entry in this field, the user must click Search to find a value not displayed, as shown in Figure 4-5. This field defaults to 100.Figure 4-5 Search Feature Enabled When Number of Values Exceeds Setting
- Select the List of Values that you defined for this parameter.
- Enable the following Options if required:
- Multiple Selection — Allows the user to select multiple entries from the list. Your data source must be able to support multiple values for the parameter. The display of a menu parameter that supports multiple selection differs. See Figure 4-6 and Figure 4-7.
- Can select all — Inserts an 'All' option in the list. When the user selects 'All' from the list of values, you have the option of passing a null value for the parameter or all list values. Choose NULL Value Passed or All Values Passed.Note:Using * passes a null, so you must handle the null in your data source. A method to handle the null would be the standard Oracle NVL command, for example:where customer_id = nvl(:cstid, customer_id)where cstid is a value passed from the LOV and when the user selects All it passes a null value.
- Refresh other parameters on change — Performs a partial page refresh to refresh any other parameters whose values are dependent on the value of this one.
Figure 4-6 shows how the Department menu type parameter displays to the report consumer when multiple selection is not enabled.
Figure 4-6 Department Menu Type Parameter with Multiple Selection Disabled
Figure 4-7 shows how the Department menu type parameter displays to the report consumer when multiple selection is enabled.
Figure 4-7 Department Menu Type Parameter with Multiple Selection Enabled
4.2.2.1 Customizing the Display of Menu Parameters
The display of menu parameters in the report can be further customized in the report definition. Menu type parameters support the additional display option as a static list of checkboxes or radio buttons. For more information, see 'Configuring Parameter Settings for the Report' in the Oracle Fusion Middleware Report Designer's Guide for Oracle Business Intelligence Publisher.
4.2.3 Defining a Date Parameter
The Date type parameter provides a date picker to prompt the user to enter a date to pass as the parameter to the data source. Figure 4-8 shows the date parameter definition.
To define a Date type parameter:
- Select Date from the Parameter Type list. The lower pane displays the appropriate fields for your selection.
- Enter the Display Label. The display label is the label that displays to users when they view the report. For example: Hire Date.
- Enter the Text Field Size as an integer. This field determines the number of characters that the user can enter into the text box for the date entry. For example: 10.
- Enter the Date Format String. The format must be a Java date format (for example, MM-dd-yyyy).
- Optionally, enter a Date From and Date To. The dates entered here define the date range that are presented to the user by the date picker. For example if you enter the Date From as 01-01-1990, the date picker does not allow the user to select a date before 01-01-1990. Leave the Date To blank to enable all future dates.
Figure 4-9 shows how the Hire Date parameter displays to the report consumer.
Figure 4-9 Hire Date Parameter
4.3 About Lists of Values
A list of values is a defined set of values that a report consumer can select from to pass a parameter value to your data source. If you define a menu type parameter, the list of values that you define here provides the menu of choices. You must define the list of values before you define the menu parameter.
Populate the list using one of the following methods:
- Fixed Data — Manually enter the list of values.
- SQL Query — Retrieve the values from a database using a SQL query.
- Flexfield— Retrieve the values from a key flexfield defined in Oracle E-Business Suite. This option is only available when BI Publisher is using the Oracle E-Business Suite security model. For more information, see Section 4.5, 'Adding Flexfield Parameters.'
4.4Adding Lists of Values
To add a List of Values:
- On the Data Model components pane, click List of Values and then click Create new List of Values, as shown in Figure 4-10.
- Enter a Name for the list and select a Type: SQL Query or Fixed Data.
4.4.1 Creating a List from a SQL Query
The data engine expects a (display) name-value pair from the list of values query. In the list of values select statement, the column listed first is used as the display name (what is shown to the user) and the second is used for the value that is passed to the parameter in the data set query by the data engine.
If the query returns only one column, then the same column value is used both as the list of values display name shown to the user and as the value that is passed to the parameter.
To create a list from a SQL query:
- Select a Data Source from the list.
- In the lower pane, select Cache Result (recommended) if you want the results of the query cached for the report session.
- Enter the SQL query or use the Query Builder. See Section 2.3.3, 'Using the SQL Query Builder' for information on the Query Builder utility. Figure 4-11 shows a SQL query type list of values.
The SQL query shown in Figure 4-11 selects only the DEPARTMENT_NAME column from the DEPARTMENTS table. In this case the list of values both displays the results of the query in the list and passes the same value to the parameter in the data set. Figure 4-12 shows the list of values display entries and the values passed to the data set. Note that the menu items and the values shown for P_DEPT are the DEPARTMENT_NAME values.
Figure 4-12 Sample Data Showing the Same LOV Display Names and Values
If instead you wanted to pass the DEPARTMENT_ID to the parameter in the data set yet still display the DEPARTMENT_NAME in the list, construct your SQL query as follows:
Figure 4-13 shows the list of values display entries and the values passed to the data set. Note that the menu lists the DEPARTMENT_NAME while the values shown for P_DEPT are the DEPARTMENT_ID values.
4.4.2 Creating a List from a Fixed Data Set
To create a list from a fixed data set:
- In the lower pane, click the Create new List of Values icon to add a Label and Value pair. The label is displayed to the user in the list. The value is passed to the data engine.
- Repeat for each label-value pair required.
Figure 4-14 shows fixed data type list of values.
4.5 Adding Flexfield Parameters
Oracle E-Business Suite customers who have configured BI Publisher to use E-Business Suite security can create reports that leverage key flexfields as parameters. When you define a data model to pass a key flexfield as a parameter, BI Publisher presents a dialog to the report consumer to make selections for the flexfield segments to pass as parameters to the report, similar to the way flexfields are presented when running reports through the concurrent manager in the E-Business Suite.
The flexfield list of values displays in the report viewer as shown in Figure 4-15.
The flexfield list of values displays as a dialog from which you select the segment values as shown in Figure 4-16.
Figure 4-16 Flexfield Segment Selection Dialog
4.5.1 Prerequisites for Using Flexfields
When defining a list of values, E-Business Suite customers see a list Type called 'Flexfield'. To enable the flexfield type list of values, BI Publisher must be configured to use E-Business Suite Security. The flexfield must already be defined in the E-Business Suite.
For information about flexfields in the E-Business Suite, see Oracle E-Business Suite Flexfields Guide. For information about setting up E-Business Suite security for BI Publisher, see 'Integrating with Oracle E-Business Suite' in Oracle Fusion Middleware Administrator's Guide for Oracle Business Intelligence Publisher.
4.5.2 Adding a Flexfield Parameter and List of Values
To add a flexfield parameter complete the following tasks. Each task is described in detail in the subsequent sections:
- Add the flexfield list of values (LOV). The flexfield type list of values retrieves the flexfield metadata definition to present the appropriate values for each segment in the flexfield list of values selection dialog.
- Add a parameter and associate it with the flexfield LOV by selecting your flexfield list of values as the source menu for the parameter.
- Add the Flexfield component to the data model. Use the flexfield parameter to pass values to the Flexfield defined in the Data Model.
- Reference the Flexfield in your SQL query using the
&flexfield_name
syntax. At runtime the&flexfield_name
reference is replaced with the lexical code constructed based on the values in the Flexfield component definition.
4.5.2.1 Adding the Flexfield List of Values
To add a list of values retrieved from a flexfield definition:
- On the Data Model components pane, click List of Values and then click Create new List of Values.
- Enter a Name for the list and choose Flexfields as the Type. When you choose Flexfields as the Type, the Data Source option is no longer editable. All flexfields type lists of values use the Oracle E-Business Suite as the data source.
- In the lower pane, enter the following:
- Application Short Name - the E-Business Suite application short name, for example: SQLGL.
- ID Flex Code - the flexfield code defined for this flexfield in the Register Key Flexfield form, for example: GL#.
- ID Flex Number - the name of the source column or parameter that contains the flexfield structure information, for example: 101 or :STRUCT_NUM. If you use a parameter, ensure that you define the parameter in the data model.
Figure 4-17 shows a sample flexfield type LOV.
4.5.2.2 Adding the Menu Parameter for the Flexfield List of Values
Define the parameter to display the flexfield list of values and capture the values selected by the user. The Flexfield type parameter definition includes an additional field called Range to support range flexfields. A range flexfield supports low and high values for each key segment rather than just single values.
To define the parameter for the flexfield list of value:
- On the Data Model components pane, click Parameters and then click Create new Parameter.
- Select Menu from the Parameter Type list. Choose the Data Type (must be String or Integer).
- Enter a Default Value for the flexfield parameter. The default value can also be customized in the report definition.
- Enter the Row Placement. The row placement determines where this parameter appears in the report viewer. Row placement can also be customized in the report definition
- Enter the Display Label. The display label is the label that displays to users when they view the report. For example: Account From. The display label can also be customized in the report definition.
- Select the List of Values that you defined for this parameter. When you select a list of values that is the Flexfield type, an additional field labeled Range displays. To pass a range of flexfield segment values see Section 4.5.2.5, 'Passing a Range of Values.'
- The following options are disabled for flexfield parameters: Number of Values to Display in List, Multiple Selection, Can select all, and Refresh other parameters on change.
Figure 4-18 shows a parameter definition for the flexfield list of values.
Figure 4-18 Defining the Parameter for the Flexfield List of Values
4.5.2.3 Using the Flexfield Parameter to Pass Values to a Flexfield Defined in the Data Model
Now you can pass the parameter values to a flexfield component in the data model. Chapter 6, 'Adding Flexfields' covers adding a flexfield component in detail. The simplified procedure is provided here to complete the example.
To define the Flexfield in the data model:
- On the Data Model components pane, click Flexfields and then click Create new Flexfield.
- Enter the following:
- Name — Enter a name for the flexfield component.
- Type — Select the flexfield type from the list. The type you select here determines the additional fields required. See Section 6.2.1, 'Entering Flexfield Details.'
- Application Short Name — Enter the short name of the Oracle Application that owns this flexfield (for example, GL).
- ID Flex Code — Enter the flexfield code defined for this flexfield in the Register Key Flexfield form (for example, GL#).
- ID Flex Number — Enter the name of the source column or parameter that contains the flexfield structure information. For example: 101. To use a parameter, prefix the parameter name with a colon, for example, :PARAM_STRUCT_NUM.
- In the lower region of the page, enter the details for the type of flexfield you selected. For the field that is to take the parameter value, enter the parameter name prefixed with a colon, for example, :P_Acct_List.In Figure 4-19 the Flexfield component is defined as a 'Where' Type. The parameter :P_Acct_List is entered in the Operand1 field. At runtime, values selected by the user for the parameter P_Acct_List will be used to create the where clause.Figure 4-19 Example Flexfield Component Using Flexfield Parameters
4.5.2.4 Referencing the Flexfield in the SQL Query
Finally, create the SQL query against the E-Business Suite database. Use the lexical syntax in the query as described in Section 6.2, 'Adding Key Flexfields.' In Figure 4-20
&Acct_Flex
is the Flexfield lexical called in the where condition of the SQL query.4.5.2.5 Passing a Range of Values
To define the parameters for the flexfield lists of values when you want to pass a range of values you create two menu parameters that both reference the same flexfield LOV. At runtime users choose a high value from the list of values and a low value from the same list of values. These two values are then passed as operands to the flexfield component of the data model.
- Create one flexfield LOV as described in Section 4.5.2.1, 'Adding the Flexfield List of Values.'
- Create the high range parameter following the steps in Section 4.5.2.2, 'Adding the Menu Parameter for the Flexfield List of Values.' For the Range field, select High to designate this parameter as the high value.
- Create the low range parameter following the steps in Section 4.5.2.2, 'Adding the Menu Parameter for the Flexfield List of Values.' For the Range field, select Low to designate this parameter as the low value. Both parameters reference the flexfield list of values that you created in Step 1. Figure 4-21 shows creating the parameters to define the range.
- Create the Flexfield in the data model, as described in Section 4.5.2.3, 'Using the Flexfield Parameter to Pass Values to a Flexfield Defined in the Data Model.'In the lower region of the page, enter the details for the type of flexfield you selected. Enter the parameter prefixed with a colon for example, :P_Acct_List.In Figure 4-19 the Flexfield component is defined as a 'Where' Type. The parameters :P_FLEX_LOW and :P_FLEX_HIGH are entered in the Operand1 and Operand2 fields. At runtime, values selected by the user for the parameters P_FLEX_LOW and P_FLEX_HIGH will be used to create the where clause.Figure 4-22 Example Flexfield Component Using Flexfield Parameters
When the report associated with this data model is displayed in the report viewer, the report consumer sees the two flexfield parameters as shown in Figure 4-23.
Figure 4-23 Flexfield Range Parameters Displayed in the Report Viewer
When the report consumer clicks either the high or low flexfield indicator (...), a dialog launches enabling input of both the high and low values as shown in Figure 4-24.
The display characteristics in the report viewer of the range flexfield parameter resemble closely the presentation of range flexfields in the E-Business Suite.