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 the Add button.

=Sheet1!A$1:D$11

Enter name as Emp2_All and the range as given below in the "Refers to:" text box and click the Add button.

=Sheet2!A$1:D$6

Enter name as Emp_All and the range as given below in the "Refers to:" text box and click the Add button.

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

Click the Ok button in the Define Name dialog window and save the Empdata.xls file.

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 on the Next button

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

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 the Finish button the data from the first worksheet is displayed in the window.

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.

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 the Finish button in the DataSource Wizard.

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 the some text (*) which correspond to the default value Emp2_All. On clicking the Finish button the data from the second worksheet is displayed in the window.

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.

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

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

Click the Finish button.

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 a combo box contains three values. On selecting a range value from the list and clicking the Finish button the corresponding output is displayed.

If instead of the range given above the parameter is entered as given below is specified.

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

Then 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 to different values to see the different output results.