Dynamic Parameters

Introduction

We need a way to control the dynamic behaviour of the program by setting the values of various parameters. Dynamic parameters are specified while setting the properties of a program at design time and the value is assigned during the execution of the program.

A parameters is “declared” when you type ${something} within a text field. a field may include multplie declarations, mixed with normal text, e.g., "My name is ${first} ${last}". A parameter is “defined” when you supply a value.

Dynamic Parameter Elements

Each dynamic parameter can have up to three elements - name, type and default value. These parts are separated by # characters, which can be omitted when there are no subsequent elements. Here are some examples:

Parameter Element Description
${name} This syntax is used to specify a parameter name.
${name#type} This syntax is used to specify the name and type of the dynamic parameter.
${name#type#value} This syntax is used to specify the name, type and value.
${name##type} If the type has to be excluded and just the default value has to be specified, the syntax is given as such.

Dynamic Parameter Names

Dynamic parameters names can include any characters, including spaces and be as descriptive as you like. For example, ${Name} or ${Please enter your name}. Reserved characters, such as #, { and }, should be escaped by prefixing with a backslash \. For example, ${Enter your \#Id}.

Dynamic Parameter Types

Below are the list of dynamic parameter types:

Parameter Description
## A plain text field (default).
#password# A password field and hence the typed characters are masked.
#choice(A,B,C)# A combo box with choices A, B and C.
#integer# An integer field, which accepts non-negative integer values.
#number# A field for numeric values, which may be positive or negative and may include decimals.
#date()# A date field with a popup calendar (refer to Data Type for more details).
#lookup(...) Lookup choices from a datasource (refer to Lookup for more details).

Date Type

The date type provides a number of variants to control how the date is presented to the user. Within the tool, the string representation of dates always follows the international standard format yyyy-MM-dd. The table below are some examples of date syntax and their meaning.

Date Type Description
date System locale, long format.
date() System locale, long format.
date(fr) French locale, long format.
date(fr_CA) French-Canadian locale, long format.
date(,"long") System locale, long format.
date(,"medium") System locale, medium format.
date(,"short") System locale, short format.
date(,"(yyyy) dd/MM") System locale, custom format.
date(fr,"short") French locale, short format.

As you can see, date can take up to two parameters (locate, format). Both parameters are optional. The locale is defined using the appropriate language, country and variant codes, separated by underscores: LA_CO_VA. Again, you can omit values from the end to accept the default, LA_CO, or just LA. The language codes are defined at: http://www.loc.gov/standards/iso639-2/englangn.html, while the country codes are defined at: http://www.iso.org/iso/country_codes/iso-3166-1_decoding_table.htm. The format should either be “long”, “medium” or “short” or else a custom pattern. Remember the format only affects how the date is presented to the user, not the interneal string representation of the date.

Dynamic parameters are always strings. This means the result substituted for a date type will not be a date oject but only a string representation (in ISO format, as mentioned earlier). If usch a string is substituted in JavaScript, you will be performing subtraction instead: 2005-01-01 = 2003!. To convert the string to a date, you need the following form of JavaScript (showing a French date, here):

asDate("${Enter French data#date(fr,"long")}");

There are two important parts to this example. First, notice that the ${} substitution is enclosed within quotes. This ensures the result is substituted as a string, not as a series of number subtractions. Second, the string is passed to the asDate() function, which is pre-defined within the tool for converting strings in ISO date format into actual Date objects.

Lookup

It is good to guve users a choice of valid values, but the options are often dynamic. Using the lookup type, you can fetch values from a datasource to populate a combo box on the parameter UI. The following is an example:

${Compnay Name#lookup(Fruit,CompanyName) #B}

This will create a parameter on the UI with label “Company Name” with a combo box that reads its values from the datasource called “Fruit” using the field called “CompanyName”. The default selection will be company B.

Further, the datasource “Fruit” can be either:

  • Name of a Graphic Object within a Composite Diagram (Data Designer)
  • Name of a Data Source within a Report (Report Designer)
  • Name of a View using view: syntax (Dashboard Designer)
  • Name of a datasource in the Repository (AI)

Also, further parameters can be passed in if the datasource requires parameters:

${Company Name#lookup(Fruit,CompanyName,User=Jon,Password=XXX) #B}

Ordering Dynamic Parameters

By default, parameters are presented in alphabetical order. This means, if you have parameters defined for ${Name} and ${Address}, then the “Address” will appear first. Often we want to control the order of presentation - it is common to request “Name” before “Address”. To achieve this, we declare a dependency between the parameters. If the name of the parameter includes a string embedded in angle brackets <string>, then this indicates that the parameter is dependent on that named parameter and should be shown after it. To ensure “Address” appears after “Name”, we can therefore define ${Name} ${Address<Name>} - this declares that “Address” depends on “Name” and should therefore be shown after it. Note that the text in angle brackets is not shown on the UI. If we need to give type and/or default values, we include the # separator as usual: For example, ${Address<Name>#choice(Singapore,UK) #UK}.

Dynamic Parameters with Nested Datasoruce

Assume a dynamic parameter is specified in the SQL query while adding the JDBC data source to fetch the records of a specific city from the “Stores” table. Here is how a composite data source is added to the repository which references the “Stores” data source and values are passed to the dynamic parameters.

  1. Make sure the Mondrian Database is available (e.g., through ODBC).

  2. Add a JDBC data source called “Stores” and choose the appropriate driver and URL for your database. If you are using Mondrian through JDBC/ODBC, then enter the URL as “jdbc:odbc:MondrianFoodMart”.

  3. Click “Next”. Enter the following SQL query in the SQL tab window.

    SELECT DISTINCT

    store.store_id, store.store_country AS Country,

    store.store_state AS State, store.store_city AS City,

    store.meat_sqft, store.grocery_sqft, store.frozen_sqft,

    store.store_sqft FROM store WHERE store_city like '${Enter City}';

  4. Click “Next” and choose “Infer Schema”. On the Dynamic Parameter dialog box, enter any city name and click “Finish”. The schema will be displayed. Click “Finish” to add the data source to the repository.

  5. Now add a Composite DataSource called “Dynamic-Comp”. On clicking “Finish”, the Composite DataSource is added to the repository and opened.

  6. Drag and drop the “Stores.ds” file onto the Composite diagram and connect it directly to the “Result”.

  7. Open the “Stores Properties” from the shape on the diagram and click “Next” to see the DataSource Properties Screen. You will see the “Enter City” parameter. Enter the value as “Salem” (without the quotes) and click “Finish”.

  8. Select the “Result” and choose “View” from the popup menu. This is similar to that of passing dynamic parameters where “Enter City=Salem”.

  9. Go back to the Stores DataSoruce Properties screen and change “Salem” to ${City1}.

  10. Select the “Result” and choose “View” from the popup menu. This time, the “Dynamic Parameters” dialog box appears.

  11. Enter “Salem” in the “City1” text field and click “Finish”. The “Result” output is the same as when the value “Salem” was hardcoded.

  12. Repeat the process, replacing ${City1} with ${City1}${City2}.

  13. When you view the “Result”, the “Dynamic Parameters” dialog box appears again, this time with two fields. Enter “Sal” in the “City1” text box and “em” in the “City2” text box. The text values from “City1” and “City2” are concatenated by the ${City1}${City2} substitution, so the records corresponding to “Salem” are fetched again.

Example Declaration of Dynamic Parameters

The “Empdata.xls” file consists of employee details in two worksheets which contains ranges. Using dynamic parameters, we can choose during loading which range of cells to access.

  1. Before adding the Excel DataSource, we need to ensure the ranges are defined in the Excel file.

    Open the “Empdata.xls” file and select Name -> Define under the Insert menu. The “Define Name” dialog box pops up. Enter name as Empl_Add and enter the range as given below in the “Refers to:” text box and click “Add”.

    =Sheet1!A$1:D$11

  2. Enter name as Emp2_All and the range as given below in the “Refers to:” text box and click “Add”.

    =Sheet2!A$1:D$6

  3. Enter name as Emp_All and the range as given below in the “Refers to:” text box and click “Add”.

    =Sheet1:Sheet2!A$1:D$11

    Click “OK” in the “Define Name” dialog box and save the “Empdata.xls” file.

  4. Launch Elixir Ambience and add a new Excel DataSource called “Emp-Range” and enter the location of the “Empdata.xls” file as the URL.

    Select the First Row Header check box and enter the “Range” as given below in the text box.

    ${Range##Emp1_Add}

    Click “Next”.

  5. In the screen that appears, click “Infer Schema”. When the “Dynamic Parameters” dialog box pops up, enter any valid range name, from those specified above and click “Finish”. The schema will be inferred. Click “Finish” to add “Emp-Range.ds” to the repository.

  6. After saving, the DataSource opens. On clicking the “Load Data” menu in the data window, the “Dynamic Parameters” window appears. You will notice that the text box contains the default value Emp1_All. On clicking “Finish”, the data from the first worksheet is displayed in the window.

  7. If instead of ${Range##Emp1_All}, the parameter ${Range##} or ${Range} is entered (i.e., the default value is not specified), then on clicking the “Load Data” menu, the “Dynamic Parameter” dialog box with a blank text field appears.

  8. Instead of the range specified in step 7, the parameter is entered as given below in the “Range” text box.

    ${Range#password#Emp2_Add}

    Click “Finish” in the DataSource Wizard.

  9. Select and double click on “Emp-Range.ds”. The Data window opens. On clicking the “Load Data” menu in the data window, you will see that the password field contains some text (*) which corresponds to the default value Emp2_All. On clicking “Finish”, the data from the second worksheet is displayed in the window.

  10. If instead of ${Range#password#Emp2_All}, the parameter ${Range#password#} is entered (i.e., the default value is not specified), then on clicking the “Load Data” menu, the “Dynamic Parameter” dialog box with a blank password field appears.

  11. The parameter is entered as follows, in the “Range” text box.

    ${Range#choice(Emp1_All,Emp2_All,Emp_All)}

    Click “Finish”.

  12. Select and double click “Emp-Range.ds”. The Data window opens. On clicking the “Load Data” menu in the data window, you will see a combo box containing three values. On selecting a range value from the list and clicking “Finish”, the corresponding output is displayed.

  13. If instead if the range given above, the parameter is entered as follows:

    ${Range#choice(Emp1_All,Emp2_All,Emp_All) #Emp_All}

    On clicking the “Load Data” menu, the “Dynamic Parameter” dialog box appears with a default value of Emp_All in the combo box. You can try changing values to see the different output results.