Limit data in Spotfire® using on demand, a multi value text area control and a document property

Introduction

The use of document properties can be a more powerful way of limiting data than using filters. It is possible to define a document property, containing multiple values, which is useful when you need to limit data using static values without user interaction.

In this example, we will use a SAP HANA data connection. This should, however, work with any data source that supports on-demand.

Description of steps

Connect to the data source, and select the table and columns from which you want to analyze data. This is the table we will fetch data from on demand; our on-demand table.

Make sure to select the "Load on demand" check box. We will define the parameters later so don't go into the Settings dialog at this stage; just click OK.

We also need a table and column from which we will drive the on-demand table. This column will populate the text area control and the values selected by the user will be set in our document property. This is very often another table, maybe from another data source. To keep things simple we will use the same source as we used for our on-demand table in this example. This means it will be very easy to find matching columns for this article.

Connect to the same data source using the same data connection and create a second view.

Just click OK in the Views in Connection dialog.

Rename the table to, for example, "Selection Table". Do not select the "Load on demand" check box for this data table.

We now have the two data tables we need. Press Ctrl-T on the keyboard to create a new text area in Spotfire. Then, right-click the text area and select "Edit Text Area". Click on the "Insert property control" button and add a new "List box (multiple select)" control.

Configure the multiple select List box property control as described below.

Click New... to create a new document property. Give it a name; in this example we called it "property", and click OK.

Make sure you have selected "Set property value through" "Unique values in column".

Under "Data table", select your newly created Selection Table.

We choose to limit data by state using the STATE column, as shown below.

Click OK.

Save and close the text area. You should now have a list box with all unique values from the STATE column listed.

Select a few values in the list box and verify that the document property's value reflects your selection by going to Edit > Document Properties, and looking at the Properties tab.

It's now time to let the document property drive what data is loaded into your on-demand data table. Go to Edit > Data Table Properties. Select your on-demand data table in the list and click on Settings...

Click the "Data on demand" radio button an select the STATE column in the list.

If you want the on-demand data to load (refresh) automatically when a new selection is made in the list box, select the "Load automatically" check box.

Click "Define Input..." and configure the input as described below.

"Input for the selected parameter" should be set to "Values (fixed/properties/expression)", because we want to control the input using a property containing a list of values.

Select the property you defined earlier by clicking "Property" and then "Select...". Choose the property you created, called "property".

Click OK.

We are now done with the data preparation and it's time to create a visualization. In the example below, we visualize data from our on-demand table in a bar chart and limit its data using the list box.

If you only want to limit data using on-demand but have no further need for a list box, you can just delete the text area at this point. The document property will then keep the values that were selected in the list box when it was deleted together with the text area. Entering multiple values into a document property can only be done by creating the property from the multi value list box.

Limiting data using SAP HANA Information View parameters

Spotfire 7.5 introduced native support for SAP HANA Information View parameters. These are automatically connected to Spotfire prompts.

Connect to Information Views using the SAP HANA connector. Please note the radio button indicating that we want to connect to Information Views.

Browse your views and select one containing parameters. You will notice that Spotfire recognizes the parameters and lists them in the bottom right corner of the dialogue.

Define prompts for all variables you wish to define values for and variables you which to prompt business users for.

Spotfire automatically creates prompts for mandatory variables as input values are needed to be able to use Information Views with mandatory variables.

Once done you will be prompted to add values for the defined prompts.

Users are by default not prompted to enter new values. This can be changed in the menu Edit > Data Tables Properties dialogue. Tick the option to "Prompt for new settings before loading" if you wish users to be prompted when opening an analysis file or reloading data.