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.
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
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
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.
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.
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.
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.
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 Finish 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 some text (*) which corresponds to the
default value Emp2_All
. On clicking Finish, the data from the
second worksheet is displayed in the window.
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.
The parameter is entered as follows, in the Range text box.
${Range#choice(Emp1_All,Emp2_All,Emp_All)}
Click Finish.
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.
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.