If you are creating a list of choices for a parameter drop-down list, you can acquire the choices from several sources:
- Manually enter the choices in the list (static and least exciting of all of the options)
- Append a unique list of choices derived from a field in the database (static)
- Point to a database field for available existing choices (dynamic)
- Import items from a text file (static)
Each of the options has their pros and cons. If you are trying to generate a list that is unlikely to change (like a list of states in the USA) and you need to ensure all options are made available from Day 1, the first option of manually entering all of the choices is the typical approach.
But what if all of those choices exist in another report or file? In this case, the ability to import list selections has great appeal.
Consider the following text file:
Each of the states and territories has been listed by both the abbreviation and the description. This list would make a perfect source for populating a parameter drop-down list.
Step 1 – Ensure the file is in the correct format
The source file must be in the form of a text file. The text file will be made up of a column of information. This will populate the “Value” field in the parameter list. If you wish to also populate the “Description” field in the parameter list, there needs to be a second column of data present in the text file. The text file can have no more than two columns of information.
Be Aware!!! The values in the text file must be separated from the description by a TAB character. This serves as the delimiter between “Value” and “Description”.
Step 2 – Populating the parameter
While in the “Create New Parameter” dialog box, select the “Actions” button and choose “Import…”
Browse out to the location of the text file and click Open. The list of entries in the text file will now be parsed by row and by column to populate the “Value” and “Description” fields.
The states and territories may now be used as a selection list in a parameter.