Example Declaration of Dynamic Parameters.

The Empdata.xls file consists of employee details in two worksheets which contain 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 Emp1_All 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 window and save the Empdata.xls file.

  4. Launch Elixir Repertoire 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_All}

    Click Next.

  5. In the screen that appears click Infer Schema. When the Dynamic Parameters dialog 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 with a blank text field appears.

  8. Instead of the range specified in step 16 the parameter is entered as given below in the Range text box.

    ${Range#password#Emp2_All}

    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, that is, 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 of 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 appears with a default value of Emp_All in the combo box. You can try changing values to see the different output results.