Parametrize column selection of information link to reduce DB load and RAM usage

[MaxB]

Hello everyone,

I’m looking to provide standardized information links to our users in Spotfire. My goal is to keep database load and RAM usage under control by loading only the columns required for specific use cases. To achieve this, I need a way to parameterize the information link, allowing flexibility in column selection.

I have an idea of how to implement this, but I cant get it working... Here’s what I have in mind:

Parameterized Information Links

 Steps:

      1.   Create a Parameter: In the Information Designer, create a parameter to represent the list of columns the user wants to query. For example, you can name this parameter SelectedColumns.

      2.   Use the Parameter in SQL: Modify your SQL query to dynamically include or exclude columns based on the value of the SelectedColumns parameter. A basic SQL template might look like this:

SELECT ?SelectedColumns

FROM MyTable 

WHERE <contitions>

           •  If the user selects specific columns, those will be substituted in place of ?SelectedColumns, allowing only the chosen columns to be queried.

           •  SelectedColumns has a default value in case the user doesn’t specify any columns.

 Using Spotfire Modifications: Dynamic Properties

To make the selection of columns more user-friendly, I would use Property Controls to allow users to choose from a predefined list of columns.

 Steps:

      1.   Create a Document Property: Set up a multi-select list property in Spotfire (let’s call it ColumnSelection) that contains the list of available columns the user can choose from.

      2.   Bind the Document Property to the Information Link: Link this ColumnSelection document property to the SelectedColumns parameter you defined in the Information Designer.

 

Any insights or suggestions on how to get this approach working would be greatly appreciated!

Thanks in advance for your help!

Edited 12 hours ago by MaxB
Correction of syntax