Spotfire® User Guide

Prompts in data connections – Limiting data fetched based on user input

Prompts are a way to filter and limit data from a data connection before you fetch data from the external database. You can add prompts to columns and parameters in a view so that, when you open the analysis with the data connection, you are shown prompts where you can select the values to limit the data by.

About this task

With prompts, you can use a single data connection for multiple purposes, and give the end user the option to select the data of interest. Below are two examples of how prompt dialogs look when you open an analysis with a prompted data connection.



On this page

Adding prompts to a data connection

You can add prompts to views in a data connection when you select data in the Select data flyout or Views in Connection dialog.

Before you begin

  • Depending on the type of data source you are creating a connection to, different prompting options might be available. See the help page for the data connector for your data source for details.

Procedure

  1. Create or edit a data connection and, in the Select data flyout (or Views in Connection dialog), select the data table (view) of interest.

    • If you are in the Select data flyout:
      1. In the table preview on the right, click the Prompts tab.

        The Prompts tab shows an overview of the prompts defined for columns and parameters in the data table.

      2. To add a new prompt for a column or parameter, click Add prompt.
      3. In the Name column, select the column or parameter you want to configure a prompt for, and then configure the prompt options.
    • If you are in the Views in Connection dialog:
      1. Above the Columns in selected view panel on the right, click Define prompting.

        The Define Prompting dialog opens, which lists the columns or parameters that are currently prompted for in the data connection.

      2. To define a new prompt, click New, or click Edit if you have existing prompts to edit.
      3. In the New/Edit prompt dialog, select the column or parameter for which you want to add a prompt and then configure the prompt options.
  3. Configure the different options for your prompt. First select the prompt type:
    OptionDescription
    Manual input Manual input lets you enter any value to include (for string columns or numeric columns). This prompt type should only be used if all end users of the data connection know which values are valid for the column or parameter, or if applicable values are shown in the description. For boolean values you get to pick a value using radio buttons (True/False) and for date columns you can choose a date from a calendar.
    RangeRange lets you specify a range of values. This prompt type is suitable for numerical or Date/DateTime/Time columns when a sustained range of numbers or time periods are to be retrieved. Dates can be selected from a calendar.
    Multiple selection Multiple selection presents a list of available values from which you can select multiple values.
    Note: For some connectors, such as Microsoft SharePoint Online, you must type values manually in Multiple selection prompts.
    Single selection Single selection presents a list of available values from which you can select a single value only.
  4. Make a selection for the following prompt settings:
    Tip: If you are in the Select data flyout, to access some options, click the cogwheel icon ().
    OptionDescription
    Min and max selections For the prompt type Multiple selection, you can choose Min selections and Max selections, to define the minimum and maximum number of values you can select in the prompt.
    Load values automatically[Only applicable for Multiple selection and Single selection prompt types.]

    When selected, a list of all available values is loaded for the end user to select from in the prompt.

    If you do not load values automatically, the user can either type values in the prompt manually, load values from a file, or click a link to load the list of available values to select from.
    Tip: For multiple selection prompts, typing values can be preferable, especially when the user needs to select a large number of values. The user can then use a saved list of values, by loading a file with text values or pasting it in the input field.

    If you select Load values automatically, the user cannot type in values or load values from a file in multiple selection and single selection prompts.

    Prompt groupPrompt groups determine the dependency between prompts when you open the data connection.

    When you select values for two prompts in the same prompt group, your selection in the first prompt filters the list of available values in the second prompt. That is, in the second prompt, you cannot see or select any values that are filtered out by your selection in the first prompt.

    When you select values for prompts in different prompt groups, your selection in one prompt does not filter the values in the other.

    Default value: 1

    Tip: If a prompt is taking a long time to load, you can try assigning it its own prompt group. It is often faster to load all available values for a column from the database, rather than loading a list filtered by your selection in a previous prompt.
    Note: For prompts based on mandatory parameters, you cannot select a prompt group. Mandatory parameters always come before other prompts and the values you select in mandatory parameter prompts limit the available values in all other subsequent prompts, regardless of the prompt group.
    RequiredSelect if the prompt should be required or optional.
    • For required prompts, you must enter a value in the prompt dialog when you open the data connection.
    • For optional prompts, you can skip the prompt without entering a value to filter by when you open the data connection.
    DescriptionThe prompt description is shown when you open the data connection and you are prompted for input in the Open connection dialog.

    A good description can help end users understand what they should enter or select when prompted for input.

    Note: Multiple prompts and prompt order

    You can add multiple prompts to a view, at most one per column or parameter.

    When you have more than one prompt, the user is shown the prompts in the order of the list in the Prompts tab or Define Prompting dialog. To change the order, use the Move up and Move down buttons. Depending on what is selected in a prompt, the available values in the view will be reduced before the next column or parameter in sequence is processed, so the order of the prompts matters.

    Prompts based on mandatory parameters always come before other prompts.

  5. When you have finished defining your prompts, in the Select data or Views in Connection dialog, click Continue.

Results

When you open an analysis with a data table from the data connection, or add the data connection to an analysis, you get to select values to filter the data by in each prompt defined for the data table view.

Using prompts as static filters (switching user input on/off)

You can use prompts as static filters, filtering the data by fixed values for anyone who opens the analysis. To do this, you turn off the ability for users to enter prompt values for the data table. Then the values you enter in the prompts when you add the data connection to the analysis are stored.

About this task

Procedure

  1. When you have added a data table from a data connection with prompts to your analysis, select Data > Data table properties.
  2. In the Data Table Properties dialog, select your data table and, on the General tab, clear the check box Prompt for new settings before loading.
  3. To save your changes, click OK.

Results

When you open the analysis or reload data, you do not get to enter new values in any prompts for the data table.

Prompts with imported or external data

Prompts are applied both when you load data tables as imported and external. To learn more, see Load methods.

About this task

Imported data (in-memory)
When you import a data table from a data connection, the data is loaded when you open the analysis and when you reload data. Your selection in prompts is applied as a filter to the query from Spotfire to the external data, so that you only import the relevant data into the Spotfire data engine.
External data (in-database)
When you keep a data table external, Spotfire queries the external database for every action in the analysis. Your selection in prompts is applied as a filter in each query. Because queries are sent frequently and the query length is affected by prompts, prompts might have an impact on performance for external data.

Limitations

There are some contexts where you cannot use prompts. Prompts require user interaction, which makes them unsuitable for automated scenarios. The following are limitations to keep in mind for prompts in data connections:

About this task

  • The type of prompts you can use depends on the connector you are using:
    • With some connectors, you cannot select all prompt types for columns.
    • With some connectors, you cannot select all prompt types for parameters.
    • With some connectors, you cannot load a list of values in the prompt, which means you must enter or paste values manually.
  • You cannot use prompting in analyses you want to open with Automation Services.
  • You cannot use prompting in analyses configured with scheduled updates.
  • With the exception of connections to SAP BW BEx queries, prompting is not supported for data connections to cube data sources. See Adding prompts for BEx queries in a SAP BW connection, for more information on defining prompting for SAP BW BEx queries.
  • You cannot specify prompt values via configuration blocks.
Copyright © Cloud Software Group, Inc. All rights reserved.