Spotfire Ideas Portal
Hello everyone,
to provide standardized information links to Spotfire users it would be very useful to have a option to parameterize the column selection of a information link. Goal is to keep database load and RAM usage under control by loading only the columns required for specific use cases. To achieve this, we need a way to parameterize the information link, allowing flexibility in column selection.
I have an idea of how to implement this, but currently this is not working due to 2 reasons:
- the information links have their predefined list of columns
- the values of a document property send to the SQL statement are in string format
Here is a possible solution 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.
Dynamic Properties in UI
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.
Would be awesome to have this possibility!
Best regards,
Max
To complete the list of things that would be good for an overhauled query engine w/ big data (in-DB) focus:
Re-ordering of the prompt sequence can help to make queries (especially the SELECT DISTINTC ones to populate the multiple value prompts) more efficient (see https://ideas.spotfire.com/ideas/TS-I-9415)
Repeating as a public comment:
I think this is a great idea, but a solution should work w/o creating the need for custom SQL since as soon as you use custom SQL maintenance of these Information Links becomes error prone (changes via the GUI are ignored). So I would love to see a solution where this can be configured via the GUI w/o modifying the SQL manually.
Also I would encourage Spotfire to view this idea not in isolation but in the light of big data use cases where data is kept external. This is currently only possible with data connections which have a few short comings:
Lack of support of temporary tables e.g. for x-DB joins and to avoid too large IN statements (see https://ideas.spotfire.com/ideas/TS-I-7749)
Lack of multiple value prompts (see https://ideas.spotfire.com/ideas/TS-I-8641)
Lack of support for optional prompts (see https://ideas.spotfire.com/ideas/TS-I-8642)
So just in case Spotfire considers an overhaul of their query engine(s), maybe even combining data connections and information links into one single, big data ready query engine, it would be super cool to have all of these things considered in that one solution rather than having parameterized column selection (especially helpful for big data) only in information links and the rest in data connections.