This article describes how to allow the user to specify an input file of values instead of entering a comma-separated list of values for an input parameter with an "IN" or "NOT IN" Operator. Begin by setting the Operator to IN or NOT IN as in the example below. Then click the Info icon:
Then make the appropriate selections in the Allow FileName for Input Values section of the dialog:
- Allow File Input? Check this if you want to allow file input for values.
- With a Header Row? Many .CSV files are generated with a header row that provides titles for the columns. If you will be using such a header row, check this box. It will skip the first line in the file.
- Suggested File Extents: The most common types of input files are .CSV and .TXT. That is the default for this field. However, you can change the list to match your needs.
When you save these selections and save the report definition, the input field will look like this:
As the ToolTip suggests, you can enter a list of comma-separated values here, or type in a filename, or use the File Search Button (... ) to select a file. As you type in a filename, the Status Message and ToolTip change to let you know about the filename you are entering:
When a complete filename is entered and the file exists, the ToolTip and Status Message tell you about the file:
- Number of lines in the file
- Whether or not a header is expected
- The first few characters of the first line in the file
- The first few characters of the second line in the file
When an existing filename is entered, there is a View button enabled, and, if there is an application registered for the type of file entered, an Edit button is enabled (both in right-most red box above). You may press the View button to see the contents of the file or press the Edit button to edit it.
When an existing filename is selected, the SQL statement will be generated by selecting the first column of the lines in the file: