Can I do dynamic parameters from Select?

[czentman]

I want to have a parameter choice of ledgers based on the order number entered as a parameter - basicly a dynamic parameter, is this possible?

[Mark McKenna]

The Select reporting system does support dynamic and cascading style parameters.

1. Dynamic parameters are implemented as dropdown listbox controls and "move it" controls (those side-by-side listboxes that allow you to move selections back and forth). The controls get their selection data at runtime by executing a particular SQL statement and populating the prompt control accordingly.
2. Cascading parameters are implemented using dynamic parameters that refer to other parameters* in their associated SQL statements.

* Note that the current cascading parameter implementation is limited in that the notifying control must also be of type dropdown listbox or "move it". We have a work item under consideration to expand this to include additional control types in those that provide change notifications to others.

Since it sounds like you want the user to enter the order number manually into a text field, and then update another listbox control's choices based on that entry, the current implementation won't do exactly what you want because the textbox type is not configured to instruct other controls to perform their requeries.

Nonetheless, this is how dynamic parameters are set up:

Provide an additional parameter field in your report specifically named PARAMETER_VALUES that is a static list of string values. In here is where the dynamic parameters are identified. We parse the string values you provide in pairs, such that the first line in a given pair is the name of the dynamic parameter to which the selection data should assign, and the second is the SQL statement that will execute to provide that selection data. As for cascading, the SQL statement itself can refer to other parameters by enclosing them in a single set of curly braces. When you do this, the effect from the prompt dialog is that a change to the dependency control will trigger a requery on the dependent control.

For example, say you have two parameter fields named TrustAccount and BankName. To make each one's selection data dynamic, such that the user will be presented with separate lists of Trust Accounts and Bank Names to choose from, add the following four values to the PARAMETER_VALUES parameter field (remember the pairing effect):

Code: Select all

TrustAccount
SELECT Code from TrustAccount ORDER BY Code
BankName
SELECT DISTINCT BankName from TrustAccount ORDER BY BankName

To provide a cascading relationship, such that the Trust Account selection data will be dependent upon the current selection of BankName, simply change the SQL Statement for the TrustAccount parameter to read as follows:

Code: Select all

SELECT Code from TrustAccount WHERE BankName = '{BankName}' ORDER BY Code

Then, when the user selects a different Bank, the selections in the Trust Account listbox will update accordingly.

[czentman]

I changed the textbox into a dropdown and did you said. It does not work. They work fine independantly but when I add the where clause for the cascading, that parameter does not get populated at all with any data. Do you have a report in the system that has cascading parameters that I can look at or can I send you the report to try on your end?

[czentman]

I found a report to copy the idea and figured it out. You didn't mention that you have to a have a formula field with the same field name and that it's based on the formula field name, not just on the other parameter. Now it works. Thanks.

[Mark McKenna]

You should not have to do anything with formula fields to get dynamic/cascading parameter fields to work. Would you mind attaching the report you created so we could take a look?